this query runs to long how can I speed it up????

Question

this query runs to long how can I speed it up????

kat35601

SSC Eights!

Points: 825
More actions
December 15, 2014 at 8:36 am

#313078

this query runs to long how can I speed it up????
SELECT WIP_master.uompScheduleNumber
,CASE WIP_master.uompScheduleColor
WHEN 'NEON' then 'ORANGERED'
WHEN 'PINK' THEN 'FUCHSIA'
ELSE WIP_master.uompScheduleColor end AS COLOR
,(SELECT COUNT(*)
FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and (itemtype ='DR' or itemtype ='DDF') and WIP_master1.status = '2'
) as Printed
,(SELECT COUNT(*)
FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and (itemtype ='DR' or itemtype ='DDF') and WIP_master1.status = '3'
) as Audited
,(SELECT COUNT(*)
FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and (itemtype ='DR' or itemtype ='DDF') and WIP_master1.status = '9'
) as Shortage
,(SELECT COUNT(*)
FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and (itemtype ='DR' or itemtype ='DDF') and WIP_master1.status = '10'
) as Shortage_Prt
,(SELECT COUNT(*)
FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and (itemtype ='DR' or itemtype ='DDF') and WIP_master1.status = '11'
) as Red
,(SELECT COUNT(*)
FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and (itemtype ='DR' or itemtype ='DDF') and WIP_master1.status = '12'
) as Neon
,(SELECT COUNT(*)
FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and (itemtype ='DR' or itemtype ='DDF') and WIP_master1.status = '20'
) as QC_Hold
FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master
where uompScheduleNumber ='123014-01' and (itemtype ='DR' or itemtype ='DDF')
order by WIP_master.uompScheduleNumber

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

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

Michael L John One Orange Chip Points: 27056 More actions · Answer 1

By using the series of in-line queries, you have created a situation where this table is hit for each row of the outside query.

You are using a CASE statement, so you understand how that works. Apply that to the rest of the queries.

SELECT WIP_master.uompScheduleNumber,

--This part may not work. You may need to put the other case statements in a sub-query

MAX(CASE WIP_master.uompScheduleColor

WHEN 'NEON' then 'ORANGERED'

WHEN 'PINK' THEN 'FUCHSIA'

ELSE WIP_master.uompScheduleColor end) AS COLOR,

SUM(CASE WHEN WIP_master1.status = '2' THEN 1 ELSE 0 END) as Printed,

SUM(CASE WHEN WIP_master1.status = '3' THEN 1 ELSE 0 END) as Audited,

--Add the rest here.

FROM OrbeData_KithKitchens.dbo.WIP_master

WHERE(itemtype ='DR' or itemtype ='DDF')

GROUP BY uompScheduleNumber

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

There's no way to tell from this.

A few things for you to look at and gather data on. What is the execution plans? What indexes are used? What indexes are on these tables? Are statistics up to date?

What are the data sizes in the tables?

How often does the data change in the count(*) query tables? Perhaps an indexed view would help.

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 3

As a guess something like this would work and remove all the sub queries

WITH CTE

AS

(

SELECT uompScheduleNumber,status, COUNT(*) TotalCount

FROM OrbeData_KithKitchens.dbo.WIP_master

WHERE (itemtype ='DR' or itemtype ='DDF')

AND status in ('2','3'......,'20')

GROUP BY uompScheduleNumber,status

)

SELECT

WIP_master.uompScheduleNumber

,CASE WIP_master.uompScheduleColor

WHEN 'NEON' then 'ORANGERED'

WHEN 'PINK' THEN 'FUCHSIA'

ELSE WIP_master.uompScheduleColor

, end AS COLOR

, SUM(CASE WHEN status = '2' THEN TotalCount ELSE 0) As Status2

, SUM(CASE WHEN status = '3' THEN TotalCount ELSE 0) As Status3

, SUM(CASE WHEN status = '20' THEN TotalCount ELSE 0) As Status20

FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master

JOIN CTE

ON WIP_master.uompScheduleNumber=CTE.uompScheduleNumber

