Bulk Updates

  • Hi,

    I need to update transaction tables for each week. I have created an SP where all the updated are written and will be called from jobs. I have handled this updation with in the Transaction block(Begin, commit).

    There is an performance issue while executing this SP. This SP is taking aroung 20 mins to update the transaction table. But when i execute the SP without having the transaction block then it executes within 7 to 8 mins..

    Is there any solution for this performance issue or can be written in alternative way?

    Solution is highly appreciated!!!!

    Begin Tran

    UPDATE ACCOUNT

    SET CUSTOMER_ID=SC.CUSTOMER_ID

    FROM #JV_SACCODE SC,DBO.JV_ACCOUNT ACC, DBO.JV_CUSTOMER CUST1,DBO.JV_CUSTOMER CUST

    WHERE ACC.ACC_NO=SC.ACC_NO AND

    ACC.SOURCE_SYS_ID=SC.SOURCE_SYS_ID AND

    ACC.CUSTOMER_ID=CUST.CUSTOMER_ID AND

    CUST.DUMMY_SAC='Y' AND

    CUST1.CUSTOMER_ID=SC.CUSTOMER_ID

    UPDATE INVOICE

    SET CUSTOMER_ID=SC.CUSTOMER_ID,SITE_ID=CUST1.SITE_ID

    FROM #JV_SACCODE SC,DBO.JV_INVOICE INV, DBO.JV_CUSTOMER CUST1,DBO.JV_CUSTOMER CUST

    WHERE INV.ACC_NO=SC.ACC_NO AND

    INV.SOURCE_SYS_ID=SC.SOURCE_SYS_ID AND

    INV.CUSTOMER_ID=CUST.CUSTOMER_ID AND

    CUST.DUMMY_SAC='Y' AND

    CUST1.CUSTOMER_ID=SC.CUSTOMER_ID

    ---

    --

    --

    --

    --

    Commit Tran

  • Hi,

    Anybody came accross above issue.....If you have any options, let me know...

  • Hi Ananth,

    How many records do you have in#table? How many records usually get affected due to update statement?

    I suggest if you can avoid using # table and use a table variable, if possible.Also you can keep one permanent table created that in place of #table which gets populated whenever you have to run the query

    Regards,

    Rohit

  • Ananth,

    You may expect that transaction adds some overhead to the procedure but appart from that I'm not sure whether the statements you use are correct.

    If you look at the first statement:

    UPDATE ACCOUNT

    SET CUSTOMER_ID=SC.CUSTOMER_ID

    FROM #JV_SACCODE SC,DBO.JV_ACCOUNT ACC, DBO.JV_CUSTOMER CUST1,DBO.JV_CUSTOMER CUST

    WHERE ACC.ACC_NO=SC.ACC_NO AND

    ACC.SOURCE_SYS_ID=SC.SOURCE_SYS_ID AND

    ACC.CUSTOMER_ID=CUST.CUSTOMER_ID AND

    CUST.DUMMY_SAC='Y' AND

    CUST1.CUSTOMER_ID=SC.CUSTOMER_ID

    one can see that the ACCOUNT table is not listed in the FROM clause (nor as a table nor as alias) and you do not provide any condition to tell which records are to be updated.

    Another isuue is the use of the JV_CUSTOMER table twice. Please note that CUST1 is used only for join so you could avoid double usage of the table by using the table when you create the #JV_SACCODE (if this is required).

    It would be also helpful if you tell how many records you have in each of tables and whether the tables have indexes.

    You can also check execution plan for each of these statements and see how many rows are processed. I would guess that millions. Is it what you intend to do?

    The basic hint is this case is to limit the number of rows in the temporary table. How many rows are in this table?

    Marek

  • The same remarks apply to the second statement:

    UPDATE INVOICE

    SET CUSTOMER_ID=SC.CUSTOMER_ID,SITE_ID=CUST1.SITE_ID

    FROM #JV_SACCODE SC,DBO.JV_INVOICE INV, DBO.JV_CUSTOMER CUST1,DBO.JV_CUSTOMER CUST

    WHERE INV.ACC_NO=SC.ACC_NO AND

    INV.SOURCE_SYS_ID=SC.SOURCE_SYS_ID AND

    INV.CUSTOMER_ID=CUST.CUSTOMER_ID AND

    CUST.DUMMY_SAC='Y' AND

    CUST1.CUSTOMER_ID=SC.CUSTOMER_ID

    The INVOICE table is not listed in the FROM CLAUSE nor in the WHERE clause. Is it intentional?

  • There is one more issue with the procedure. If you look for example at the second statement:

    UPDATE INVOICE

    SET CUSTOMER_ID=SC.CUSTOMER_ID,SITE_ID=CUST1.SITE_ID

    FROM #JV_SACCODE SC,DBO.JV_INVOICE INV, DBO.JV_CUSTOMER CUST1,DBO.JV_CUSTOMER CUST

    WHERE INV.ACC_NO=SC.ACC_NO AND

    INV.SOURCE_SYS_ID=SC.SOURCE_SYS_ID AND

    INV.CUSTOMER_ID=CUST.CUSTOMER_ID AND

    CUST.DUMMY_SAC='Y' AND

    CUST1.CUSTOMER_ID=SC.CUSTOMER_ID

    you can see that most probably the JV_CUSTOMER contains more that one record for a given customer (because you use additional condition DUMMY_SAC = "Y'). If this is true, the second usage of the JV_CUSTOMER table (with condition on CUSTOMER_ID only) causes that carthesian product is generated (many more records than expected are processed). Is it true that the JV_CUSTOMER table may contain more that 1 record for a given customer?

    The same problem appears in the first statement. It seems that both statements are incorrect. I hope you are not running the procedure on the production server 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply