Advatages of Stored Procedures?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
Sean said:
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.
Click to expand...
--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
 
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).

--
Patrice

Sean said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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.
 
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).


Patrice said:
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 said:
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 said:
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/

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.
Click to expand...
Click to expand...
Click to expand...
 
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).

--
____________________________________
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.
__________________________________
 
Back
Top