The following is stored procedure that causing problems.
When I run the sp it takes about 2 minutes to execute but when I copy the same code into Query Analyzer it runs in about 1-3 seconds. Even when I go back and forth between the sp and the code it consistantly takes about the same time (2 minutes for the sp and 1-3 seconds for the code in query analyzer).
I have noticed that when running the stored procedure that there's locks being placed on the three tables being used but I haven't noticed any locking when running the code in query analyzer.
I've also tried placing the derived table into a temp table first, but it runs about the same.
a.ChartId, a.Characteristic, b.LocationStat, b.EditDate
EZ_Samplesummary b ON b.ChartId = a.ChartId
(SELECT x.Process, x.PartNumber, x.Characteristic, max(y.EditDate) as maxdate
FROM EZ_Chartindex x WITH (NOLOCK)
INNER JOIN EZ_SampleSummary y WITH (NOLOCK) ON y.ChartId = x.ChartId
LEFT OUTER JOIN EZ_Customfields z WITH (NOLOCK) ON z.ChartId = x.ChartId and z.SampleSequence = y.SampleSequence
AND z.CustomText = @Die
WHERE x.ProductLineId = 1 AND x.OperationID = 66
AND x.Process LIKE CASE WHEN x.ItemID = 1 THEN @ReqProcess ELSE '%' END
AND x.PartNumber LIKE CASE WHEN x.ItemID = 77 OR x.ItemID = 78 THEN @ReqProcess ELSE '%' END
AND y.EditDate < @WOTime
AND ISNULL(z.CustomText,'') LIKE CASE WHEN x.ItemID=1 AND LEN(REPLACE(x.Process,'Die','xx'))<LEN(x.Process)-1 THEN @Die ELSE '%' END
GROUP BY x.Process, x.Partnumber, x.Characteristic) d
ON d.Process = a.Process and d.maxdate = b.EditDate AND d.Characteristic = a.Characteristic
a.OperationID = 66
AND a.ProductLineId = 1
AND LatestRevision = 1
AND StatusId = 1
AND a.Process LIKE CASE WHEN ItemID = 1 THEN @ReqProcess ELSE '%' END
AND a.PartNumber LIKE CASE WHEN ItemID = 77 OR ItemID = 78 THEN @ReqProcess ELSE '%' END
Try running sp_recompile 'procedure name' - sounds like maybe the query plan is bad.
Viewing 2 posts - 1 through 1 (of 1 total)