Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: Two stored procs that do the same thing?

    Hey guys,

    Yet another question from me today

    I have two stored procs:
    Code:
    getMessages( machine_name VARCHAR )
    getMessages( machine_id INT )
    They return the last 10 messages sent by a computer.

    Now, in theory (and so far, in practice) it is not possible to have two computers with the same name. However, I don't use "machine_name" as the primary key, because perhaps the name might still change.

    Anyway, so the above 2 sprocs allow me to get my messages by either passing the machine name, or the machine's ID. Obviously the first (machine name) is more convienent.

    In my middle layer DLL I have two methods that wrap the latter sprocs.
    Code:
    GetMachineMessages( string machineName );
    GetMachineMessages( int machineId );
    Which call the above 2 procedures respectively.

    The problem: I don't like have the two sprocs which essentially do the same thing, except use a different field in the WHERE clause.

    My Question: What if, instead of creating that "getMessages(VARCHAR)" procedure, I only keep the getMessage( INT )... But in my middle layer DLL, I do something like this:
    Code:
    string[] GetMachineMessages( string machineName ) {
        // Get the ID via name thru a separate proc. 
        // This proc already exists, and I'm using it in other places too
        machineId = getMachineId( machineName );
    
        // Now that we have the Id, just call our original function
        return GetMachineMessage( machineId );
    }
    What do you guys think of that approach. I mean... Yeah, it's an extra hit to the database by calling getMachineId()... But will it make that much of a difference?

    I feel like I'm cluttering the database if I were to keep getMessage(VARCHAR) and getMessage(INT) because they both do the same thing essentially.

    What are your thoughts?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    That is obviously a cleaner approach. Back in the days I would also do a reverse, which resulted in having 2 procedures any way. But the number of back-end methods never bothered me, as long as I document and justify the presence of each.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'd write a single sproc with two optional parameters...
    ...or a single varchar parameter that I evaluate to see whether it is numeric or not.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2007
    Posts
    62
    If you are sure of no duplicates then
    a) enforce a unique constraint on machine name and
    b) just implement 2 sps where one is
    GetMachineMessages((select machineid from machines where machinename=@name))
    which will save you a hit.

    If you can't enforce the constraint then you need 2 sps anyway, but one with IN or a join.

    No you don't. Posting while drunk.
    Last edited by LoztInSpace; 06-20-08 at 05:47.

  5. #5
    Join Date
    Jan 2008
    Posts
    186
    Great, thanks for the replies guys.

    So basically I see two solutions

    1) Blindman says to create only 1 backend sproc, but have 2 optional parameters (one VARCHAR for name, and one INT for the id). That way, it solves my uneasyness of having two backend sprocs that accomplish the same thing

    2) LoztInSpace says I should keep the 2 separate sprocs, because it saves me a hit to the db, but also put a UNIQUE constraint on the machine name.

    In either method, it saves me a hit to the database because I'm not using a separate middle-layer DLL method to get the ID, rather I'm getting it while I'm already in the database.

    As many of you might have guessed, I'm new to DB programming... Is this something that you guys find often? The need to run the same sprocs -- but with a different criteria? (in my case, either ID, or "name")

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, I see that very often for search procedures.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by blindman
    Yes, I see that very often for search procedures.
    And so generally, do most people take the 1-sproc-with-2-optional parameters route? Or 2-sproc route?

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Having 1 procedure means that you will have conditional logic in it:
    if @id is not null
    select...where id=@id
    else
    select...where name=@name

    This means that you will have 2 potential execution plans that a procedure may use. But this is not possible, because a procedure plan gets compiled on the first execution, meaning that depending on the parameter passed only 1 plan will be available for consequent executions. If the parameter changes, either the same plan will be reused with highly degraded performance impact, or the new plan will have to be generated (plan recompilation) that will satisfy the parameter change. This is why I recommend to have 2 procedures.

    Another alternative will be to have 2 functions inside this stored procedure. 1 for ID and another one for name. This way a procedure has 1 plan that won't change, and each function will have its own plan that will be used accordingly without recompilation.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Jan 2008
    Posts
    186
    Ahhh you make a very convincing point about having the execution plan re-compile everytime the sproc is called with a parameter change.

    But hmm... I always thought that a plan is compiled independent of what parameters were passed? Are you saying that anytime the paramters to a proc change to different values, then it gets recompiled?

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    In the scenario that I described - YES. Think about it, - the access to the data changed, and query processor can only go by what the optimizer creates, - how to access requested data. If the existing plan is based on ID field, and you're passing the NAME parameter, - how would QP know that it's the same row?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by rdjabarov
    In the scenario that I described - YES. Think about it, - the access to the data changed, and query processor can only go by what the optimizer creates, - how to access requested data. If the existing plan is based on ID field, and you're passing the NAME parameter, - how would QP know that it's the same row?
    Ohh okay, so lets see if I understand this right.

    If my sproc takes 2 params for either ID or Name, it will recompile everytime I switch between ID and name.

    Otherwise, if I create an sproc with only 1 param (the ID), it will not have to re-compile everytime, because I'm using the same field (ID) for my search

    Is that right?

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Recompiling is not guaranteed, but is highly possible. If the cost of generating the plan is low, recompilation is most likely. The worst thing is when inadequate plan is used to execute the statement. This happens when the optimizer determines that recompiling the plan is very costly and may take more time than executing the statement using existing plan.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, I avoid recompiling but simply looking up the surrogate key based upon the natural key if the surrogate key is not submitted.

    set @Surrogate = coalesce(@Surrogate, (select Surrogate from Table where Natural = @Natural))
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Actually, if on the first execution @Surrogate is not null, and in any of the consequent executions @Surrogate is null, then at that time the plan may have to be recompiled (see recompilation conditions in post #12), because the original plan did not contain select Surrogate from Table where Natural = @Natural. In 2005 and later the optimizer may decide to only recompile that particular statement though.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    also think about adding a check to enforce only one of the pair (surrogate,natural) is not null.

    if both are not null, blindman's code will use the surrogate without error, but that may not be what the client expects. my instinct is to raise an error in such a case.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •