Performance problem

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','F
F','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','F
F','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?
UPDATE: I have redone the query to look like this as per some other
people in the SQL Server Central forum
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','F
F','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 s 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.Which version of SQL are you using? 2000 or 2005?
Regards
Colin Dawson
www.cjdawson.com
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1147719197.120509.118220@.j33g2000cwa.googlegroups.com...
> 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','F
F','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','F
F','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?
>
>
> UPDATE: I have redone the query to look like this as per some other
> people in the SQL Server Central forum
>
> 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','F
F','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 s 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.
>|||Ok. I've just taken a very quick look at this select statement and it'll be
very difficult to help without more information.
Here's what we'll need to know...
1. what version of SQL server are you using?
2. what tables to does is column come from? Please modify the sql that I've
attached below to include table alias's for all the columns. It'll help
when it comes to re-jitting the code to make it more efficient.
3. what does the execution plan look like? Please include a text based
execution place (use set showplan on to get it)
4. can you include ddl and some example data so that we'll be able to
actually test the the procedure. It's important because we don't know what
datatypes these columns are.
I've cleaned up the second SQL statement to help others decipher what's
happening, here's the code...
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','F
F','TR')
and UBNR = '001'
Regards
Colin Dawson
www.cjdawson.com
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1147719197.120509.118220@.j33g2000cwa.googlegroups.com...
> 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','F
F','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','F
F','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?
>
>
> UPDATE: I have redone the query to look like this as per some other
> people in the SQL Server Central forum
>
> 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','F
F','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 s 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.
>|||> I have put a non-clustered index on the table TXUYUV00 involving UBLA,
> UBNR and USL1, and have redone the query to this:
Try creating this same index on the other tables as well. You are joining
to several tables using these columns, and you need the index on all of
them. Put the column with the most distinct values first in your index, and
the column with the fewest distinct values last. I think this will speed
things up.
However, it is hard to say without proper DDL. Post DDL showing your
tables, keys, and indexes, and we may be able to give a better answer.
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1147719197.120509.118220@.j33g2000cwa.googlegroups.com...
> 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','F
F','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','F
F','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?
>
>
> UPDATE: I have redone the query to look like this as per some other
> people in the SQL Server Central forum
>
> 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','F
F','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 s 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.
>|||Brent
In addition to others I want to ask why do you use dates as intereger
datatype? Am I right?
> '3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
Or it is just wrong typing? Shoul be
> '3' AND TXUYUF01.VDAT >= '20050926' AND TXUYUF01.VDAT <= '20060924' AND
Run DBCC FREEPROCCACHE and your query and see what is going on
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1147719197.120509.118220@.j33g2000cwa.googlegroups.com...
> 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','F
F','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','F
F','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?
>
>
> UPDATE: I have redone the query to look like this as per some other
> people in the SQL Server Central forum
>
> 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','F
F','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 s 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.
>|||I am using SQL Server 2000.
I didn't design this database; I'm tapping into the database set up by
our vendors for reporting purposes. All date columns are integer type,
using the yyyymmdd format (which, actually, really works great for
sorting because you don't have to have one program say '10/26/06' is a
string and before '10/27/05').
I apologize for my ignorance on some of your questions. First, how do
I even get the DDL for the tables in question? Also, I was under the
impression that FREEPROCCACHE would only work with ad hoc SQL
procedures, and I didn't think this would qualify as it is an actual
stored procedure. Will this tie up the system for others if I do this?
Colin:
I can't figure out what you're asking in #2:
2. what tables to does is column come from? Please modify the sql that
I've
attached below to include table alias's for all the columns. It'll
help
when it comes to re-jitting the code to make it more efficient.|||Hi Brent,
what I meant to say was, what table does each column come from? It is
important to know which tables each of the columns comes from, as this will
have a major impact on how the query can be modified.
Regards
Colin Dawson
www.cjdawson.com
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1147784999.293403.286010@.g10g2000cwb.googlegroups.com...
>I am using SQL Server 2000.
> I didn't design this database; I'm tapping into the database set up by
> our vendors for reporting purposes. All date columns are integer type,
> using the yyyymmdd format (which, actually, really works great for
> sorting because you don't have to have one program say '10/26/06' is a
> string and before '10/27/05').
> I apologize for my ignorance on some of your questions. First, how do
> I even get the DDL for the tables in question? Also, I was under the
> impression that FREEPROCCACHE would only work with ad hoc SQL
> procedures, and I didn't think this would qualify as it is an actual
> stored procedure. Will this tie up the system for others if I do this?
> Colin:
> I can't figure out what you're asking in #2:
> 2. what tables to does is column come from? Please modify the sql that
> I've
> attached below to include table alias's for all the columns. It'll
> help
> when it comes to re-jitting the code to make it more efficient.
>|||On 16 May 2006 06:09:59 -0700, Brent White wrote:

>I am using SQL Server 2000.
>I didn't design this database; I'm tapping into the database set up by
>our vendors for reporting purposes. All date columns are integer type,
>using the yyyymmdd format (which, actually, really works great for
>sorting because you don't have to have one program say '10/26/06' is a
>string and before '10/27/05').
Hi Brent,
But the datetime would still be a better choice. Sorts as intended as
well, but has much better vallidation and enables easy date/time
calculations.

>I apologize for my ignorance on some of your questions. First, how do
>I even get the DDL for the tables in question?
See www.aspfaq.com/5006.

> Also, I was under the
>impression that FREEPROCCACHE would only work with ad hoc SQL
>procedures, and I didn't think this would qualify as it is an actual
>stored procedure. Will this tie up the system for others if I do this?
SQL Server stores execution plans for both stored procedures and ad-hoc
queries. FREEPROCCACHE will flush them all.
It will also reduce your popularity if you do this on a busy production
system. Better move to a test or dev server before playing around with
these options.
Hugo Kornelis, SQL Server MVP|||I have not tried the FREEPROCCACHE yet, but I did take part of the
query where it's pulling from TXUYUV00 and TXMYTX00 and made a view out
of the subset of TXMYTX00, and the row retrieval time is shorter than
ever (and I mean shorter than even before the problem surfaced), so for
now I think this is going to work. We made the person who uses the
report the most very happy.
As for the datetime, that's not my choice because it was done
externally. I have a function that converts it to an actual datetime
both in SQL Server and in Crystal Reports. Generally if I use a
parameter query in SQL Server for a stored procedure, I capture the
input dates as date time and convert just the parameters to the numeric
form and filter the recordsets that way. Saves a load off the SQL
Server having to convert the date fields for every record, and I then
capture the numeric date in Crystal Reports, converting the date to the
standard date/time.