SP runs MUCH slower than same code in QA.

  • 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?


    a.ChartId, a.Characteristic, b.LocationStat, b.EditDate


    EZ_Chartindex a


    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)

You must be logged in to reply to this topic. Login to reply