SQL Server Performance

Update Performance

Discussion in 'General Developer Questions' started by alimmia, Nov 22, 2006.

  1. alimmia New Member

    The following script is taking too long to update the table that has 14 million records. Is there any alternative way to do the same? Any hint will be appreciated.
    Thanks!
    ---------------------<<<<<< Script >>>>>>>---------
    select @inv_next = min(invoice_number) from Payment_Charges_All
    while 1 = 1
    begin
    select @chrg_txn_next = min(charge_transaction) from Payment_Charges_All where invoice_number = @inv_next
    while 1 = 1
    begin
    select top 1 @rec_id = record_num from Payment_Charges_All where invoice_number = @inv_next
    and charge_transaction = @chrg_txn_next
    if @@rowcount > 0
    begin
    update Payment_Charges_All set REJ_CHRG_COUNT = 1 from Payment_Charges_All where invoice_number = @inv_next
    and charge_transaction = @chrg_txn_next and record_num = @rec_id and rej_txn = 1
    update Payment_Charges_All set DIST_CHRG_COUNT = 1 from Payment_Charges_All where invoice_number = @inv_next
    and charge_transaction = @chrg_txn_next and record_num = @rec_id
    update Payment_Charges_All set charge_amount = 0.00 from Payment_Charges_All where invoice_number = @inv_next
    and charge_transaction = @chrg_txn_next and record_num <> @rec_id
    end
    select @chrg_txn_next = min(charge_transaction) from Payment_Charges_All where invoice_number = @inv_next
    and charge_transaction > @chrg_txn_next
    if isnull(@chrg_txn_next,0) = 0
    break
    end
    select @inv_next = min(invoice_number) from Payment_Charges_All where invoice_number > @inv_next
    if isnull(@inv_next,0) = 0
    break
    end
  2. Roji. P. Thomas New Member

    Get rid of the while loops, and write a Single set based update statement. If it affects 14 million rows, consider splitting the Update into batches of reasonable size.


    Roji. P. Thomas
    http://toponewithties.blogspot.com
  3. Madhivanan Moderator

    Something like

    Update T1
    set col1=T2.col1,col2=T2.col2,......
    from table1 T1 inner join table2 T2
    on t1.keycol=T2.keycol

    Madhivanan

    Failing to plan is Planning to fail
  4. alimmia New Member

    Thanks for the comments. However, I have missed both of yours points. How can I get rid of the while loop, and how the update going to work.

    In the script there are three different conditions for the three updates. In my record set, there are duplicate invoices and I only need to update the first one. Can I do it without loop? Would you please give me more hints.
    Thanks!

    Alim Mia

Share This Page