Stored procedure running slowly
-
Hi,
I have a stored procedure that takes 30s to run:
CREATE PROCEDURE dbo.usp_get_untranslated_prompts_for_export
@LabelID int
AS
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
then:
EXEC usp_get_untranslated_prompts_for_export 27
However, if I just run the SQL on its own, it takes less than 1 second, i.e.
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
For info, I've put the table definitions at the end of this posting. Also:
tblPrompts has 15167 rows
tblPromptTranslations has 244783 rows
tblPromptLabels has 162010 rows
The result of the query has 68 rows.
The SQL used on its own and in the SP is identical.
I've tried dropping and re-creating the SP, and I've tried sp_recompile on the SP, but this makes no difference.
In the Execution Plans, the SQL on its own uses Hash Matches and a Right Anti Semi Join, yet the SP uses Nested Loops. I can't find a way of copying the executions plans to copy in here though
Any ideas why the SP should run so slowly?
Thanks
Adam
Table Defs:
CREATE TABLE [tblPromptTranslations] (
[TranslationID] [int] NOT NULL ,
[PromptID] [int] NOT NULL ,
CONSTRAINT [PK_tblPromptTranslations] PRIMARY KEY CLUSTERED
(
[TranslationID],
[PromptID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_tblPromptTranslations_tblPrompts] FOREIGN KEY
(
[PromptID]
) REFERENCES [tblPrompts] (
[PromptID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_tblPromptTranslations_tblTranslations] FOREIGN KEY
(
[TranslationID]
) REFERENCES [tblTranslations] (
[TranslationID]
) ON DELETE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [tblPrompts] (
[PromptID] [int] IDENTITY (1, 1) NOT NULL ,
[Version] [int] NOT NULL ,
[PromptName] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[ApplicationIdentifier] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProductID] [int] NULL ,
[ChangeType] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[DateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblPrompts_DateAdded] DEFAULT (getdate()),
[Prompt_Options] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[CompanyID] [int] NULL ,
[Original_Prompt] [nvarchar] (1000) COLLATE Latin1_General_CI_AS NOT NULL ,
[Approved_Prompt] [nvarchar] (1000) COLLATE Latin1_General_CI_AS NULL ,
[Comment] [nvarchar] (1000) COLLATE Latin1_General_CI_AS NULL ,
[Description] [nvarchar] (1000) COLLATE Latin1_General_CI_AS NULL ,
[UIContext] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Localisable] [bit] NULL CONSTRAINT [DF_tblPrompts_Localisable] DEFAULT (1),
[PixelLength] [int] NULL ,
[Buffered] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Buffer_Length] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[Target] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Approved] [bit] NOT NULL CONSTRAINT [DF_tblPrompts_Approved] DEFAULT (0),
[Ignore] [bit] NULL CONSTRAINT [DF_tblPrompts_Ignore] DEFAULT (0),
[Deleted] [bit] NOT NULL CONSTRAINT [DF_tblPrompts_Deleted] DEFAULT (0),
[DateDeleted] [smalldatetime] NULL ,
[UpdatedBy] [int] NULL ,
[DateUpdated] [smalldatetime] NULL ,
CONSTRAINT [PK_tblPrompts] PRIMARY KEY CLUSTERED
(
[PromptID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_tblPrompts_tblCompanies] FOREIGN KEY
(
[CompanyID]
) REFERENCES [tblCompanies] (
[CompanyID]
),
CONSTRAINT [FK_tblPrompts_tblProducts] FOREIGN KEY
(
[ProductID]
) REFERENCES [tblProducts] (
[ProductID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_tblPrompts_tblRegisteredUsers] FOREIGN KEY
(
[UpdatedBy]
) REFERENCES [tblRegisteredUsers] (
[RegisteredUserID]
)
) ON [PRIMARY]
GO
CREATE TABLE [tblPromptLabels] (
[PromptID] [int] NOT NULL ,
[LabelID] [int] NOT NULL ,
CONSTRAINT [PK_tblPromptLabels] PRIMARY KEY CLUSTERED
(
[PromptID],
[LabelID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_tblPromptLabels_tblLabels] FOREIGN KEY
(
[LabelID]
) REFERENCES [tblLabels] (
[LabelID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_tblPromptLabels_tblPrompts] FOREIGN KEY
(
[PromptID]
) REFERENCES [tblPrompts] (
[PromptID]
) ON DELETE CASCADE
) ON [PRIMARY]
GO
-
Did you try to compare execution plans?
_____________
Code for TallyGenerator -
Yes, and they were different.
If you can tell me how I can copy and post them here, I will do!
Regards
Adam
-
Here are the traces from running the two queries, if it is of any help:
SQL on its own:
set @LabelID = 27 SQL:StmtCompleted0000
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.ApprovSQL:StmtCompleted1411412300
Running the SP:
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 promptSP:StmtCompleted8065150
usp_get_untranslated_prompts_for_export 27SQL:StmtCompleted8065190
Regards
Adam
-
Try this little trick to see if it works for you (all changes/additions in bold):
CREATE PROCEDURE dbo.usp_get_untranslated_prompts_for_export
@Label_ID int
AS
DECLARE @LabelID int
SELECT @LabelID = @Label_ID
/* Rest of you code goes here */
-
Paul,
That works! Thank you 🙂
It makes no sense though - why should it make such a difference?
Thanks
Adam
-
As the execution plans were different between the sp and the native code, it is likely that the procedure was compiled some time ago and since then there has been a new index or statistics created/updated on the table - the native sql would compile when you executed it and used the new index/statistics.
Recompiling the procedure without any changes have had the same effect.
J
-
But I tried all that, as I said in my original post
I ran 'ALTER PROCEDURE', I ran sp_recompile, I even dropped and recreated the SP, and this didn't make a difference to the execution plan.
And now, just changing the name of the variable in the last line of the SELECT to the variable passed to the SP, instead of the new one declared within the SP causes the problem to come back!
Very Strange
Regards
Adam
-
Did you try DBCC FREEPROCCACHE?
_____________
Code for TallyGenerator -
Yes, and it doesn't make a difference.
Adam
-
Adam, like you I have tried those same things but w/o much success.
Normally, what usually happens is that the SP works fine for a while and suddenly it stops working properly! There have been a number of threads in this forum describing the same situation, but I'm not sure that a definitive answer has been given as to its cause - though there are some work-arounds!
Another work-around is to use the OPTION (FORCE ORDER) hint in the select statement that is causing you some angst.
Paul
-
Paul,
The way it stopped working (even though it was previously fine) is another symptom I noticed.
After I fixed this SP, I found another with the same problem, although several others are still OK and making the changes you suggested didn't improve performance.
I have noticed though that the Execution Plan does change considerably when making this change, beyond the simple addition of another select statement.
As the two variables (@LabelID and @Label_ID) are both 'int', the EP should be just the same for both versions. This really does not make sense.
If I have some time, I may create a more detailed post and put it on one of the Microsoft USENET groups, and see if I can get a better response from Microsoft.
Otherwise, instability like this is a major black mark against what is otherwise a very competent and usable database.
Anyway, thanks again for your help.
Regards
Adam
-
I've seen similar things happen, when a proc running fine suddenly gets a new plan that's horrible for performance. Even in the middle of the day.
We've never gotten a good response from Microsoft. Typically rebooting the server has cleared things out and built a good plan, but that's not a solution.
The addition of the new select with a variable is interesting. I wonder if it's just creating a new plan and you get a new one for some reason. I'm assuming statistics up to date, indexes defragged, etc. This is a sudden problem, not a degradation over time, right?
-
Steve,
I wondered if it was just creating a new plan, but I tried removing the new variable, and it reverted back to the old Execution Plan, so it really is treating the SQL in the stored procedure differently when this new variable is used. The variable has to be used, not just declared and populated. If you do this, but still use the variable as passed to the SP, you get the old execution plan.
Yes, it is a sudden problem. There's also a Maintenance Plan that optimises the database and checks integrity that runs once a week, so there should be no problems here.
The server has been running a month. To reboot it though would require a change ticket so that's not a route I want to go down just yet (although I will be checking the SP after the next time it's rebooted!)
Regards
Adam
-
Adam
Executing SET SHOWPLAN_TEXT ON will cause a text version of the execution plan to be generated. Just run it before your statement/procedure.
--------------------
Colt 45 - the original point and click interface
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply