Query vs Stored Proc

[Bonediggler1]

Hi-

Why would a query using three CTEs take 30 seconds to run, and the same code as a SP takes 30 minutes?

The only difference is passing the parameter to the SP rather than declaring/setting it in code. The parameter is as follows:

DECLARE @ACCTGID INT
SET @ACCTGID = 201009

Thank you!!

 

[djj55]

How is the variable declared in your SP?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Bonediggler1]

CREATE PROCEDURE [BLAH].[sp_BLAH1]
@ACCTGID INT
AS
SET NOCOUNT ON;

ETC ETC

 

[gmmastros]

It is probably a [google]parameter sniffing[/google] issue.

Try adding WITH RECOMPILE to the stored procedure declaration to see if that solves the problem.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bonediggler1]

It's been running 5 minutes (query is 30 seconds) already with the WITH RECOMPILE addition.

I changed the CTE arrangement into temp tables and execution time is down to 50 seconds down from 30 minutes.

Wonder why that is?

Thanks for the help anyway. I didn't now about that WITH RECOMPILE feature...may come in handy in the future.

 

[gmmastros]

I've never been a fan of CTE's. Have you tried using a derived table approach? That usually gives better performance than temp tables. Not always, but usually.

Can you show the whole query?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

There is a great article by Peter Larsson explaining the difference in usage of temp table vs. table variable. I suppose it may apply to cte vs. temp tables as well.

Take a look

http://sqlblog.com/blogs/peter_lars...onsideration-when-using-a-table-variable.aspx

PluralSight Learning Library