many stored procedures vs 1

  • elaine-131617

    Ten Centuries

    Points: 1066

    We have several asp pages querying the same table, just different columns. I'm moving these queries into stored procedures. I had thought of writing just one stored procedure, taking an input parameter to indicate which columns to return, and then do something like:

    if @parm = 'bills'

    select bills from table

    if @parm = "name"

    select name from table

    Is this OK, or would it be better to write many stored procedures? Does it matter as far as performance goes?

    Thanks

    elaine

  • abmore

    Hall of Fame

    Points: 3628

    Its always good practice to differentiate your objects, so it won't affect your functionality. In this case instead of writing IF ELSE you can also create a Dynamic query which takes the column name as input parameter

    e.g: SELECT "+ @param +" FROM table

  • thegreatsamarth

    Mr or Mrs. 500

    Points: 578

    elaine (7/17/2008)


    We have several asp pages querying the same table, just different columns. I'm moving these queries into stored procedures. I had thought of writing just one stored procedure, taking an input parameter to indicate which columns to return, and then do something like:

    if @parm = 'bills'

    select bills from table

    if @parm = "name"

    select name from table

    Is this OK, or would it be better to write many stored procedures? Does it matter as far as performance goes?

    Thanks

    elaine

    Hi Elaine,

    Why u r unnecessarily using "IF", Also it is better to use just one Stored Procedure.

    Refer following code:

    ----------------------------------------------------------

    DECLARE @parm VARCHAR(50)

    DECLARE @SQLstmt VARCHAR(max)

    SET @parm = 'bills'

    SET @SQLstmt = 'SELECT ' + @parm + ' FROM table'

    EXEC (@SQLstmt)

    ----------------------------------------------------------

    Samarth

  • elaine-131617

    Ten Centuries

    Points: 1066

    I thought it was better to stay away from dynamic sql, which is why i did all the if statements.

  • Grant Fritchey

    SSC Guru

    Points: 396605

    Actually, if I were doing it, first, I'd try to avoid dynamic sql unless you use sp_executesql and pass it parameters (this creates execution plans that are reusable). For what you want, you could have a single stored procedure act as a wrapper and then within the IF statements have it call other stored procedures. This way, each individual stored procedure can get it's very own execution plan. Otherwise, you are reduced to dynamic sql or, you get a recompile almost every time the stored procedure is called. This happens because the first time through it hits your first IF statement and executes, creating a nice plan for that first query. The second time through it passes the first IF and goes into the second. It can't use the execution plan in cache, so it dumps it and creates a beautiful new plan for the second query. The third time through, it hits the first IF statement.... You get the picture.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • elaine-131617

    Ten Centuries

    Points: 1066

    I wasn't sure if the execution plan would be reused or not, but was suspicious that it might not be. Thanks for your explanation and the suggestion for using this first stored procedure as a wrapper. I will try that.

    Thanks very much

    Elaine

  • meichner

    SSCertifiable

    Points: 5563

    I would take a slightly different approach. I would create one stored procedure that returns all of the fields from the table. I would then do one of the following:

    a) Have the program/class (depending on the language) that executes the stored procedure and returns the data to the webpage do the If statement or

    b) Have a two different programs/classes execute the one stored procedure returning the specific information that each webpage needs.

    Using either method you only have one stored procedure that doesn't need to know anything about the program that executed it.

    The benefit to option b is if you ever decided you need to return different fields from that same table for a different page, you don't have to change the stored procedure or any existing code. All you would need to do is to create a new program/class to return only the data needed for this page.

    Of course, this is just my opinion.

  • Jack Corbett

    SSC Guru

    Points: 184381

    meichner (7/17/2008)


    I would take a slightly different approach. I would create one stored procedure that returns all of the fields from the table. I would then do one of the following:

    a) Have the program/class (depending on the language) that executes the stored procedure and returns the data to the webpage do the If statement or

    b) Have a two different programs/classes execute the one stored procedure returning the specific information that each webpage needs.

    Using either method you only have one stored procedure that doesn't need to know anything about the program that executed it.

    The benefit to option b is if you ever decided you need to return different fields from that same table for a different page, you don't have to change the stored procedure or any existing code. All you would need to do is to create a new program/class to return only the data needed for this page.

    Of course, this is just my opinion.

    I would have to disagree with this as best practices for performance stipulate that you should only return the data you need. It also would mean that you may end up with bookmark (key) lookups because you are not using a covering index because of the extra data returned.

    I would agree with Grant that you should use multiple stored procedures, but I would likely implement it in the asp pages as you state that

    We have several asp pages querying the same table, just different columns.

    . What do you really gain by having the one "master" procedure? If you had one asp page with a dropdown list that changed the output then I would consider the master procedure, but calling from multiple pages I would directly call the correct procedure. You also do not mention if there are different search criteria for each call, which would probably change the solution as well.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • Ron McCullough

    SSC Guru

    Points: 63877

    You should take into consideration the danger of using dynamic SQL statements, in that they provide a path through database security for injection attacks, and unauthorized data access.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Grant Fritchey

    SSC Guru

    Points: 396605

    I'm happy to see someone else come out with the old data saw, only move the data you need and only when you need it. I would have added that.

    As to the dynamic SQL being a security risk... only if you do it wrong.

    If you do this:

    DECLARE @mysql nvarchar(max)

    SET @mysql = 'SELECT * FROM ' + @TableName + ' WHERE ' + @MyParam

    EXEC @mysql

    Then yeah, you're dead.[/url] But you can build your strings & execute them like this (from BOL):

    DECLARE @IntVariable int;

    DECLARE @SQLString nvarchar(500);

    DECLARE @ParmDefinition nvarchar(500);

    /* Build the SQL string one time.*/

    SET @SQLString =

    N'SELECT * FROM AdventureWorks.HumanResources.Employee

    WHERE ManagerID = @ManagerID';

    SET @ParmDefinition = N'@ManagerID tinyint';

    /* Execute the string with the first parameter value. */

    SET @IntVariable = 197;

    EXECUTE sp_executesql @SQLString, @ParmDefinition,

    @ManagerID = @IntVariable;

    /* Execute the same string with the second parameter value. */

    SET @IntVariable = 109;

    EXECUTE sp_executesql @SQLString, @ParmDefinition,

    @ManagerID = @IntVariable;

    That's safe.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • GSquared

    SSC Guru

    Points: 260824

    From a DBA point of view, I'd go with multiple procs, no master proc. From a "make life easy on the devs" point of view, I'd go with the master proc and a parameter for columns.

    But keep in mind that the master proc is going to have to be rewritten often, as more apps are built or as changes cause the columns-needed list to vary.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 11 posts - 1 through 11 (of 11 total)

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