October 15, 2001 at 10:58 am
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.
Any suggestions?
SELECT
a.ChartId, a.Characteristic, b.LocationStat, b.EditDate
FROM
EZ_Chartindex a
INNER JOIN
EZ_Samplesummary b ON b.ChartId = a.ChartId
INNER JOIN
(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
WHERE
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
ORDER BY
a.Characteristic
October 15, 2001 at 12:17 pm
Try running sp_recompile 'procedure name' - sounds like maybe the query plan is bad.
Andy
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy