-
Notifications
You must be signed in to change notification settings - Fork 10
Efficient Data Modification
Konstantin Triger edited this page May 25, 2020
·
1 revision
The rule of dumb tells that for optimal performance we need to make as few database requests as possible. This is especially relevant for insert and update scenarios, where we sometimes need to work with thousands of objects. Sending those objects to the database one by one is usually significantly slower than in a batch.
FluentJPA lets write optimized INSERT
queries using Java:
-
INSERT
Multiple Rows in a single statement. (Refer to your vendor documentation for an optimal batch size) -
INSERT INTO SELECT
- so called Bulk Insert - in case the data is already in the database, it is much cheaper to avoid data pulling altogether.
In case of data update there are 3 important scenarios:
- Bulk Update, where there is a need to update multiple rows in the same table. There is a special SQL construct for this case -
UPDATE ... WHERE
, which performs the update in a single query. Some databases, like SQL server, also support a more powerfullUPDATE
withJOIN
construct. - Bulk Delete, same idea for delete case.
And the last scenario - UPSERT
. INSERT
the new rows and UPDATE
existing. Most vendors support it, but with different syntax and capabilities:
-
MERGE
- SQL Server and Oracle. -
INSERT ... ON DUPLICATE ...
- MySQL and Postgres. Sakila MySQL example:
Getting Started
- Introduction
- Setup
- Data Types
- Entities & Tuples
- Sub Queries
- JPA Integration
- Java Language Support
- Directives
- Library
- Returning Results
- JPA Repositories
Examples
Basic SQL DML Statements
Advanced SQL DML Statements
- Common Table Expressions (WITH Clause)
- Window Functions (OVER Clause)
- Aggregate Expressions
- MERGE
- Temporal Tables
Advanced Topics