Update is slow

Improving Performance for Operations Other Than “Add All Records”

Some customers have reported slow performance for operations like Add New and Update Old Records or Delete Records, believing these limitations are due to the software itself. This article clarifies the cause and provides recommendations for optimizing performance.

Understanding How Updates and Deletes Work

Update/Delete Operations Require a Key

For updates or deletes, the Update Key must be defined. Without it, operations cannot function as the software relies on this key to identify existing records.

Steps for Add New and Update Old Records:

Determine Record Existence
For each record, the software executes a COUNT query to check if the record exists:
```
SELECT COUNT(*)
FROM [orders]
WHERE CustomerId = ? AND OrderNo = ?
```

Perform Update or Insert

If the record exists, an UPDATE is executed:

UPDATE [orders]
SET orderdate = ?, amount = ?
WHERE CustomerId = ? AND OrderNo = ?

If the record does not exist, an INSERT is executed:

INSERT INTO [orders] (CustomerId, OrderNo, orderdate, amount)
VALUES (?, ?, ?, ?)

Why Operations Are Slow

High Number of SQL Statements
For 10,000 source records, 20,000 SQL statements (10,000 COUNT + 10,000 INSERT/UPDATE) are executed.
Table Scans
Each COUNT query performs a full table scan or an index scan, which can be time-consuming, especially for large datasets.
Database Overhead
Every SQL statement incurs overhead due to database execution and network communication.

Optimizing for Large Tables

Use a Temporary Table

For large datasets, the recommended approach is to load data into a temporary table and perform batch updates and inserts.

Load Data into Temporary Table (orders_tmp)
All source data is loaded into a staging table.

Execute Batch Update and Insert Queries

Batch Update: Update existing records in one statement:

UPDATE orders
SET orderdate = orders_tmp.orderdate,
    amount = orders_tmp.amount
FROM orders_tmp
WHERE orders.CustomerId = orders_tmp.CustomerId
  AND orders.OrderNo = orders_tmp.OrderNo;

Batch Insert: Insert new records:

INSERT INTO orders (CustomerId, OrderNo, orderdate, amount)
SELECT CustomerId, OrderNo, orderdate, amount
FROM orders_tmp e
WHERE NOT EXISTS (
    SELECT 1
    FROM orders
    WHERE orders.CustomerId = e.CustomerId
      AND orders.OrderNo = e.OrderNo
);

Benefits:

Fewer SQL Executions: Only two statements are executed regardless of the number of records.
Optimized Performance: Bulk operations reduce database overhead and leverage indexing for faster execution.

Best Practices for Improved Performance

Index Key Columns

Ensure the Update Key columns (e.g., CustomerId, OrderNo) are indexed in the target table.
Optimize Database Configuration
- Increase memory allocated to the database.
- Use connection pooling to reduce connection overhead.
Schedule Off-Peak Operations

For large-scale updates, schedule operations during low-usage periods to minimize contention.

Monitor Database Performance

Use tools like SQL Server Profiler or database-specific performance monitors to identify bottlenecks.

By following these strategies, you can address slow performance issues and handle large datasets efficiently. For further assistance, feel free to contact our support team .