|
|
Posted by Erland Sommarskog on 09/29/29 11:32
David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:
> 1. You certainly ought to do this set-based rather than row by row as
> your code fragment implied. What you can also do is batch the UPDATEs
> into X rows at a time. For example:
>
> SET ROWCOUNT 50000
>
> WHILE 1=1
>
> BEGIN
>
> UPDATE dataTable
> SET fieldE = 'fieldE_valueF'
> WHERE EXISTS
> (SELECT *
> FROM dataTable AS T
> WHERE fieldA = 'fieldA_valueB'
> AND T.fieldC = dataTable.fieldC
> AND T.fieldD = dataTable.fieldD);
>
> IF @@ROWCOUNT = 0 BREAK
>
> END
>
> SET ROWCOUNT 0
Note that in SQL 2005, this is better written as:
WHILE 1=1
BEGIN
UPDATE TOP 50000 dataTable
SET fieldE = 'fieldE_valueF'
WHERE EXISTS
(SELECT *
FROM dataTable AS T
WHERE fieldA = 'fieldA_valueB'
AND T.fieldC = dataTable.fieldC
AND T.fieldD = dataTable.fieldD);
IF @@ROWCOUNT < 50000 BREAK
END
The TOP is new syntax, which Microsofts recommend over SET ROWCOUNT,
as it is more optimizer-friende. The change for the check on @@rowcount
is just a small optimisation.
I also like to add that in my experience is that for these batching-
operations to be meaningful, you should base the batch on the clusrered
index of the table. Else the time it takes for SQL Server to locate the
rows in the batch can be expensive.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|