April 27, 2022 at 8:51 pm
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
April 27, 2022 at 9:56 pm
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)
April 28, 2022 at 1:47 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 28, 2022 at 12:21 pm
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
April 28, 2022 at 1:15 pm
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
April 28, 2022 at 3:27 pm
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...
April 28, 2022 at 3:50 pm
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
April 28, 2022 at 9:08 pm
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