SQL 2008 stored procedures and execution plans

15 pts.
Tags:
SQL Server 2008
SQL Server 2008 extended stored procedures
SQL Server stored procedures
Hi,

Is it any significant difference in performance between stored procedure with many parameters (some of them can be null sometimes) and creating many stored procedures that will contains only subset of this parameters ?

Thx,

 

ASKED: February 15, 2010  4:04 PM
UPDATED: February 16, 2010  3:39 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Buy having lots of extra parameters you may cause the SQL Server to use the wrong index because of the cached execution plan. Because of this it can be better to break them out. Each system is different, and SQL can sometimes deal with this just fine.

If you break out the options into a separate stored procedure you may be able to remove some JOINs from that query which will help reduce query run time.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Bink
    Thanks for reply :) I agree with you that for more advanced queries it will be better to split them into separate stored procedures. In my case the queries look like that: -- GetByLastName SELECT [Firstname], [LastName], [Fullname], [Locality], [Country] FROM [People].[User] Where ([Lastname] Like @Lastname -- GetByLastNameAndCountry SELECT [Firstname], [LastName], [Fullname], [Locality], [Country] FROM [People].[User] Where ([Lastname] Like @Lastname AND Country = @Country -- .... and more .. (ByFirstName, ByFullNameAndCountry ...) Simply i think that this can be done as one query (using IsNull() for example) without any visible performance decrease. And it will be more easy to maintain than having 10 stored procedures :). Anyway the reason im asking is that i had argument with my team mate at work about that, and im affraid that if we will take approach of creating so many stored procedures for all queries in our system we will have later problem with maintaining them :). Cheers,
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following