Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: Stored procedure performance when attaching parameters?

    About 75% of my stored procedures can possibly produce an "output message" which is sent back to the client via an OUTPUT parameter
    Code:
    CREATE PROCEDURE myProc
    (@outputMsg VARCHAR(100))
    AS
    ...
    My client-code is written such that I never call the SqlCommand.ExecuteReader() directly; everything is encapsulated with an "ExecuteCommand" routine. Within the "ExecuteCommand" routine, I automatically attach an output parameter called "outputMsg" since 75% of my procs use it.

    The problem: Only 75% use it, the other 25% don't need it. If don't include that outputMsg parameter in those procedures, then my "ExecuteCommand" routine generates an error saying something like "No parameters were expected by 1 was found" -- which is correct, because the procedure does not take any paramters, but my "ExecuteCommand" method is set up to automatically include that "outputMsg" parameter since 75% of the procedures use it.

    As a workaround, I just included that "outputMsg" to EVERY stored procedure even though 25% of them don't need it.

    My question: Would this produce a noticible performance difference? By "this" I mean the fact that I'm attaching output parameters to procedures for procedures that don't even use them.

    Thanks!

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by dbguyfh
    Would this produce a noticible performance difference?
    I doubt it.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If you didn't notice a difference then I would say "no". Otherwise, I'm gonna go with "yes"

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Pootle, you are obviously mistaken here.
    If you DID notice a difference, then I would say "Yes". Otherwise, I'd go with "No".
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Jan 2008
    Posts
    186
    Well I have no real way of measuring this...

    The only overhead I could see is binding the parameters to the procedure. I don't even make use of the parameters after that, so I can't see anything else affecting it.

    Is there a way to measure this? Or should I just assume that the difference is negligible?

  6. #6
    Join Date
    Sep 2005
    Posts
    161
    It's negligible. It would be like driving your car barefoot because the weight of your shoes affects your car's performance. While it would make a difference, who could measure it?

  7. #7
    Join Date
    Jan 2008
    Posts
    186
    Sounds good. Thanks

Posting Permissions

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