query execution plan differ when run as a single query vs. in a sp

  • I have a SQL statement with a single int parameter. When i run it as a query, it uses index seek from both tables in the join and took seconds to finish. But if I put the same query in a stored procedure, and pass in the same parameter value 38176, it uses index scans instead and took 2 minutes to finish. Could someone please help me figure out why this happened? and how to make the sp faster? BTW, execution plans are attached and our statistics is up to date ... Thank you! --Angie

    Here is the query:

    /** query, returns 1200 rows***/

    DECLARE @crr_rowid INT

    SET @crr_rowid = 38176

    SELECT sif.*

    FROM F_IDW_summary_inspection_factor sif

    LEFT JOIN F_IDW_Summary_Inspection si

    on si.smi_rowid = sif.smi_rowid

    WHERE si.crr_rowid = @crr_rowid

    /*** Here is the sp ***/

    Create PROCEDURE  usp_Inspections_Complete

    @crr_rowid int

    AS

    BEGIN

    SELECT sif.*

    FROM F_IDW_summary_inspection_factor sif

    LEFT JOIN F_IDW_Summary_Inspection si

    on si.smi_rowid = sif.smi_rowid

    WHERE si.crr_rowid = @crr_rowid

    END

    Here are the table info:

    /* table info **/

    F_IDW_summary_inspection_factor sif:

    non-CLUSTERED INDEX ON SMI_ROWID --  index seek used by single statement's query plan

    has a clustered PK                                                -- PK scan is used by sp query plan

    has 135 million records

    F_IDW_Summary_Inspection si:

    CLUSTERED PK ON SMI_ROWID

    non-CLUSTERED INDEX ON CRR_ROWID --  index seek is used by single statement's query plan; and index scan used in the sp plan

    has 26 million records

    Attachments:
    You must be logged in to view attached files.
  • Why do you have a left join? it seems you could change it to an inner join. I don't know what that would do to the execution plan, but I would try it.

    You also don't need a join, a semi join would also work, using exists or in. I would try something like this. I know this doesn't answer your specific question, which may be related to parameter sniffing, but I would try removing the left join first.

    SELECT sif.*
    FROM F_IDW_summary_inspection_factor AS sif
    WHERE EXISTS ( SELECT 1
    FROM F_IDW_Summary_Inspection AS si
    WHERE si.smi_rowid = sif.smi_rowid
    AND si.crr_rowid = @crr_rowid)

    SELECT *
    FROM F_IDW_summary_inspection_factor
    WHERE smi_rowid IN ( SELECT smi_rowid FROM F_IDW_Summary_Inspection WHERE crr_rowid = @crr_rowid)

     

    • This reply was modified 1 year, 11 months ago by  Ed B.
  • You need to look at your stand-alone (non-proc) query again.  It seems to have an INNER JOIN rather than a LEFT JOIN, at least according to the query plan.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Thank you both, Ed B and ScottPletcher! Really appreciated your fast responses. I don't know why the code was using a left join (it was written 15 years ago by some contractors) but I do agree an inner join seems to work. And like Scott pointed out, the standalone query used inner join which I didn't notice. too busy consumed with the scan 🙂

    I modified the sp to use inner join and tested it on non-prod server. it worked like a charm, returned results in seconds instead of 2 mins. If developers agree to this change, this will be saving tons of time for us. When the system runs the sp hundreds times a day, this will make HUGE difference.

    Thanks again! 🙂

    Angie

  • Sure sounds like parameter sniffing. Running the code locally do you hard code the value, meaning:

    SELECT x.a
    FROM dbo.mytable as x
    WHERE x.b = 42;

    I would expect that to generate the same execution plan as you would get from a parameter in a stored procedure. It'll use the value to compile the execution plan in a batch like this. Conversely, in a parameterized situation, it'll look up the value passed when it compiles to arrive at the same row estimates.

    Or, are you using a local variable like this:

    DECLARE @b INT;
    SELECT x.a
    FROM dbo.mytable as x
    WHERE x.b = @b;

    In that case, you may be seeing an average of values instead of specific values used to compile the plan.

    Or, your plan in production was compiled with a different value than the one you're passing in dev/test in the batch. Look at the properties of the first operator in the plan. There you'll find the runtime and compile time values. That will let you know what was used to put the plan together.

    As to the rest, glad everyone's suggestions worked for you.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thank you for the info!! really appreciated. We used local variables.

    This is what happened in the past few days with the execution plan in Production, it was using this: Column="@crr_rowid" ParameterDataType="int" ParameterCompiledValue="(38849)" 

    the stand-alone query with value 38849 still used seek, instead of scan and returns 126 rows. 38849 didn't show up in the DBCC SHOW_STATISTICS results yet, since it was very new. (stats attached).

    We have a job to update statistics with fullscan which runs every other week. I wonder if we simply run sp_updatestats on the db, if that will make any difference...

     

    Attachments:
    You must be logged in to view attached files.
  • Statistics updates are way more of an art than a science. However, every two weeks, might be a little long. As a general rule, I updated stats once a week. However, we had a couple of systems where we updated it daily. We also had one, very badly designed system, where we updated stats on a pair of tables once every fifteen minutes. That is absolutely not recommended. It was a horrible data structure that ultimately got replaced. However, for a few months, very frequent statistics updates saved our bottoms.

    It also depends on how often you're rebuilding indexes. Rebuilds, not reorgs, update statistics too, so take that into account.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thank you for the advice. I changed the stats update job to run weekly (took 4 hours with full scan). Our rebuild index process runs daily for less than 10 mins for indexes with 30% fragmentation or more. I will create a job to update stats for some big tables with millions of rows daily and see how that works...

    Thanks

    Angie

Viewing 8 posts - 1 through 7 (of 7 total)

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