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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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 recoverability

    We 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