I’ve seen a lot of talk recently that is against the use of stored procedures when designing and developing database & data driven applications. There seems to be a real desire to purpetuate the idea that use of stored procedures is a bad idea. While in some instances this is almost certainly the case, it is not a hard and fast rule.
The Idea of “Best Practices”
Almost nothing in development is a hard and fast rule. And “best practices” are not always best. Everything is dependent on the exact situation you are in. Sometimes it can be your client who sets the ground rules. And while most clients are reasonable, sometimes they come from a time when things were in fact, done a certain way and they don’t really have a desire to deviate. Some clients will hear you out and make a shift to a different method if proven that the new method will work better. Sometimes, the situation just calls for things to be done a certain way.
Stored procedures to make database calls can fit into all of the categories mentioned above. Stored procedures are not fun to write or deal with. If you have to write one, then you must understand what is and is not the best way to to things. You must understand things like database performance, T-SQL “best practices”, and even the inner-workings of your DBMS (Database Management System). If you are not the one writing the stored procedure, you are at the mercy of your database administrator. If your company/client has a good DBA, then you have nothing to worry about. However, if they have someone who does not know what they are doing, stored procedures can be a hinderence.
Where Stored Procedures Fit
When it comes to deciding when to use stored procedures, there are some things to consider. FIrst and foremost is what language are you using to code your application. I can speak from experience when I say, if you are not using C/C++, using stored procedures is going to make your life far easier than not using them. This is true for most low level languages.
Another place that you might want to use a stored procedure is on the reporting side of things. When it comes to report, the way that report looks will change drastically over the course of a few days. Which means you have to shift your SQL code around when things change. Sometimes storing the database logic in a stored procedure can be best. This truely serparates the data logic from the application logic.
Where Stored Procedures Don’t Work
If you’re using something like .Net or Java to develop your application, there is little need for stored procedures (but there can be cases where you will use them). The reason for this is because .Net and Java make it very easy to separate business logic from interface logic. Also, there are tools that can help you separate things further if you need to.
Another place where stored procedures don’t work, is when you have a database administrator who does not care too much about database performance and thus does not write stored procedures with performance in mind. If you have a database administrator like this, don’t bother, try to get your company/client to allow you to put the code in your application code base. You can do this by proving that your code returns results faster than the provided stored procedure.
Something to Chew On
Keep in mind that there is rarely one correct way to do things. Using stored procedures has it’s place just as not using them has their place. Nothing is set in stone and in some cases using a stored procedure makes perfect sense. The Alt.NET movement is all about find the best way to do things and nothing should be thrown out just because it’s not a “practice” that you find acceptable.
Like this:
Like Loading...
Related
Michael Koby 
The Case for Stored Procedures
I’ve seen a lot of talk recently that is against the use of stored procedures when designing and developing database & data driven applications. There seems to be a real desire to purpetuate the idea that use of stored procedures is a bad idea. While in some instances this is almost certainly the case, it is not a hard and fast rule.
The Idea of “Best Practices”
Almost nothing in development is a hard and fast rule. And “best practices” are not always best. Everything is dependent on the exact situation you are in. Sometimes it can be your client who sets the ground rules. And while most clients are reasonable, sometimes they come from a time when things were in fact, done a certain way and they don’t really have a desire to deviate. Some clients will hear you out and make a shift to a different method if proven that the new method will work better. Sometimes, the situation just calls for things to be done a certain way.
Stored procedures to make database calls can fit into all of the categories mentioned above. Stored procedures are not fun to write or deal with. If you have to write one, then you must understand what is and is not the best way to to things. You must understand things like database performance, T-SQL “best practices”, and even the inner-workings of your DBMS (Database Management System). If you are not the one writing the stored procedure, you are at the mercy of your database administrator. If your company/client has a good DBA, then you have nothing to worry about. However, if they have someone who does not know what they are doing, stored procedures can be a hinderence.
Where Stored Procedures Fit
When it comes to deciding when to use stored procedures, there are some things to consider. FIrst and foremost is what language are you using to code your application. I can speak from experience when I say, if you are not using C/C++, using stored procedures is going to make your life far easier than not using them. This is true for most low level languages.
Another place that you might want to use a stored procedure is on the reporting side of things. When it comes to report, the way that report looks will change drastically over the course of a few days. Which means you have to shift your SQL code around when things change. Sometimes storing the database logic in a stored procedure can be best. This truely serparates the data logic from the application logic.
Where Stored Procedures Don’t Work
If you’re using something like .Net or Java to develop your application, there is little need for stored procedures (but there can be cases where you will use them). The reason for this is because .Net and Java make it very easy to separate business logic from interface logic. Also, there are tools that can help you separate things further if you need to.
Another place where stored procedures don’t work, is when you have a database administrator who does not care too much about database performance and thus does not write stored procedures with performance in mind. If you have a database administrator like this, don’t bother, try to get your company/client to allow you to put the code in your application code base. You can do this by proving that your code returns results faster than the provided stored procedure.
Something to Chew On
Keep in mind that there is rarely one correct way to do things. Using stored procedures has it’s place just as not using them has their place. Nothing is set in stone and in some cases using a stored procedure makes perfect sense. The Alt.NET movement is all about find the best way to do things and nothing should be thrown out just because it’s not a “practice” that you find acceptable.
Share this:
Like this:
Related