March 27, 2011 at 11:11 pm
Hi Guys,
I'm having trouble on tuning this update statements.
UPDATE CASE_DETAILS_V
SET USER_DATA3= LEFT(INDUSTRYDESC,105),--INDUSTRY,
USER_DATA1= TEST_PROGRAM--TEST PROGRAM
FROM (
SELECT DISTINCT ACCTNO,CIFNO,TEST_PROGRAM,INDUSTRYDESC
FROM #TBL_HAND_OFF_TMP HAND_OFF
LEFT OUTER JOIN
(
SELECT DISTINCT T2.LTDM_CTGDESC_C TEST_PROGRAM,T1.APP_ID_C APPID
FROM VIEW_LOS_APP_TAGS T1
INNER JOIN VIEW_LOS_TAGS_DTL_MST T2
ON (T1.LAT_CTGCODE_C=T2.LTDM_CTGCODE_C AND LAT_TAGID_N = 24
OR T1.TAG_24 = T2.LTDM_CTGCODE_C) -- INCLUDE CAS TEST PROGRAM TAGS
AND T1.SYSID=T2.SYSID
) TAGS ON (HAND_OFF.APP_ID = TAGS.APPID)
LEFT OUTER JOIN (
SELECT APP.APP_ID_C,IND.INDUSTRYDESC FROM VIEW_LOS_APP_APPLICATIONS APP
INNER JOIN #TBL_CUSTOMER CUST ON APP.CUST_ID_N = CUST.CUST_ID_N AND APP.SYSID=CUST.SYSID
INNER JOIN VIEW_NBFC_INDUSTRY_M IND ON CUST.INDUSTRYID = IND.INDUSTRYID AND CUST.SYSID=IND.SYSID
) INDUSTRY ON (HAND_OFF.APP_ID = INDUSTRY.APP_ID_C)
) LOS
WHERE CASE_DETAILS_V.FINANCIER_ID = 'CTB-LOANS'
AND CASE_DETAILS_V.APPL_ID = LOS.ACCTNO
AND CASE_DETAILS_V.CUST_ID = LOS.CIFNO
The statements runs for 1hr and 10mins. I attached the complete query including the execution plans, table definitions and it's corresponding row count.
1 more thing, you'll be noticing tables with same DB structure (e.g NBFC_CUSTOMER_M & LOS3_NBFC_CUSTOMER_M). tables with LOS3 prefix were used by an old system and eventually migrated to a new system. They' cant merge these tables since it's a complicated thing to do (primary and foreign dependencies). And also, I think some of the report were mapped to the old tables. If you think merging of these tables would also help, please let me know.
Thanks everyone
March 28, 2011 at 12:11 am
Marvin Maralit (3/27/2011)
Hi Guys,I'm having trouble on tuning this update statements.
UPDATE CASE_DETAILS_VSET USER_DATA3= LEFT(INDUSTRYDESC,105),--INDUSTRY,
USER_DATA1= TEST_PROGRAM--TEST PROGRAM
FROM (
SELECT DISTINCT ACCTNO,CIFNO,TEST_PROGRAM,INDUSTRYDESC
FROM #TBL_HAND_OFF_TMP HAND_OFF
LEFT OUTER JOIN
(
SELECT DISTINCT T2.LTDM_CTGDESC_C TEST_PROGRAM,T1.APP_ID_C APPID
FROM VIEW_LOS_APP_TAGS T1
INNER JOIN VIEW_LOS_TAGS_DTL_MST T2
ON (T1.LAT_CTGCODE_C=T2.LTDM_CTGCODE_C AND LAT_TAGID_N = 24
OR T1.TAG_24 = T2.LTDM_CTGCODE_C) -- INCLUDE CAS TEST PROGRAM TAGS
AND T1.SYSID=T2.SYSID
) TAGS ON (HAND_OFF.APP_ID = TAGS.APPID)
LEFT OUTER JOIN (
SELECT APP.APP_ID_C,IND.INDUSTRYDESC FROM VIEW_LOS_APP_APPLICATIONS APP
INNER JOIN #TBL_CUSTOMER CUST ON APP.CUST_ID_N = CUST.CUST_ID_N AND APP.SYSID=CUST.SYSID
INNER JOIN VIEW_NBFC_INDUSTRY_M IND ON CUST.INDUSTRYID = IND.INDUSTRYID AND CUST.SYSID=IND.SYSID
) INDUSTRY ON (HAND_OFF.APP_ID = INDUSTRY.APP_ID_C)
) LOS
WHERE CASE_DETAILS_V.FINANCIER_ID = 'CTB-LOANS'
AND CASE_DETAILS_V.APPL_ID = LOS.ACCTNO
AND CASE_DETAILS_V.CUST_ID = LOS.CIFNO
The statements runs for 1hr and 10mins. I attached the complete query including the execution plans, table definitions and it's corresponding row count.
1 more thing, you'll be noticing tables with same DB structure (e.g NBFC_CUSTOMER_M & LOS3_NBFC_CUSTOMER_M). tables with LOS3 prefix were used by an old system and eventually migrated to a new system. They' cant merge these tables since it's a complicated thing to do (primary and foreign dependencies). And also, I think some of the report were mapped to the old tables. If you think merging of these tables would also help, please let me know.
Thanks everyone
This is a classic problem where halloweening comes into play. On joined updates, the target of the update must also appear in the FROM clause or what should be a 2 second update can pin 4 CPU's to the wall for hours.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2011 at 12:35 am
Bro, can you be more specific? sorry just a newbie 😛
March 29, 2011 at 9:16 am
Seeking For Anwser (3/28/2011)
Bro, can you be more specific? sorry just a newbie 😛
The complexity of this issue isn't something that lends itself to a few forum posts. Given that you are a newbie, see if your company will hire a performance tuning mentor for you to a) identify and improve existing badness in your systems and b) teach you how to do the same.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 29, 2011 at 7:51 pm
Seeking For Anwser (3/28/2011)
Bro, can you be more specific? sorry just a newbie 😛
Sure... if you'll stop calling me "Bro" and start calling me "Jeff". 😉 Details are in the comments in the code below. I'll also tell you that these changes still might not solve the overall performance problem (which I just don't have enough info to do from here) but it will usually prevent a problem known as "Halloweening" which could be a major part of the performance problem here.
To repeat what I said before, for joined updates, the target (table/object of the update) of the update MUST be in the FROM clause. You won't find a single example in Books Online where that isn't true. They don't come out and say it, but it's an absolute truth to help prevent "Halloweening".
UPDATE cdv --<<<< alias from newly added table in FROM clause replaced table name
SET USER_DATA3 = LEFT(INDUSTRYDESC,105),--INDUSTRY,
USER_DATA1 = TEST_PROGRAM--TEST PROGRAM
FROM CASE_DETAILS_V cdv --<<<< table and alias added
INNER JOIN --<<<< added
(
SELECT DISTINCT ACCTNO,CIFNO,TEST_PROGRAM,INDUSTRYDESC
FROM #TBL_HAND_OFF_TMP HAND_OFF
LEFT OUTER JOIN
(
SELECT DISTINCT T2.LTDM_CTGDESC_C TEST_PROGRAM,T1.APP_ID_C APPID
FROM VIEW_LOS_APP_TAGS T1
INNER JOIN VIEW_LOS_TAGS_DTL_MST T2
ON (T1.LAT_CTGCODE_C=T2.LTDM_CTGCODE_C AND LAT_TAGID_N = 24
OR T1.TAG_24 = T2.LTDM_CTGCODE_C) -- INCLUDE CAS TEST PROGRAM TAGS
AND T1.SYSID=T2.SYSID
) TAGS ON (HAND_OFF.APP_ID = TAGS.APPID)
LEFT OUTER JOIN (
SELECT APP.APP_ID_C,IND.INDUSTRYDESC FROM VIEW_LOS_APP_APPLICATIONS APP
INNER JOIN #TBL_CUSTOMER CUST ON APP.CUST_ID_N = CUST.CUST_ID_N AND APP.SYSID=CUST.SYSID
INNER JOIN VIEW_NBFC_INDUSTRY_M IND ON CUST.INDUSTRYID = IND.INDUSTRYID AND CUST.SYSID=IND.SYSID
) INDUSTRY ON (HAND_OFF.APP_ID = INDUSTRY.APP_ID_C)
) LOS
ON cdv.APPL_ID = LOS.ACCTNO --<<<< moved to here and changed table name to alias of cdr
AND cdv.CUST_ID = LOS.CIFNO --<<<< moved to here and changed table name to alias of cdr
WHERE CASE_DETAILS_V.FINANCIER_ID = 'CTB-LOANS'
--AND CASE_DETAILS_V.APPL_ID = LOS.ACCTNO --<<<< Commented out
--AND CASE_DETAILS_V.CUST_ID = LOS.CIFNO --<<<< Commented out
--Jeff Moden
Change is inevitable... Change for the better is not.
March 31, 2011 at 12:58 am
Hi Jeff thanks for the response. I tested the query you gave me however I'm getting an error The column prefix '<column_prefix_name>' does not match with a table name or alias name used in the query, so I removed the cdv alias name. It turned out the new query was 4 minutes longer than the original query.
Do you think adding a memory will also help?
Actually we do have a DBA/Sys Admin here but they can't accommodate me since they have a lot of things to do. So while waiting for them, I'm looking for a back-up plan.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply