August 11, 2008 at 7:14 am
I have a sql script which performs well until I try to encapsulate it in a stored procedure. The stored procedure has a single datetime parameter. During development, I simply DECLARE the variable to make it available. I also have several other variables that are declared at the beginning of the script. I get correct results from the script in < 1 second. When I CREATE PROCEDURE and put the datetime variable into the parameter list of the procedure, then EXEC the procedure, it takes as long as 4 minutes to execute on the same data and with the same result set.
I've seen similar behavior before, but always been able to rework the query and alleviate the performance issue. The common factor here seems to be if the procedure contains a CTE. I have many procedures that use CTEs and am usually pleased with the results, but this one issue continues to pop up, and I was hoping someone could shed some light. Is there anything unusual about CTEs (or stored procedures) that might cause them to work well in a direct query, but lose performance in a stored procedure?
I'll be happy to post the code if anyone would like to see it, but I assure you the only difference is that the single parameter is implied in the header, rather than DECLAREd later.
Thanks in advance.
August 11, 2008 at 7:22 am
I'm not 100% sure on this, but I have seen a few people with problems similar to this.
A lot of the time it is down to something called "Parameter sniffing" !?!?! not all that clued up it but might be worth a search.
At the beginning of your procedure try this.
[Code]
DECLARE @newParam DATETIME
SET @newParam = @InputParam
[/code]
Then replace your input param in code with the new one and see if that helps?
Sounds strange I know but, plesae try it 😉
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 11, 2008 at 7:39 am
That did it Christopher. I'd heard the term 'parameter sniffing' but I did not realize this is what it meant. It actually crossed my mind to try replacing the input parameter with a DECLAREd parameter, but it seemed too strange and too easy. Wish I'd tried it a long time ago..
I can't tell you how valuable this is to me. Thanks again!
August 11, 2008 at 7:41 am
Glad to be of help 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply