SQL Server Performance
  1. ashish.johri2007 New Member

    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)
    WHILE@i_End <= @i_Max
    BEGIN
    SET@i_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)
    ANDCash_Receipt_IDBETWEEN@i_StartAND@i_End

    SET@i_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)
    ANDCash_Receipt_IDBETWEEN@i_StartAND@i_End



    Ashish Johri
  2. Adriaan New Member

    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.
  3. joechang New Member

    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

Share This Page