WHILE LOOP | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

WHILE LOOP

I have seen that the below query (Q1) is more efficient than that of normal update query (Q2). But logically ‘WHILE OPERATION’ should create an overhead. Could anybody prove this logically? Any pointers please? Q1)
[email protected]_End <= @i_Max
BEGIN
[email protected]_End= @i_Start + @i_Incr UPDATECash_Receipt
SETIntended_Sales_Contract_Nbr= LTRIM(RTRIM(Location_Cd)) + LTRIM(RTRIM(Tmp_Branch)) + LTRIM(RTRIM(Tmp_Contract)) FROMCash_Receipt
,Control_DMCtl
WHERECtl.Control_Desc= ‘DB_Id’
ANDCtl.Control_ValueIN (1,4,5,9)
[email protected][email protected]_End [email protected]_Start= @i_End + 1
END Q2)
UPDATECash_Receipt
SETIntended_Sales_Contract_Nbr= LTRIM(RTRIM(Location_Cd)) + LTRIM(RTRIM(Tmp_Branch)) + LTRIM(RTRIM(Tmp_Contract)) FROMCash_Receipt
,Control_DMCtl
WHERECtl.Control_Desc= ‘DB_Id’
ANDCtl.Control_ValueIN (1,4,5,9)
[email protected][email protected]_End Ashish Johri
There is no overhead involved in having a WHILE loop in and by itself, not that you could notice. Depending on the number of rows involved, updating in batches can be beneficial. I would be more concerned with the Start and End values, which you’re using as a filter range for the ID values. You seem to be starting with a range 1-100, then 2-100, then 3-100 … that way, you would be updating the same rows over and over again.
there are several issues in each the loop overhead is fairly low,
in the first, each update generates a write to the log in the second, the whole update must be written to the log,
if your log file size must increase,
that would explain why 2 is slower than 1 on your setup otherwise, it really depends on how many rows 1 updates on each call,
if only 1 row, then 2 should be 3-4X faster than 1 if no log file increase is required
if 1 update 20+ rows, then there would be little difference between 1 & 2 there is also the matter of the mistake in the SQL 2000 optimizer regarding update costing, i think i have paper on this subject on this site, not sure if 2005 corrects this

]]>