Stored procedure running slowly
-
Phil,
Thanks - that is useful.
I've found something very strange doing this. See the SQL and the execution plans below. It makes a big difference to the execution plan if I use a number or a variable in the 'WHERE' clause:
SQL using a variable in the WHERE clause
SET SHOWPLAN_TEXT ON
go
declare @LabelID int
set @LabelID = 27
SELECT p.PromptID AS ID,
p.Original_Prompt AS ORIGINAL_VALUE,
p.Approved_Prompt AS VALUE
FROM tblPrompts p
inner join tblPromptLabels pl on p.PromptID = pl.PromptID
WHERE p.promptid not in (SELECT promptid FROM tblPromptTranslations)
and p.Approved = 1
and p.[Ignore] = 0
and pl.LabelID = @LabelID
go
SET SHOWPLAN_TEXT OFF
go
Execution Plan
|--Hash Match(Right Anti Semi Join, HASH: ([tblPromptTranslations].[PromptID])=([p].[PromptID]))
|--Hash Match(Aggregate, HASH: ([tblPromptTranslations].[PromptID]))
| |--Clustered Index Scan(OBJECT: ([Prompts].[dbo].[tblPromptTranslations].[PK_tblPromptTranslations]))
|--Merge Join(Inner Join, MERGE: ([p].[PromptID])=([pl].[PromptID]), RESIDUAL: ([pl].[PromptID]=[p].[PromptID]))
|--Clustered Index Scan(OBJECT: ([Prompts].[dbo].[tblPrompts].[PK_tblPrompts] AS [p]), WHERE: (Convert([p].[Approved])=1 AND Convert([p].[Ignore])=0) ORDERED FORWARD)
|--Clustered Index Scan(OBJECT: ([Prompts].[dbo].[tblPromptLabels].[PK_tblPromptLabels] AS [pl]), WHERE: ([pl].[LabelID]=[@LabelID]) ORDERED FORWARD)
SQL using a constant in the WHERE clause
SET SHOWPLAN_TEXT ON
go
declare @LabelID int
set @LabelID = 27
SELECT p.PromptID AS ID,
p.Original_Prompt AS ORIGINAL_VALUE,
p.Approved_Prompt AS VALUE
FROM tblPrompts p
inner join tblPromptLabels pl on p.PromptID = pl.PromptID
WHERE p.promptid not in (SELECT promptid FROM tblPromptTranslations)
and p.Approved = 1
and p.[Ignore] = 0
and pl.LabelID = 27
go
SET SHOWPLAN_TEXT OFF
go
Execution Plan
|--Nested Loops(Left Anti Semi Join, OUTER REFERENCES: ([p].[PromptID]))
|--Nested Loops(Inner Join, OUTER REFERENCES: ([pl].[PromptID]))
| |--Clustered Index Scan(OBJECT: ([Prompts].[dbo].[tblPromptLabels].[PK_tblPromptLabels] AS [pl]), WHERE: ([pl].[LabelID]=27))
| |--Clustered Index Seek(OBJECT: ([Prompts].[dbo].[tblPrompts].[PK_tblPrompts] AS [p]), SEEK: ([p].[PromptID]=[pl].[PromptID]), WHERE: (Convert([p].[Approved])=1 AND Convert([p].[Ignore])=0) ORDERED FORWARD)
|--Top(1)
|--Clustered Index Scan(OBJECT: ([Prompts].[dbo].[tblPromptTranslations].[PK_tblPromptTranslations]), WHERE: ([p].[PromptID]=[tblPromptTranslations].[PromptID]))
The second version (with the nested loops) is also the version being used by the stored procedure, using the @LabelID variable as passed to the SP, but not copied to a new variable, as in the fix suggested by Paul Mu. This is the one working very slowly.
It can be seen that a completely different method of searching for the specified values is used (Nested Loops versus Hash Matches) and even the order in which the WHERE clauses are evaluated is changed.
The Nested Loops version (using a constant) takes 31 seconds to execute, while the Hash Matches version, with a variable, takes less than 1 second, all using exactly the same data.
Why such a big change in the Execution plan, and why should this make such a big difference to the execution time?
Regards
Adam
-
Stop.
From the beginning.
The second query you just posted is not exactly the case.
I can explain what's going on there, but it's another topic.
We were discussing 2 cases:
- one with pl.LabelID = @LabelID - procedure parameter;
- another with pl.LabelID = @Label_ID - variable declared inside procedure.
Can you post execution plans for these 2 cases?
_____________
Code for TallyGenerator -
Sergiy,
Here is the SQL for the two versions of the SP, the code used to call it, and the generated execution plans.
It is displaying exactly the same behaviour as shown by the SQL on its own, in my previous post
Code to call stored procedure (same for both versions)
SET SHOWPLAN_TEXT ON
go
usp_get_untranslated_prompts_for_export 27
go
SET SHOWPLAN_TEXT OFF
go
Stored procedure using @LabelID (parameter passed to procedure)
ALTER PROCEDURE dbo.usp_get_untranslated_prompts_for_export
@LabelID int
AS
DECLARE @Label_ID int
SELECT @Label_ID = @LabelID
SELECT p.PromptID AS ID,
p.Original_Prompt AS ORIGINAL_VALUE,
p.Approved_Prompt AS VALUE,
p.Approved,
p.PixelLength AS SCREEN,
p.ApplicationIdentifier AS SCHEMATIC,
p.UIContext AS UICONTEXT,
p.Buffer_Length AS MAX_CHARS,
p.PromptName AS PromptName,
p.Description AS DESCRIPTION,
p.DateAdded AS MODIFYDATE
FROM tblPrompts p
inner join tblPromptLabels pl on p.PromptID = pl.PromptID
WHERE p.promptid not in (SELECT promptid FROM tblPromptTranslations)
and p.Approved = 1
and p.[Ignore] = 0
and pl.LabelID = @LabelID
Generated Execution Plan
|--Nested Loops(Left Anti Semi Join, OUTER REFERENCES: ([p].[PromptID]))
|--Nested Loops(Inner Join, OUTER REFERENCES: ([pl].[PromptID]))
| |--Clustered Index Scan(OBJECT: ([Prompts].[dbo].[tblPromptLabels].[PK_tblPromptLabels] AS [pl]), WHERE: ([pl].[LabelID]=[@LabelID]))
| |--Clustered Index Seek(OBJECT: ([Prompts].[dbo].[tblPrompts].[PK_tblPrompts] AS [p]), SEEK: ([p].[PromptID]=[pl].[PromptID]), WHERE: (Convert([p].[Approved])=1 AND Convert([p].[Ignore])=0) ORDERED FORWARD)
|--Top(1)
|--Clustered Index Scan(OBJECT: ([Prompts].[dbo].[tblPromptTranslations].[PK_tblPromptTranslations]), WHERE: ([p].[PromptID]=[tblPromptTranslations].[PromptID]))
Stored procedure using @Label_ID (parameter declared in SP)
ALTER PROCEDURE dbo.usp_get_untranslated_prompts_for_export
@LabelID int
AS
DECLARE @Label_ID int
SELECT @Label_ID = @LabelID
SELECT p.PromptID AS ID,
p.Original_Prompt AS ORIGINAL_VALUE,
p.Approved_Prompt AS VALUE,
p.Approved,
p.PixelLength AS SCREEN,
p.ApplicationIdentifier AS SCHEMATIC,
p.UIContext AS UICONTEXT,
p.Buffer_Length AS MAX_CHARS,
p.PromptName AS PromptName,
p.Description AS DESCRIPTION,
p.DateAdded AS MODIFYDATE
FROM tblPrompts p
inner join tblPromptLabels pl on p.PromptID = pl.PromptID
WHERE p.promptid not in (SELECT promptid FROM tblPromptTranslations)
and p.Approved = 1
and p.[Ignore] = 0
and pl.LabelID = @Label_ID
Generated Execution Plan
|--Hash Match(Right Anti Semi Join, HASH: ([tblPromptTranslations].[PromptID])=([p].[PromptID]))
|--Hash Match(Aggregate, HASH: ([tblPromptTranslations].[PromptID]))
| |--Clustered Index Scan(OBJECT: ([Prompts].[dbo].[tblPromptTranslations].[PK_tblPromptTranslations]))
|--Merge Join(Inner Join, MERGE: ([p].[PromptID])=([pl].[PromptID]), RESIDUAL: ([pl].[PromptID]=[p].[PromptID]))
|--Clustered Index Scan(OBJECT: ([Prompts].[dbo].[tblPrompts].[PK_tblPrompts] AS [p]), WHERE: (Convert([p].[Approved])=1 AND Convert([p].[Ignore])=0) ORDERED FORWARD)
|--Clustered Index Scan(OBJECT: ([Prompts].[dbo].[tblPromptLabels].[PK_tblPromptLabels] AS [pl]), WHERE: ([pl].[LabelID]=[@Label_ID]) ORDERED FORWARD)
Regards
Adam
-
So, you mean that second version with Clustered Index Scans and Hash Joins is faster?
How many rows have you got in tblPrompts and in tblPromptLabels?
How many rows satisfy the condition pl.LabelID = 27 ?
_____________
Code for TallyGenerator -
Sergiy,
Yes, the Hash Tables version runs the fastest
tblPrompts has 15167 rows
tblPromptTranslations has 244783 rows
tblPromptLabels has 162010 rows
The result of the query has 68 rows.
Regards
Adam
-
You mean there are 68 records in tblPromptLabels having LabelID = 27?
I think if you reverse order of columns in PK_tblPromptLabels it will fix your problem forever.
_____________
Code for TallyGenerator -
Sergiy,
No, there are 68 records in tblPrompts, which have a LabelID of 27 in tblPromptLabels (which is a join table for a many-many join) that also don't appear in the table tblPromptTranslations (i.e. the prompt currently doesn't have a translation in any language. tblPromptTranslations is also a many-many join table). There are actually 10888 records in tblPromptLabels having LabelID = 27.
You proposal is interesting though.
Do you mean change:
CREATE TABLE [tblPromptLabels] (
[PromptID] [int] NOT NULL ,
[LabelID] [int] NOT NULL ,
CONSTRAINT [PK_tblPromptLabels] PRIMARY KEY CLUSTERED
to:
CREATE TABLE [tblPromptLabels] (
[LabelID] [int] NOT NULL ,
[PromptID] [int] NOT NULL ,
CONSTRAINT [PK_tblPromptLabels] PRIMARY KEY CLUSTERED
Regards
Adam
-
Not exactly.
Order of columns not in table but in PK:
CONSTRAINT [PK_tblPromptLabels] PRIMARY KEY CLUSTERED ([LabelID],[PromptID])
And I would suggest to replace NOT IN with NOT EXISTS (...).
_____________
Code for TallyGenerator -
Sergiy,
Thanks - I'll give it a go.
Regards
Adam
-
Have you thought about/tried a non-clustered index covering all of the fields in the where clause?
--------------------
Colt 45 - the original point and click interface
-
The performance of the query, when the varaibale passed to the SP is copied into a new local variable is fine - it takes less than a second.
I'm more concerned with why the performance of the original SP dropped so badly, and why the execution plan changes so much when the copy of the variable is used, instead of the original variable (or even, in tha case of the simple SQL statement, why it works so poorly using constant, and so much better using a variable)
Regards
Adam
-
I am not sure but Can there be a chance where in the execution plan is being cached (earlier scenario ) and being used ... where as in case that the variable is being changed causes the procedure to recompile hence the execution plan is being regenerated.....
-
And when I revert to the original SQL, it goes off and finds the old cached version, and uses that again, and doesn't generate a new plan? I doubt it.
I've tried everything to prove this is not the case - creating new stored procedures, using sp_recompile etc and still the difference in the execution plan consistently occur.
Regards
Adam
-
I'm pretty sure you're running into the standard "parameter sniffing" problem. It's also the first thing I always look for when I hear "My stored proc is slow, but running it as an ad hoc query is fast". It's documented in many places, but here is the text from BOL 2005 regarding the issue:
Another reason to force a stored procedure to recompile is to counteract, when necessary, the "parameter sniffing" behavior of stored procedure compilation. When SQL Server executes stored procedures, any parameter values used by the procedure when it compiles are included as part of generating the query plan. If these values represent the typical ones with which the procedure is called subsequently, then the stored procedure benefits from the query plan each time it compiles and executes. If not, performance may suffer.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply