December 15, 2014 at 8:36 am
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
December 15, 2014 at 8:52 am
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/
December 15, 2014 at 8:54 am
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.
December 15, 2014 at 8:56 am
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
December 15, 2014 at 10:22 am
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/
December 16, 2014 at 2:14 am
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
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply