SQL Server Performance

Input Variable for a Stored Procedure runs much more slowly than simple Query with the same joins

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by bulld, Dec 7, 2009.

  1. bulld New Member

    I'm trying to gather summary information from 4 tables for the latest entry of a PID. The query runs very quickly (<1sec) with a string input with the literal value of a PID. If I variable-ize the PID and convert the query to a stored procedure, the stored proc takes over 4 minutes to run for the same string literal input and it has a different execution plan. What's happening, why is it compiled/interpreted so differently?
    /*
    acct table is HUGE, several million records
    pgroup table is ~100,000 records
    LOOKUP is unindexed, but small < 2000 records
    custaddress is around 550,000 records
    all tables except pgroup table are indexed on PID
    */


    --As a query, finishes in under a second
    SELECT top 1 rp.custname, rp.billingmonth, rp.costcode, appvalue,
    par.pname, B.LDESC AS pdesc, pa.psite, cc.bcount, cc.ucount
    FROM property.parcel par
    full outer join property.acct rp ON rp.PID = par.PID
    full outer join property.pgroup cc ON par.PID = cc.PID
    full outer join property.LOOKUP B ON (par.PRESENTUSE = B.LITEM and B.LTYPE = 10)
    full outer join property.custaddress pa ON par.PID = pa.PID
    WHERE
    par.PID = '65033414'
    ORDER BY rp.billingmonth DESC


    --As a procedure, takes 4+ minutes
    CREATE PROCEDURE [property].[p_PVSummaryByPID]
    (
    @thePID varchar(10)
    )
    AS
    SELECT top 1 rp.custname, rp.billingmonth, rp.costcode, appvalue,
    par.pname, B.LDESC AS pdesc, pa.psite, cc.bcount, cc.ucount
    FROM property.parcel par
    full outer join property.acct rp ON rp.PID = par.PID
    full outer join property.pgroup cc ON par.PID = cc.PID
    full outer join property.LOOKUP B ON (par.PRESENTUSE = B.LITEM and B.LTYPE = 10)
    full outer join property.custaddress pa ON par.PID = pa.PID
    WHERE
    par.PID = @thePID
    ORDER BY rp.billingmonth DESC

  2. Adriaan New Member

    With this type of performance issue the usual answer is that you may be suffering from "parameter sniffing" (which was originally intended to improve performance, but it tends to back-fire).
    Add a local variable to the procedure.
    Copy the parameter value into the local variable.
    Use the local variable inside the procedure.
  3. bulld New Member

    That saved a little time. Duration dropped from over 4.5 minutes to just over 4 minutes. The execution plan is different for the variable-ized version than it is for the method with the hard-coded PID. What's the best way to show an execution plan here?
  4. Adriaan New Member

    I didn't really look at the query in detail at first, but why all the FULL JOINs?
  5. preethi Member

    Just to clarify, IS the datatype of property.parcel.PID varchar(10)? If not, there could be a conversion taking place when a parameter is passed.
  6. satya Moderator

    You could check the Execution plan for this query to see where it is taking time.

Share This Page