Stored Procedures - Less parameters = More or less cost?

Question

Stored Procedures - Less parameters = More or less cost?

dj1202

SSCommitted

Points: 1829
More actions
October 22, 2012 at 1:00 pm

#393439

Hello everyone, I am working on a report that is passing parameters to a stored procedure for its dataset. I was thinking of simplifying the stored procedure and removing some parameters. I would think that this would allow the report to run faster and more efficient since less parameters would have to be passed to the sp. However, I understand that this would pull more data from the data source, which gives me reason to believe that this may in fact result in less efficiency. Which would be correct in this case?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

dj1202 (10/22/2012)
Hello everyone, I am working on a report that is passing parameters to a stored procedure for its dataset. I was thinking of simplifying the stored procedure and removing some parameters. I would think that this would allow the report to run faster and more efficient since less parameters would have to be passed to the sp. However, I understand that this would pull more data from the data source, which gives me reason to believe that this may in fact result in less efficiency. Which would be correct in this case?

With no more details than this I would say "it depends". In almost any real world situation you will suffer far worse performance issues by returning more data than needed instead of filtering the data prior to returning it to the client. Just the nature of passing a parameter is so negligible on performance I am not even sure you can measure the difference.

If performance of the query is at question, removing parameters is NOT the way to improved performance.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

haiao2000 Hall of Fame Points: 3734 More actions · Answer 2

it sounds like the exact oposite is true (again it depends) but take the examples below

method #1

create procedure SP1

@p1 int

,@p2 int

,@p3 int

,@p4 int

as

select * from SomeTable

where p1=@p1 and p2=@p2 and p3=@p3 and p4=@p4

--method #2

create procedure SP2

@p1

as

declare @p1 int,@p2 int,@p3 int,@p4 int;

set @p2= select p2 from sometable;

set @p3= select p2 from sometable;

set @p4= select p2 from sometable;