|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Advatages of Stored Procedures?Currently we are building SQL statements using strings and string variables
depending on users selections and then passing the SQL statement into the command or connection object. I have been taught to use stored procedures however I am unclear as to the specific advantages of using stored procedures instead of building strings. Answers to the above is what I am seeking. Thanks. The only advantages (besides slight performance gain) are that dbaadmin has
more control over the database when you use stored procedures and if you need to change sp logic you don't need to modify your application (perhaps). And either way you should use parametrised values. -- Show quoteMiha Markic [MVP C#] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ "Sean" <S***@discussions.microsoft.com> wrote in message news:5C89CF14-B317-4C1D-8050-D45C82435E26@microsoft.com... > Currently we are building SQL statements using strings and string > variables > depending on users selections and then passing the SQL statement into the > command or connection object. I have been taught to use stored procedures > however I am unclear as to the specific advantages of using stored > procedures > instead of building strings. > > Answers to the above is what I am seeking. > > Thanks. > > Perfect that answers the question.
The time alone to change the legacy way it was done doesnt justify making sprocedures at this time given your answer. It would take more then a week to change it and the application isnt big enough to have a dba anyway. Show quote "Miha Markic [MVP C#]" wrote: > The only advantages (besides slight performance gain) are that dbaadmin has > more control over the database when you use stored procedures and if you > need to change sp logic you don't need to modify your application (perhaps). > And either way you should use parametrised values. > > -- > Miha Markic [MVP C#] > RightHand .NET consulting & development www.rthand.com > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ > > "Sean" <S***@discussions.microsoft.com> wrote in message > news:5C89CF14-B317-4C1D-8050-D45C82435E26@microsoft.com... > > Currently we are building SQL statements using strings and string > > variables > > depending on users selections and then passing the SQL statement into the > > command or connection object. I have been taught to use stored procedures > > however I am unclear as to the specific advantages of using stored > > procedures > > instead of building strings. > > > > Answers to the above is what I am seeking. > > > > Thanks. > > > > > > > Speed and performance are the reasons to use stored proc.
If you are processing thousands of records and preparing a report then stored proc will be much faster then fetching all the records and processing in .net application. But if the number of concurrent users are also in thousands then as all the stored proc will be on one server only then its disadvantage. We use stored proc a lot in our application where concurrent users are less then 100 and all data crunching processing is done in stored proc and performance is very good. Vinod Just to stress that the response mentioned parameterized queries i.e. you
should still likely at least use parameter place holder values for the queries instead of including yourself the values as text in your queries. It allows to avoid possible issues with text (text representation for dates, numbers etc...is culture dependant) plus it allows to avoid SQL injection attacks (i.e. by tweaking a value a malicious user could transform the intended SQL statement). -- Show quotePatrice "Sean" <S***@discussions.microsoft.com> a écrit dans le message de news:D81B8027-6A4E-475B-8598-C8ADA590AEBE@microsoft.com... > Perfect that answers the question. > > The time alone to change the legacy way it was done doesnt justify making > sprocedures at this time given your answer. It would take more then a week to > change it and the application isnt big enough to have a dba anyway. > > > > "Miha Markic [MVP C#]" wrote: > > > The only advantages (besides slight performance gain) are that dbaadmin has > > more control over the database when you use stored procedures and if you > > need to change sp logic you don't need to modify your application (perhaps). > > And either way you should use parametrised values. > > > > -- > > Miha Markic [MVP C#] > > RightHand .NET consulting & development www.rthand.com > > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ > > > > "Sean" <S***@discussions.microsoft.com> wrote in message > > news:5C89CF14-B317-4C1D-8050-D45C82435E26@microsoft.com... > > > Currently we are building SQL statements using strings and string > > > variables > > > depending on users selections and then passing the SQL statement into the > > > command or connection object. I have been taught to use stored procedures > > > however I am unclear as to the specific advantages of using stored > > > procedures > > > instead of building strings. > > > > > > Answers to the above is what I am seeking. > > > > > > Thanks. > > > > > > > > > > > > Yeah, he has a lot (and I mean a lot) of string manipulation to create these
SQL statements and doesn’t use SQL Parameters but instead string parameters passed into a method which then changes it. It just hit me tonight that his application is nearly 100% data centric and yet he doesn’t have a single dataset (he uses classes as pseudo-datasets). I am afraid I am going to have to approach this delicately because the entire approach might have to be de-designed to really take advantage of the whole point in upgrading from classic asp. I don’t understand the reasoning behind having 3 classes to populate a single data grid (but that could be my misunderstanding of the project as a whole). Show quote "Patrice" wrote: > Just to stress that the response mentioned parameterized queries i.e. you > should still likely at least use parameter place holder values for the > queries instead of including yourself the values as text in your queries. > > It allows to avoid possible issues with text (text representation for dates, > numbers etc...is culture dependant) plus it allows to avoid SQL injection > attacks (i.e. by tweaking a value a malicious user could transform the > intended SQL statement). > > -- > Patrice > > "Sean" <S***@discussions.microsoft.com> a écrit dans le message de > news:D81B8027-6A4E-475B-8598-C8ADA590AEBE@microsoft.com... > > Perfect that answers the question. > > > > The time alone to change the legacy way it was done doesnt justify making > > sprocedures at this time given your answer. It would take more then a week > to > > change it and the application isnt big enough to have a dba anyway. > > > > > > > > "Miha Markic [MVP C#]" wrote: > > > > > The only advantages (besides slight performance gain) are that dbaadmin > has > > > more control over the database when you use stored procedures and if you > > > need to change sp logic you don't need to modify your application > (perhaps). > > > And either way you should use parametrised values. > > > > > > -- > > > Miha Markic [MVP C#] > > > RightHand .NET consulting & development www.rthand.com > > > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ > > > > > > "Sean" <S***@discussions.microsoft.com> wrote in message > > > news:5C89CF14-B317-4C1D-8050-D45C82435E26@microsoft.com... > > > > Currently we are building SQL statements using strings and string > > > > variables > > > > depending on users selections and then passing the SQL statement into > the > > > > command or connection object. I have been taught to use stored > procedures > > > > however I am unclear as to the specific advantages of using stored > > > > procedures > > > > instead of building strings. > > > > > > > > Answers to the above is what I am seeking. > > > > > > > > Thanks. > > > > > > > > > > > > > > > > > > > > "Sean" <S***@discussions.microsoft.com> wrote in message --Well, i used to think there were a lot of advantages, but my buddy Frans news:5C89CF14-B317-4C1D-8050-D45C82435E26@microsoft.com... > Currently we are building SQL statements using strings and string > variables > depending on users selections and then passing the SQL statement into the > command or connection object. I have been taught to use stored procedures > however I am unclear as to the specific advantages of using stored > procedures > instead of building strings. > > Answers to the above is what I am seeking. handed me my a33 on a platter when I argued the point with him. Miha did a great job answering your question but if you're interested in a great read that will definitely give you some insights, this is a must read http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx Show quote > > Thanks. > > I am glad you pointed out this blog entry, because in order to decide to use
stored procedures, you have to know the good and bad. Certainly you can state these advantages: Performance, stored procs are retained in the procedure cache and the SQL plan is retained. Maintenance, when you discover a bug in your SQL, you can correct it without recompiling/redistributing your source. Reduced Roundtrip, weather you are using identity columns or temp tables, you will not have to round trip as often Cursor support, you can use a cursor in stored procedures (not an efficient performer, but it does exist) You may also cite the following disadvantages: Maintenance, as your application ages and needs changed, some stored procs may become orphaned and may reference invalid objects. Loss of debug, either in development or maintenance mode, debugging and tracing in-line SQL is much easier than stored procs. Performance, either your stored procs will have to be complex or numerous to accommodate all the variations of user intent. Field level updates and tacking are just not simply implemented in stored procs, meanwhile, business logic knows what should be done. I am sure there are more pro's and con's, certainly I recommend that which ever you choose remain consistant with it. Show quote "W.G. Ryan - MVP" <WilliamRyan@nospam.gmail.com> wrote in message news:OT%23%23BlwMGHA.3408@TK2MSFTNGP12.phx.gbl... > > "Sean" <S***@discussions.microsoft.com> wrote in message > news:5C89CF14-B317-4C1D-8050-D45C82435E26@microsoft.com... >> Currently we are building SQL statements using strings and string >> variables >> depending on users selections and then passing the SQL statement into the >> command or connection object. I have been taught to use stored procedures >> however I am unclear as to the specific advantages of using stored >> procedures >> instead of building strings. >> >> Answers to the above is what I am seeking. > --Well, i used to think there were a lot of advantages, but my buddy Frans > handed me my a33 on a platter when I argued the point with him. Miha did > a great job answering your question but if you're interested in a great > read that will definitely give you some insights, this is a must read > http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx >> >> Thanks. >> >> > > Ah, there are always going to be detractors that throw up roadblocks to SPs.
However, based on interviews I've done over the last 15+ years, I know that stored procedures are used by the vast majority of professional shops. SPs permit development teams to work with an abstraction layer that's easily understood, easily protected and easily managed. Properly written stored procedures run faster than ad hoc queries. SS is tuned to leverage SPs in cache whenever it can. In addition, as applications and databases grow in complexity developers find they quickly outgrow client-side ad hoc SQL. When you build applications with imbedded SQL you also run the risk of having to rewrite and redeploy applications when the schema or business logic changes. This can also be prevented by properly written SPs. SPs _are_ very easy to debug (now that Visual Studio has figured out how to do it seamlessly). -- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ "Sean" <S***@discussions.microsoft.com> wrote in message news:5C89CF14-B317-4C1D-8050-D45C82435E26@microsoft.com... > Currently we are building SQL statements using strings and string > variables > depending on users selections and then passing the SQL statement into the > command or connection object. I have been taught to use stored procedures > however I am unclear as to the specific advantages of using stored > procedures > instead of building strings. > > Answers to the above is what I am seeking. > > Thanks. > >  |
|||||||||||||||||||||||
Other interesting topics