Multiple queries in the same stored procedure
-
Our developers have gotten this idea lately that instead of having many small stored procedures that do one thing and have small parameter lists that SQL can optimize query plans for, its better to put like 8-10 different queries in the same stored procedure.
They tend to look like this:
create procedure UberProc (@QueryId varchar(50))
as
if @QueryId = 'First Horrible Idea'
begin
select stuff from something
end
if @queryid = 'Second really bad idea'
begin
select otherstuff from somethingelse
end
I see the following problems with this practice:
1) SQL can't cache the query plan appropriately
2) They are harder to debug
3) They use these same sorts of things for not just gets, but also updates, with lots of optional NULLable parameters that are not properly handled to avoid parameter sniffing.
Am I in the wrong that this is a really bad idea?
-
Manic Star (9/16/2015)
Our developers have gotten this idea lately that instead of having many small stored procedures that do one thing and have small parameter lists that SQL can optimize query plans for, its better to put like 8-10 different queries in the same stored procedure.They tend to look like this:
create procedure UberProc (@QueryId varchar(50))
as
if @QueryId = 'First Horrible Idea'
begin
select stuff from something
end
if @queryid = 'Second really bad idea'
begin
select otherstuff from somethingelse
end
I see the following problems with this practice:
1) SQL can't cache the query plan appropriately
2) They are harder to debug
3) They use these same sorts of things for not just gets, but also updates, with lots of optional NULLable parameters that are not properly handled to avoid parameter sniffing.
Am I in the wrong that this is a really bad idea?
You are NOT wrong at all. This is a bad idea because the performance is going to suffer.
Here are a couple of articles about this type of thing from Gail.
https://www.simple-talk.com/content/article.aspx?article=2280
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/[/url]
_______________________________________________________________
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/ -
Sean Lange (9/16/2015)
Manic Star (9/16/2015)
Our developers have gotten this idea lately that instead of having many small stored procedures that do one thing and have small parameter lists that SQL can optimize query plans for, its better to put like 8-10 different queries in the same stored procedure.They tend to look like this:
create procedure UberProc (@QueryId varchar(50))
as
if @QueryId = 'First Horrible Idea'
begin
select stuff from something
end
if @queryid = 'Second really bad idea'
begin
select otherstuff from somethingelse
end
I see the following problems with this practice:
1) SQL can't cache the query plan appropriately
2) They are harder to debug
3) They use these same sorts of things for not just gets, but also updates, with lots of optional NULLable parameters that are not properly handled to avoid parameter sniffing.
Am I in the wrong that this is a really bad idea?
You are NOT wrong at all. This is a bad idea because the performance is going to suffer.
Here are a couple of articles about this type of thing from Gail.
https://www.simple-talk.com/content/article.aspx?article=2280
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/[/url]
Now I feel vindicated, lol.
-
You could meet halfway by doing something like this:
create procedure UberProc (@QueryId varchar(50))
as
if @QueryId = 'First Horrible Idea'
begin
EXEC OneProcedure @PossibleParameter
end
if @queryid = 'Second really bad idea'
begin
EXEC AnotherProcedure @AnotherParameter
end
I'm not sure if this would be applicable to your situation, but it could be an option that reduces some problems.
-
That's not a bad idea at all. Will need to set up some test benches to make sure the QA is acting the way I think it should be.
-
Is there a particular reason why your developers want to do this, some weird security reasons that make creating multiple procedures an issue? Or do they think it's just generally a good idea?
-
ZZartin (9/16/2015)
Is there a particular reason why your developers want to do this, some weird security reasons that make creating multiple procedures an issue? Or do they think it's just generally a good idea?I think they like having all the code in one container.
-
Manic Star (9/16/2015)
ZZartin (9/16/2015)
Is there a particular reason why your developers want to do this, some weird security reasons that make creating multiple procedures an issue? Or do they think it's just generally a good idea?I think they like having all the code in one container.
Do they write all their programming logic in a single file? Or all of their logic in a single method?
I don't know why so many developers want everything in sql wrapped up in a single procedure but have no problem creating 10,000 class files. It makes no sense to me.
Many thanks to Lowell for this awesome image. I have used it many times since he originally created it.
_______________________________________________________________
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/ -
Sean Lange (9/16/2015)
I don't know why so many developers want everything in sql wrapped up in a single procedure but have no problem creating 10,000 class files. It makes no sense to me.I think it's because most application developers think a database is nothing more that a "data persistence" mechanism for their application... Like a giant, centrally located, application cookie.
That would also explain why there are so many unnormalized tables out there that have columns that are an exact match to the fields on some app screen.
-
" Like a giant, centrally located, application cookie. "
LOL Yep
-
My co-dba and I wrote some really bad poetry:
one proc to rule them
one proc to bind them
one proc to bring them all and in the darkness, deadlock them
'precious deadlock' 😀
-
Manic Star (9/16/2015)
My co-dba and I wrote some really bad poetry:one proc to rule them
one proc to bind them
one proc to bring them all and in the darkness, deadlock them
'precious deadlock' 😀
LOL That's awesome!!!
_______________________________________________________________
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/ -
Luis Cazares (9/16/2015)
You could meet halfway by doing something like this:create procedure UberProc (@QueryId varchar(50))
as
if @QueryId = 'First Horrible Idea'
begin
EXEC OneProcedure @PossibleParameter
end
if @queryid = 'Second really bad idea'
begin
EXEC AnotherProcedure @AnotherParameter
end
Yup, that's one of the solutions in the simple-talk article.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Of course, SQL Server could certainly benefit by implementing the Package concept that Oracle provides.
You can bundle all your related procedures and functions into a single Package for portability and maintenance, but internally they are all individual modules.
This avoids the ugliness and confusion of trying to do lots of different, unrelated things in a single procedure, yet you have all the source code in a single source file (ok, usually two with separate Package definition and body files).
There are LOTS of times when I need to use a simple local function within a stored procedure, but have to create and maintain a separate external source file to implement the function.
-
Sean Lange (9/16/2015)
Many thanks to Lowell for this awesome image. I have used it many times since he originally created it.Thanks to Lowell for creating it and to you for posting it. I'm going to go use it on someone right now. 😀
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply