none
hi all , small help about Stored Procedure ! RRS feed

  • Question

  • hi all , thank u for all member here for ur efforts and answering great answers for users , in fact i have asked many questions , i dont know if its allowed , or not allowed , But i promisse u this is last question if its not allowed , or i will continue if its allowed:)

    i have searched in google about storded procedure and i can create it and dealing with it, but my questions when we should use it?? i have searched for this question and i found the answer , the answer was to prevent sql query from running many times , and we should use it in case sql command running many time , in my mind i find that evey sql query running many time ,  hmmmmmmmmm

    there is any simple example?? and what meant by many time?? , its idle to use stored procedure in all cases ,it will increase the performance of system??
    Monday, July 27, 2009 9:30 PM

Answers

  • hi,

    >hi all , thank u for all member here for ur efforts and answering great answers for users , in fact i have asked many
    > questions , i dont know if its allowed , or not allowed , But i promisse u this is last question if its not allowed , or i will
    > continue if its allowed:)

    it is allowed :)... it's the way to go and the spirit of the forum :)

    >but my questions when we should use it?? i have searched for this question and i found the answer ,
    >the answer was to prevent sql query from running many times , and we should use it in case sql command
    > running many time , in my mind i find that evey sql query running many time ,  hmmmmmmmmm

    IMVHO, stored procedures should be the only mean to access data in a database... you should use them to retrieve data from it and to modify data as well.. this allows you to protect your base tables not allowing direct access to them, so that you define the minimum path to access the underlying data.. having a procedure or a limited (and well known) set of them grants you the ability to completely decouple the database from the presentation layer.. you can for instance change the name of a table or a column and what you need to let the applications accessing the data continuing working is just modify the code of the related procedures.. so a deploy is just limited to recompile the metadata of the new procedures instead of deploying the whole application as well [obviously, after the required test period :) ].. more, you can define and code a whole business layer inside them, checking parameters and data against the underlying and existing data and so on..
    knowing that only you procedures are allowed to access the base tables and data allows you to eventually fine tune the code running poorly in a snap.. on the contrary, relying on applications submitting dynamic sql, you have to test and check the whole app(s)..
    and many more arguments, if you want.. as security concerns where you can define and set the permissions on the appropriate procedures for all relevant groups of users and having them negated the access to the tables (and all is circular again, as all the pros come back again)...
    but this usually is a "religious" question.. you usually have people only submitting dynamic sql code, and unfortunately Linq pushes this trend on, and you have others (like me) that complitely wrote management systems only allowing procedures and not a single direct access to the data.. the half way is usually not a concern.. you usually totally go one way or the other.. the "mix" is often used when Reporting Services comes to play, as  BI integration often requires to let users complete or quiet free form "asking" about the data and results.. having said all that, I'm totally a stored procedure guy :)

    on the "systematic" approach, nothing claims to really use procedures.. if the apps submitting dynamic sql are decently written, their dynamic sql usually  gets the same "performance" as procedure uses to, as the engine becomes every version more smart and performant, letting query plans reuse the same way procedure's use allows.. but it's clear that repeating submitting "quite the same" query but not exactly "the same" heavily compromises performances as plans and cache often get discharged and recompiled or/refetched... this does not happens using procedures, even if parameter sniffing could compromise performance as well..
    again, this quiet is a "religious" question.. "do you believe in procedures and all the good it brings in: security, politnes, ease of coding, often ease of porting on other dbms, elegance and clean access, ease of changes at the database metadata level, ..."?
    then, you should become a "procedure guy" :)
    if you don't, you are a "dynamic sql guy" [ :( ], and nothing hurts in this vision as, again, Linq and Entity Framework unfortunately push this way on drammatically.. I do not really understand the basis on that, as moving on from ADO to ADO.Net helped a lot moving away from dynamic sql, but it's here again :( :)
    so, my last word is "dynamic sql is evil to me", but it's not for all the people out there :)

    Conor, by the Query Processing team of SQL Server often releases great blogs like http://blogs.msdn.com/conor_cunningham_msft/archive/2009/06/03/conor-vs-dynamic-sql-vs-procedures-vs-plan-quality-for-parameterized-queries.aspx

    another great opnion can be found at http://sqlblog.com/blogs/paul_nielsen/archive/2009/05/09/why-use-stored-procedures.aspx

    there's out a video of about 1hour about this debate, with great people there, but I'm unable to find the link now.. at the end of the game, obviously no one wins or loses, it's a matter of taste, requirements, and obviously good sense.. :)

    again, all this is IMVHO..
    regards
    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools
    Monday, July 27, 2009 10:47 PM
    Moderator

All replies

  • hi,

    >hi all , thank u for all member here for ur efforts and answering great answers for users , in fact i have asked many
    > questions , i dont know if its allowed , or not allowed , But i promisse u this is last question if its not allowed , or i will
    > continue if its allowed:)

    it is allowed :)... it's the way to go and the spirit of the forum :)

    >but my questions when we should use it?? i have searched for this question and i found the answer ,
    >the answer was to prevent sql query from running many times , and we should use it in case sql command
    > running many time , in my mind i find that evey sql query running many time ,  hmmmmmmmmm

    IMVHO, stored procedures should be the only mean to access data in a database... you should use them to retrieve data from it and to modify data as well.. this allows you to protect your base tables not allowing direct access to them, so that you define the minimum path to access the underlying data.. having a procedure or a limited (and well known) set of them grants you the ability to completely decouple the database from the presentation layer.. you can for instance change the name of a table or a column and what you need to let the applications accessing the data continuing working is just modify the code of the related procedures.. so a deploy is just limited to recompile the metadata of the new procedures instead of deploying the whole application as well [obviously, after the required test period :) ].. more, you can define and code a whole business layer inside them, checking parameters and data against the underlying and existing data and so on..
    knowing that only you procedures are allowed to access the base tables and data allows you to eventually fine tune the code running poorly in a snap.. on the contrary, relying on applications submitting dynamic sql, you have to test and check the whole app(s)..
    and many more arguments, if you want.. as security concerns where you can define and set the permissions on the appropriate procedures for all relevant groups of users and having them negated the access to the tables (and all is circular again, as all the pros come back again)...
    but this usually is a "religious" question.. you usually have people only submitting dynamic sql code, and unfortunately Linq pushes this trend on, and you have others (like me) that complitely wrote management systems only allowing procedures and not a single direct access to the data.. the half way is usually not a concern.. you usually totally go one way or the other.. the "mix" is often used when Reporting Services comes to play, as  BI integration often requires to let users complete or quiet free form "asking" about the data and results.. having said all that, I'm totally a stored procedure guy :)

    on the "systematic" approach, nothing claims to really use procedures.. if the apps submitting dynamic sql are decently written, their dynamic sql usually  gets the same "performance" as procedure uses to, as the engine becomes every version more smart and performant, letting query plans reuse the same way procedure's use allows.. but it's clear that repeating submitting "quite the same" query but not exactly "the same" heavily compromises performances as plans and cache often get discharged and recompiled or/refetched... this does not happens using procedures, even if parameter sniffing could compromise performance as well..
    again, this quiet is a "religious" question.. "do you believe in procedures and all the good it brings in: security, politnes, ease of coding, often ease of porting on other dbms, elegance and clean access, ease of changes at the database metadata level, ..."?
    then, you should become a "procedure guy" :)
    if you don't, you are a "dynamic sql guy" [ :( ], and nothing hurts in this vision as, again, Linq and Entity Framework unfortunately push this way on drammatically.. I do not really understand the basis on that, as moving on from ADO to ADO.Net helped a lot moving away from dynamic sql, but it's here again :( :)
    so, my last word is "dynamic sql is evil to me", but it's not for all the people out there :)

    Conor, by the Query Processing team of SQL Server often releases great blogs like http://blogs.msdn.com/conor_cunningham_msft/archive/2009/06/03/conor-vs-dynamic-sql-vs-procedures-vs-plan-quality-for-parameterized-queries.aspx

    another great opnion can be found at http://sqlblog.com/blogs/paul_nielsen/archive/2009/05/09/why-use-stored-procedures.aspx

    there's out a video of about 1hour about this debate, with great people there, but I'm unable to find the link now.. at the end of the game, obviously no one wins or loses, it's a matter of taste, requirements, and obviously good sense.. :)

    again, all this is IMVHO..
    regards
    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools
    Monday, July 27, 2009 10:47 PM
    Moderator
  • Andera ur great helper :** thank u

    i vote for u 100000000%

    i love u :>
    Tuesday, July 28, 2009 12:07 PM