March 7, 2013 at 8:44 am
hi friends i have small doubt in sql plese tell me
how to Determine when to use stored procedures vs. SQL in the code
March 7, 2013 at 8:53 am
If you don't use any ORM in our application, then you better place all your SQL code into stored procedures.
There are no standard rules which specify where to use stored proc or application embedded SQL code. However it's considered by many to be the best practice to use stored procs!
March 10, 2013 at 4:45 am
Using stored procedures has also the following benefits.
Security : dba can determine who can execute what
Tuning : mostly stored procedures are written by sql developpers who mostly know to find the most performant ways to manage the objective at hand.
Modify : when bussiness logic changes there is no need to wait for the application developper but can be handled by all sql developpers
and in most cases because stored procedures are compiled code there could be performance benefits..
Hopes this helps you in your way of thinking.
Wkr,
Van Heghe Eddy
March 11, 2013 at 5:37 am
My suggestion is: avoid stored procedures at all, unless you have very special needs of performance on some complicated operations.
I know that here, at SQLServerCentral, many people are skilled DB administrators, and they like s.p. that give them much power. But if you are a developer, like me, it is better not to use them for the following reasons:
- Portability: there are many types of DB out of there, tomorrow you could find a customer who wants to run the application with Oracle, MySql, Db2, Access, ..., or even your single customer can change his mind about which is the best Db suitable for his needs.
There is a myth around that tells "use SP as much as you can" but I think this is just propaganda by the database producers. If you use s.p. then you can't change Db type easily, and this is exactly what they want.
SQL is a standard, s.p. are not.
There are some subtle differences in SQL syntax among different producers, but if you program in a smart way, you can work with them. S.p. instead must be completely rewritten.
- Maintenance: if business logic change, you probably need to change something into the code and something into the DB structure; it is easier to have only one place where to look for, the application code. If you use s.p. you should always check all s.p. code if you need to make a change.
- Security: the customer decides who can execute what, by assigning each user to one or more usergroups. You, the developer, decide which usergroups can execute what, by identifying the user.
I suggest you keep tables for users, usergroups, and rights, and avoid using DB users for anything other than distinguish db administrators from normal db users.
When the user is logged in you know which rights he has: for example, if your application has a pushbutton for "Delete all records" and you want that only some users can push it, you will define a right for this and your code will disable or hide that button if the user hasn't the right.
Someone says "dba can determine who can execute what", but what does this means ? That if you, by mistake, let a user run a procedure that it is not allowed to, the user will receive a difficult to undestand error. This is not the best solution, the best solution is that the application does not allows users to do things they are not allowed to.
I definitely think that s.p. should be reserved to very special needs, and I think this happens not more than 1 % of the times you need to do a Db operation from a normal application.
Bye
Francesco Muzul
P.S. 1: I am a C++ developer with 25 years of experience, working with SQLServer since 1995, but also with Oracle, Access, SQLite and others.
P.S. 2: sorry for my imperfect english, I'm italian.
March 11, 2013 at 5:55 am
Well, I think you've got both ends of the argument there in the last couple of posts!
But what's with the paranoia over DBA's Francesco? We're not all bad 🙂
March 11, 2013 at 6:03 am
Van Heghe Eddy (3/10/2013)
Using stored procedures has also the following benefits.Security : dba can determine who can execute what
Tuning : mostly stored procedures are written by sql developpers who mostly know to find the most performant ways to manage the objective at hand.
Modify : when bussiness logic changes there is no need to wait for the application developper but can be handled by all sql developpers
and in most cases because stored procedures are compiled code there could be performance benefits..
Hopes this helps you in your way of thinking.
Wkr,
Van Heghe Eddy
Security - can be also achieved without use of stored procedures (eg. via table & vieww access)
Tuning - the way you've put it is very optimistic! In my experience I've found too many cases where stored procedures were written by non-sql developers, without any consideration for performance...
Modify - that is interesting one! One of the reason ORM is used for system/application development is to avoid additional resources (eg. sql-devloper) required for making changes.
and the last bit:
and in most cases because stored procedures are compiled code there could be performance benefits..
Ad-hoc queries also can be compiled and cached...
So, all of the above alone cannot be reason for using stored procedures.
Nowadays, it's really depends on the chosen system/application architecture.
March 11, 2013 at 6:08 am
fmuzul (3/11/2013)
My suggestion is: avoid stored procedures at all, unless you have very special needs of performance on some complicated operations....
Calling statement! :w00t:
I'm afraid you will not find many SQL specialists agreeing with the above...
Actually first few paragraphs from BOL tell well enough:
Stored procedures assist in achieving a consistent implementation of logic across applications. The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure. Each application needing to perform that task can then simply execute the stored procedure. Coding business logic into a single stored procedure also offers a single point of control for ensuring that business rules are correctly enforced.
Stored procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server. The results do not have to be returned to the client to have the conditional logic applied; all of the work is done on the server...
March 11, 2013 at 7:00 am
In addition, if the underlying table structure changes but the output of the stored procedures remains the same, you isolate the changes to the database. You modify the stored procedures to use the new table structures ensuring that the input and output of the procedures remains the same, the application does not need to change.
March 11, 2013 at 7:20 am
Gazareth (3/11/2013)
Well, I think you've got both ends of the argument there in the last couple of posts!But what's with the paranoia over DBA's Francesco? We're not all bad 🙂
I don't think I'm paranoic over DBA's.
I just think that sometime they overestimate the benefits of using s.p., and underestimate the problems they can give.
March 11, 2013 at 7:29 am
fmuzul (3/11/2013)
Gazareth (3/11/2013)
Well, I think you've got both ends of the argument there in the last couple of posts!But what's with the paranoia over DBA's Francesco? We're not all bad 🙂
I don't think I'm paranoic over DBA's.
I just think that sometime they overestimate the benefits of using s.p., and underestimate the problems they can give.
The biggest problem with having the SQL code embedded in the application code comes to tuning the code. Any rewrites of the embedded code require a redeployment of the application. If the code is contained inside of a stored procedure, only the stored procedure needs to be redeployed, not the entire application.
March 11, 2013 at 7:32 am
Eugene Elutin (3/11/2013)
fmuzul (3/11/2013)
My suggestion is: avoid stored procedures at all, unless you have very special needs of performance on some complicated operations....
Calling statement! :w00t:
I'm afraid you will not find many SQL specialists agreeing with the above...
Actually first few paragraphs from BOL tell well enough:
Stored procedures assist in achieving a consistent implementation of logic across applications. The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure. Each application needing to perform that task can then simply execute the stored procedure. Coding business logic into a single stored procedure also offers a single point of control for ensuring that business rules are correctly enforced.
Stored procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server. The results do not have to be returned to the client to have the conditional logic applied; all of the work is done on the server...
BOL is written by Microsoft. This is not an independent point of view.
They want to convince the reader that s.p. are good.
The statement "The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure." can be rewritten replacing "stored procedure" with "C++ procedure", or "C# procedure", or "PHP procedure" or whatever language else you use for developing the application.
And about performances, yes, I recognize that sometime you can get gains using s.p., but the most of the time we are speaking about milliseconds, so this doesn't really matter.
March 11, 2013 at 7:41 am
I think this is more a discussion with the stand from the world you belong to.
SQL DBA vs native code writers.
Well i belong to both worlds.
I used to write VB.Net and also perform SQL DBA/developer tasks.
For me the choice wass easy.
I use the world that is the most convinient.
Most cases this was SQL Side SP.
But even when i used ad-hoc sql code in my applications, i found myself writing the statements in sql mgt studio and test them on execution performance (also Redgate sqlprompt was great help writing here;-) )
And so i have to agree with Lynn, i have found myself different times on the case that bussines logic changes so did the table structure but output in application would stay the same.
With an SP this is an easy tasks, no so in ad-hoc sql code.
With kind regards,
Van Heghe Eddy
March 11, 2013 at 7:42 am
Lynn Pettis (3/11/2013)
In addition, if the underlying table structure changes but the output of the stored procedures remains the same, you isolate the changes to the database. You modify the stored procedures to use the new table structures ensuring that the input and output of the procedures remains the same, the application does not need to change.
Replace "stored procedure" with "C++ procedure" and swap "application" with "database" and you get a perfectly valid statement again.
If you work with s.p., you isolate the changes to the database. And this can be good if you don't want to change the application.
If you work with the application language, you isolate the changes to the application. And this can be good if you don't want to change the database logic (or even don't want to have logic at all in the database).
DBAs obviously tend to prefer s.p. because they are accustomed to them.
Developers should prefer to avoid s.p.
March 11, 2013 at 7:50 am
fmuzul (3/11/2013)
Lynn Pettis (3/11/2013)
In addition, if the underlying table structure changes but the output of the stored procedures remains the same, you isolate the changes to the database. You modify the stored procedures to use the new table structures ensuring that the input and output of the procedures remains the same, the application does not need to change.Replace "stored procedure" with "C++ procedure" and swap "application" with "database" and you get a perfectly valid statement again.
If you work with s.p., you isolate the changes to the database. And this can be good if you don't want to change the application.
If you work with the application language, you isolate the changes to the application. And this can be good if you don't want to change the database logic (or even don't want to have logic at all in the database).
DBAs obviously tend to prefer s.p. because they are accustomed to them.
Developers should prefer to avoid s.p.
You are operating under a false perception of me. I am not a DBA. I am a DBA/Database Developer/Developer. I have worked on all sides of the application. My perception comes from that experience.
If your application needs to support multiple database backends, then develop those database backends separately. Support the multiple code bases needed. Make use of the capabilities of each of those DB Engines to get the most from them to improve the performance of the application using those DB Engines.
There is a fallacy of truly portable code. I don't write Standard SQL code, I write code that makes the best use of the underlying DB engine. This provides a performant system upon which an application can be built.
March 11, 2013 at 7:52 am
Lynn Pettis (3/11/2013)
fmuzul (3/11/2013)
Gazareth (3/11/2013)
Well, I think you've got both ends of the argument there in the last couple of posts!But what's with the paranoia over DBA's Francesco? We're not all bad 🙂
I don't think I'm paranoic over DBA's.
I just think that sometime they overestimate the benefits of using s.p., and underestimate the problems they can give.
The biggest problem with having the SQL code embedded in the application code comes to tuning the code. Any rewrites of the embedded code require a redeployment of the application. If the code is contained inside of a stored procedure, only the stored procedure needs to be redeployed, not the entire application.
Maybe you live in a different world than mine. For me the redeploy of an application is not a problem, I just need to replace a .exe file, and this can happen automatically also (as many modern applications do, checking for an update somewhere into the net). Updating the code of a stored procedure could be more uncomfortable.
And using a source version control I can know exactly who and when made a change to the application code, I can get a complete history of the changes.
I know that version control can be applied to s.p. also, but I think this is more difficult.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply