SQL query Optimization help
I have the the following SQL query
Declare @tempcalctbl Table
(
ItemId varchar(50),
ItemLocation varchar(50),
ItemNo varchar(50),
Width real,
Unit varchar(50),
date datetime
)
Insert Into @tempcalctbl
Select distinct SubId,ItemLocation,ItemNo,
(ABS((Select width From @temptbl a Where ItemProcess ='P1'and a.ItemId = c.ItemId
and a.ItemNo = c.ItemNo and a.ItemLocation = c.ItemLocation)
-(Select width From @temptbl b Where ItemP开发者_运维技巧rocess ='P2' and b.ItemId = c.ItemId
and b.ItemNo = c.ItemNo and b.ItemLocation = c.ItemLocation))) * 1000,
Unit,date
From @temptbl c
Group by ItemId,ItemLocation,ItemNo,Unit,date
I was wondering how to optimize this query. The idea is to find out the different in width (p1's item - p2's item) between ItemProcess 'P1' and 'P2' according to the same ItemID, same ItemNo and same ItemLocation. I have around 75000 and it took more then 25 minute to get the width differences for all the ItemId.
I tried to use Group by for the width different calculation but it would return multiple row instead of just a value which then would return error. By the way I am use MS SQL server 2008 and @tempcalctbl is a table that I declared in a store procedure.
Does the following help?
INSERT INTO @tempcalctbl
SELECT P1.SubId ,
P1.ItemLocation ,
P1.ItemNo ,
ABS(P1.Width - P2.Width) * 1000 AS Width ,
P1.Unit ,
P1.date
FROM @temptbl AS P1
INNER JOIN @temptbl AS P2 ON P1.ItemId = P2.ItemId
AND P1.ItemNo = P2.ItemNo
AND P1.ItemLocation = P2.ItemLocation
WHERE P1.ItemProcess = 'P1'
AND P2.ItemProcess = 'P2'
EDIT
To make use of indexes, you will need to change your table variable to a temporary table
CREATE TABLE #temptbl
(
ItemId varchar(50),
ItemLocation varchar(50),
ItemNo varchar(50),
Width real,
Unit varchar(50),
date DATETIME,
ItemProcess INT,
SubId INT
)
CREATE NONCLUSTERED INDEX Index01 ON #temptbl
(
ItemProcess ASC,
ItemId ASC,
ItemLocation ASC,
ItemNo ASC
)
INCLUDE ( SubId,Width,Unit,date)
GO
That should speed you up a little.
John Petrak's answer is the best query for this case. If the speed is still now acceptable, maybe you can store @temptbl at a temporary real table, and create the related index on those four columns.
精彩评论