AND WIP_Master.status=CTE.Status

where uompScheduleNumber ='123014-01' and (itemtype ='DR' or itemtype ='DDF')

GROUP BY

WIP_master.uompScheduleNumber

,WIP_master.uompScheduleColor

order by WIP_master.uompScheduleNumber

NOTE : This will need testing as I don't have access to sample data, and you may need to put in the missing aggregations for all the types.

EDIT : Forgot the Group by on the outer table.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Yet another version taking the advantage of preaggregation used by Jason and single table scan from Michael.

WITH preAggregation AS(

SELECT WIP_master.uompScheduleNumber

,CASE WIP_master.uompScheduleColor

WHEN 'NEON' THEN 'ORANGERED'

WHEN 'PINK' THEN 'FUCHSIA'

ELSE WIP_master.uompScheduleColor END AS COLOR

,STATUS

,COUNT(*) TotalCount

FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master

WHERE uompScheduleNumber = '123014-01' AND (itemtype = 'DR' OR itemtype = 'DDF')

GROUP BY WIP_master.uompScheduleNumber

,CASE WIP_master.uompScheduleColor

WHEN 'NEON' THEN 'ORANGERED'

WHEN 'PINK' THEN 'FUCHSIA'

ELSE WIP_master.uompScheduleColor END

,WIP_master.STATUS

)

SELECT WIP_master.uompScheduleNumber

,WIP_master.uompScheduleColor AS COLOR

,SUM(CASE WHEN STATUS = '2' THEN TotalCount END) AS Printed

,SUM(CASE WHEN STATUS = '3' THEN TotalCount END) AS Audited

,SUM(CASE WHEN STATUS = '9' THEN TotalCount END) AS Shortage

,SUM(CASE WHEN STATUS = '10' THEN TotalCount END) AS Shortage_Prt

,SUM(CASE WHEN STATUS = '11' THEN TotalCount END) AS Red

,SUM(CASE WHEN STATUS = '12' THEN TotalCount END) AS Neon

,SUM(CASE WHEN STATUS = '20' THEN TotalCount END) AS QC_Hold

FROM preAggregation WIP_master

GROUP BY WIP_master.uompScheduleNumber

,WIP_master.uompScheduleColor

ORDER BY WIP_master.uompScheduleNumber

More information on these kind of queries can be found in here: http://www.sqlservercentral.com/articles/T-SQL/63681/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

The outer select in the OP's original query isn't aggregated:

SELECT

w.uompScheduleNumber,

COLOR = CASE w.uompScheduleColor

WHEN 'NEON' then 'ORANGERED'

WHEN 'PINK' THEN 'FUCHSIA'

ELSE w.uompScheduleColor END,

x.Printed,

x.Audited,

x.Shortage_Prt,

x.Red,

x.Neon,

x.QC_Hold

FROM OrbeData_KithKitchens.dbo.WIP_master w

CROSS APPLY (

SELECT

Printed = SUM(CASE WHEN wi.[status] = '2' THEN 1 ELSE 0 END),

Audited = SUM(CASE WHEN wi.[status] = '3' THEN 1 ELSE 0 END),

Shortage = SUM(CASE WHEN wi.[status] = '9' THEN 1 ELSE 0 END),

Shortage_Prt = SUM(CASE WHEN wi.[status] = '10' THEN 1 ELSE 0 END),

Red = SUM(CASE WHEN wi.[status] = '11' THEN 1 ELSE 0 END),

Neon = SUM(CASE WHEN wi.[status] = '12' THEN 1 ELSE 0 END),

QC_Hold = SUM(CASE WHEN wi.[status] = '20' THEN 1 ELSE 0 END)

FROM OrbeData_KithKitchens.dbo.WIP_master wi

WHERE wi.uompScheduleNumber = w.uompScheduleNumber

AND wi.itemtype IN ('DR','DDF')

AND wi.[status] IN ('2','3','9','10','11','12','20')

) x

WHERE w.uompScheduleNumber = '123014-01'

AND w.itemtype IN ('DR','DDF')

ORDER BY w.uompScheduleNumber

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden