February 28, 2008 at 12:30 am
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
February 28, 2008 at 11:38 pm
Hi,
Anybody came accross above issue.....If you have any options, let me know...
March 4, 2008 at 6:51 am
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
March 10, 2008 at 4:07 am
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
March 10, 2008 at 4:10 am
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?
March 10, 2008 at 4:35 am
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