May 11, 2006 at 12:08 pm
I need help ASAP on this.
I had a stored procedure that pulls the "bookings" for our company (sales, if you will) for the past year. Before, it looked like this:
SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup, UVT1, SLNAME, UVT2, AXTXT, USL1
FROM (((bdg_view_GroupingByReceivableCustomer INNER JOIN TXUYUV00 ON bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND TXUYUV00.UPLT = TXUYUF01.VPLT) INNER JOIN TXMYTX00 ON '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UPLT=TXMYTX00.AXPLT) LEFT OUTER JOIN CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
WHERE
UBLA<>'JB' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND TXUYUF01.VANR NOT LIKE 'ZZ%' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5' AND TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','FF','TR')
It was running fine. It was a little slow, but not too bad. Fastforward to today. We added a new unit to our company, that now requires a second value for fields ending in "BNR" (VBNR, UBNR, etc.)
Now the query looks like this:
SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup, UVT1, SLNAME, UVT2, AXTXT, USL1
FROM (((bdg_view_GroupingByReceivableCustomer INNER JOIN TXUYUV00 ON bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND TXUYUV00.UPLT = TXUYUF01.VPLT) INNER JOIN TXMYTX00 ON '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UBNR = TXMYTX00.AXBNR and TXUYUV00.UPLT=TXMYTX00.AXPLT) LEFT OUTER JOIN CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
WHERE
UBLA<>'JB' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND TXUYUF01.VANR NOT LIKE 'ZZ%' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5' AND TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','FF','TR') and UBNR = '001'
Problem? Though these tables have changed only modestly since this changeover (number of rows), the query takes 5 minutes to complete, and the CPU usage on a dual-processor server goes to 100%. I can't even bring up the task manager until the query completes. This wasn't a problem with the old procedure.
Can someone help me figure this out and what I need to do?
May 12, 2006 at 3:45 am
Try moving the addition and many of the other WHERE clause items to the JOIN ON clause. Was:
...AND TXUYUV00.UBNR=TXUYUF01.VBNR...
changed:
...AND TXUYUV00.UBNR=TXUYUF01.VBNR AND TXUYUV00.UBNR='001'...
This should help buy eliminating records early on in the JOIN instead of later with the WHERE.
I would also suggest that you add any missing <table name>. references. This may be old school, but I "think" this may assist in the query optimization.
Andy
May 12, 2006 at 6:07 am
I'm not sure what you mean by missing <table name> references.
May 12, 2006 at 12:52 pm
May 12, 2006 at 2:05 pm
To piggy back onto Todd's suggestion, check out the Execution Plan and I'll bet the table scan will stand out like a sore thumb.
Mark
May 12, 2006 at 2:17 pm
I just ran the Estimated Execution Plan. Actually, there's a clustered index seek that is taking 49%. It is on the WHERE clause, it appears, on TXUYUV00.
Could specifying the UBNR have pushed it over the edge?
May 15, 2006 at 8:26 am
Any suggestions? I need to know.
May 15, 2006 at 12:55 pm
As per your suggestions, I have retooled the query and added indices to no avail.
I have put a non-clustered index on the table TXUYUV00 involving UBLA, UBNR and USL1, and have redone the query to this:
SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup, UVT1, SLNAME, UVT2, AXTXT, USL1
FROM (((bdg_view_GroupingByReceivableCustomer INNER JOIN TXUYUV00 ON bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND TXUYUV00.UPLT = TXUYUF01.VPLT AND TXUYUV00.UBNR = '001' and TXUYUV00.UBLA IN('SA','SO') and TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','FF','TR') ) INNER JOIN TXMYTX00 ON '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UBNR = TXMYTX00.AXBNR and TXUYUV00.UPLT=TXMYTX00.AXPLT) LEFT OUTER JOIN CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
WHERE
bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND TXUYUF01.VANR NOT LIKE 'ZZ%' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5'
It still runs and runs and takes a ton of CPU time. The Clustered Index seek is taking 49% of the execution plan. When I run the query, I have to stop it immediately because it makes the CPU go to 90% and the system stays there.
It didn't used to do this. I can't figure out why it does now.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply