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

    Unanswered: Direct SQL vs Stored Procedures

    Hi,

    There are certian "routine things" that need to be queried in my database. Currently we can either do:

    1) SELECT * FROM myTable WHERE id = x

    OR

    2) We can use a stored procedure to do the above SQL for us...That way, if the table changes, or we make changes to the database etc. The calling code will remain unchanged and we just have to change the stored procedure code instead of anywhere that calls it.

    What would you say are any disadvantages of doing the 2nd approach?

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by dbguyfh
    What would you say are any disadvantages of doing the 2nd approach?
    How did your teacher lay out the database for this assignment? Is the question taken directly from the book, or is it a practical/quizz question?

    -PatP

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by dbguyfh
    What would you say are any disadvantages of doing the 2nd approach?
    Quite often the FE devs get a bit disgruntled but otherwise none really.

    Most of the people on here, naturally gravitating towards the back end, would go with the second approach.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Or you could combine the two and pass table and column names into the sproc as parameters
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Naughty boy

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by pootle flump
    Naughty boy

    Hey, they would only need 1 sproc for the whole shebang(s?)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I see your single sproc and raise an EAV.

  8. #8
    Join Date
    Jan 2008
    Posts
    186
    Thanks for the reply guys.

    Basically I have performance in the back of my mind. If using a stored procedure decreases performance, then I would rather go with the more efficient approach of using SQL directly.

    What are your thoughts?

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    They are basically identical for such a simple statement. Check out Prepared Statements in BoL, as well as Parameter Sniffing for potential problems. Sprocs do not decrease performance as such, cached plan reuse typically improves performance but it can trash it.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are your client side apps concatenating values into SQL statements or using parameterised statements?

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    do you have any hot chicks in your programming classes this spring? that's what I miss. a new bunch of "study" buddies every semester.

    stored procedures for everything. the application gets no direct table access and neither does anyone else who is not a DBA and a system admin for that box.

    stored procedures tend to help performance. not hurt it. read about execution plans, the procedure cache and all of that good stuff.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  12. #12
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by pootle flump
    Are your client side apps concatenating values into SQL statements or using parameterised statements?
    Concatenating values... So something like:

    sqlStr = "SELECT * FROM X WHERE Y = " + Z

  13. #13
    Join Date
    Jan 2008
    Posts
    186
    Basically...The story is that our new system is currently under development and the database schema is not written in stone.

    At this point, we just want something up and running with the bare minimum, so we're almost guaranteed to have schema changes in the future...I want to minimze the amount of changes client apps would have to make if the schema changes (by using stored procedures instead of direct SQL).

    But looks like the general consensus is that there would be no notable performance drops, in fact it might even help performance.

    Thanks for the info everyone!

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    dynamic or ad-hoc sql will almost alway be less effecient, unless it's used a bunch of times, the it may get it's plan cached

    BUT

    Sprocs plans get cached and the optimizer does work when it get's compiled...can a wrong plan get cached...yes...but I've only seen it once

    Your biggest thing though is security

    No access to data except via a sproc

    Period

    All business logic in either sprocs or views

    I like to use views for business logic, because if the logic chnages, all you to do is change the view..the sproc and front are isolated from any impact
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by dbguyfh
    Basically...The story is that our new system is currently under development and the database schema is not written in stone.

    At this point, we just want something up and running with the bare minimum, so we're almost guaranteed to have schema changes in the future...I want to minimze the amount of changes client apps would have to make if the schema changes
    All the more reasons for using views

    And Don't use SELECT * anywhere
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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