How-to: Execute very large updates without exhausting the database
When working with large volumes of data it can become a problem to execute certain update statements, i.e. updates which update multiple records at once. The reason lies in that each statement runs inside a transaction and if updates are updating in 100,000 records then the transaction caches together all the changes. This means that the database can become slower and slower until the transaction commits. All the while the transaction may have locks on some of the parts of the database which may also be problematic.
You can solve this in an elegant way with SQLMerger. The solution also has two additional positive side effects:
1. All changes of values are documented and can subsequently be proved accurately! (In a bulk update, no one knows afterwards what changed)
2. The changes can be executed in any order and in smaller portions. This can be very practical.
In the example, will perform the following statement in a better way:
UPDATE
NewCustomer
SET
CreatedDate =
(
SELECT
COALESCE
(
Min
(
CO.
CreatedDate),
CAST
(
FLOOR
(
CAST
(
getDate
()
AS
FLOAT
)
)
AS
DATETIME
))
AS
ResultDate
FROM
CustomerOrder CO
WHERE
CO.
CustomerID =
NewCustomer.
ID)
The above statement is fine when there are is only e.g. 1000 records but often not if there are e.g. 100.000.
So, instead:
We can select the result we wish for:
SELECT
C.
ID,
COALESCE
(
Min
(
CO.
CreatedDate),
CAST
(
FLOOR
(
CAST
(
getDate
()
AS
FLOAT
)
)
AS
DATETIME
))
AS
CreatedDate
FROM
NewCustomer C
LEFT
OUTER
JOIN
CustomerOrder CO
ON
CO.
CustomerID =
C.
ID
GROUP
BY
C.
ID
And we can select where we want it to go:
SELECT
ID,
CreatedDate FROM
NewCustomer
By the way, that was even easier than it was constructing the original bulk update query..!
Now we’ll put the two statements into the source and the target SQL of a merge item.
Do:
1. Open the data.
You can open both tables by using the option "Open Source & Target" from the "Item" menu, or you can just click the [Open Source] and [Open Target] buttons. (Notice, if you invoke an operation in SQLMerger that needs data, SQLMerger will open the data automatically, for your convenience.)
You will see the data we want to update (the target) and what values we will use (the source):
|
|
Looking at 'the new way' to execute an update statement
|
2. Invoke the merger operation.
Choose the "Make Target values = Source" option from the "Action" menu.
When data has been processed the main window will show:
|
|
The data to update
|
3. Click the "Script" tab, to have SQL statements generated:
4. Click the [Execute] button and select the "Merge" tab again.
|
|
Finished
|
Here is SQL for creating the two tables used above in the example and populating them:
CREATE
TABLE
NewCustomer (
ID INTEGER
PRIMARY
KEY
,
CustName VARCHAR
(
10),
CreatedDate DATETIME
NULL
);
CREATE
TABLE
CustomerOrder (
ID INTEGER
PRIMARY
KEY
,
CustomerID INTEGER
NOT
NULL
REFERENCES
NewCustomer(
ID),
CreatedDate DATETIME
NULL
);
INSERT
INTO
NewCustomer (
ID,
CustName,
CreatedDate)
VALUES
(
1 /* ID */
,
'A'
/* CustName */
,
NULL
/* CreatedDate */
);
INSERT
INTO
NewCustomer (
ID,
CustName,
CreatedDate)
VALUES
(
2 /* ID */
,
'B'
/* CustName */
,
NULL
/* CreatedDate */
);
INSERT
INTO
NewCustomer (
ID,
CustName,
CreatedDate)
VALUES
(
3 /* ID */
,
'C'
/* CustName */
,
NULL
/* CreatedDate */
);
INSERT INTO
NewCustomer (
ID,
CustName,
CreatedDate)
VALUES
(
4 /* ID */
,
'D'
/* CustName */
,
NULL
/* CreatedDate */
);
INSERT
INTO
CustomerOrder (
ID,
CustomerID,
CreatedDate)
VALUES
(
1 /* ID */
,
1 /* CustomerID */
,
'12-03-2009'
/* CreatedDate */
);
INSERT
INTO
CustomerOrder (
ID,
CustomerID,
CreatedDate)
VALUES
(
2 /* ID */
,
1 /* CustomerID */
,
'04-03-2009'
/* CreatedDate */
);
INSERT
INTO
CustomerOrder (
ID,
CustomerID,
CreatedDate)
VALUES
(
3 /* ID */
,
1 /* CustomerID */
,
'02-03-2007'
/* CreatedDate */
);
INSERT
INTO
CustomerOrder (
ID,
CustomerID,
CreatedDate)
VALUES
(
4 /* ID */
,
2 /* CustomerID */
,
'05-03-2009'
/* CreatedDate */
);
INSERT
INTO
CustomerOrder (
ID,
CustomerID,
CreatedDate
VALUES
(
5 /* ID */
,
2 /* CustomerID */
,
'02-12-2004'
/* CreatedDate */
);
INSERT
INTO
CustomerOrder (
ID,
CustomerID,
CreatedDate)
VALUES
(
6 /* ID */
,
2 /* CustomerID */
,
'02-12-2002'
/* CreatedDate */
);
INSERT
INTO
CustomerOrder (
ID,
CustomerID,
CreatedDate)
VALUES
(
7 /* ID */
,
3 /* CustomerID */
,
'01-03-2009'
/* CreatedDate */
);
INSERT
INTO
CustomerOrder (
ID,
CustomerID,
CreatedDate)
VALUES
(
8 /* ID */
,
3 /* CustomerID */
,
'04-11-2009'
/* CreatedDate */
);
INSERT
INTO
CustomerOrder (
ID,
CustomerID,
CreatedDate)
VALUES
(
9 /* ID */
,
3 /* CustomerID */
,
'03-03-2005'
/* CreatedDate */
);
INSERT
INTO
CustomerOrder (
ID,
CustomerID,
CreatedDate)
VALUES
(
10 /* ID */
,
3 /* CustomerID */
,
'03-03-2004'
/* CreatedDate */
);
Back to Overview...