Post reply

SP runs MUCH slower than same code in QA.

  • October 15, 2001 at 10:58 am

    #157823

    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

    #418999

    Try running sp_recompile 'procedure name' - sounds like maybe the query plan is bad.

    Andy

    Andy
    SQLAndy - My Blog!
    Connect with me on LinkedIn
    Follow me on Twitter

Viewing 2 posts - 1 through 1 (of 1 total)

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