Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Transact-SQL (2000)  SP runs MUCH slower than same code in QA.

Author  Topic  rtracy Starting Member 3 Posts Posted - 2001-10-15 : 14:47:36 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