Stored procedure is a set of Structured Query Language statements with an assigned name which are stored with in the database in compiled form so that it can be used by a number of programs.
Ad hoc queries are normally written on application side and are meant to be used for once only and are never saved to run again.
At the beginning developers who are not good at database side, like to use ad hoc queries for fetching and to make changes in required data. These ad hoc queries can kill performance and some time it is hard to control complex logics through these ad hoc queries. Store procedures are the best choice to accomplish these data processes. These are helpful in following regards.
- Reduce Network Traffic
Excessive network traffic is a big performance killer. Frequent trips to database server from client application (because of ad hoc queries) may be a cause of this excessive network traffic. Store Procedures helps you to reduce such network traffic by holding group of statements and returning required result with a single call.
Avoid lengthy transactions in store procedures to prevent lock contention problems.
- Database Privileges
Users can be restricted from having access to read/write to tables directly in database by using store procedures. Only developer of store procedure require specific privileges, while creating a store procedure but to execute these store procedures client of application only need execute privileges.
- Code Security
Sql Injections, which uses AND or Or to append commands on to a valid input parameter can be defended by using store procedures, but If you still have a string in your application with the store procedure name and concatenated parameters from user input to that string in your code, you are still on risk.
- Execution Plan Re-use
Store procedures are compiled once and resultant execution plan are utilized for future executions. This results in tremendous performance boosts when store procedures are called repeatedly.
- Efficient Re-use of Code
Commonly used store procedures can be effectively used for different projects.
For example, create a store procedure which returns amount in words against integer input. (INPUT= 1542214, OUTPUT= 1.5 Million, Forty Two Thousand, Two Hundred and Fourteen). Store procedure like this, can be used in any application.
- Single Point of Maintenance
Change in business rules defined for a project, over a time is normal. If such business rules are controlled with in store procedures rather then application, it is easy to make changes in database and NO need to recompile your application code.
This sounds like advice from a DBA -- not an architect.
ReplyDeleteDatabases are for storage. Period.
Applications are for application logic.
If you have a need to reuse this logic, that's what service-oriented architectures are for. This also takes care of the "single-point-of-maintenance" argument.
With respect to code security, every decent data access technology helps prevent sql injection. If your developers don't know this, then you need to hire more senior people to review their code and help them learn.
With respect to execution plan re-use... most enterprise databases offer an execution plan cache. So to say that this is a reason to use stored procedures over ad-hoc queries is slightly disengenuous.
With respect to reducing network traffic... yes, this should be a requirement for all system development efforts. There are numerous ways to achieve this within the application: object caching, writing more efficient queries, etc.
But moving the application logic into the database is rarely the correct answer.
First of all, database access is typically the number one bottleneck for application performance. Placing a larger burden on the database server by forcing it to handle ever-more complex business logic is not going to help.
If the application logic happens to be slow, throw another server into the farm. If all that logic is in the database, this is neither cheap, nor easy, and often-times not even possible.
Second, stored procedures are not unit-testable.
Third, database code rarely follows the same lifecycle as the application that relies on it. Have you ever seen stored procedures in a source control system? This is a rare occurrence. When it does happen, the version that is in source control rarely follows the same tag and build lifecycle as the application; and more often than not, the stored proc in production never even makes it into the code repository.
Fourth, if you have ever been in the situation where IT management decides that Vendor A is no longer providing good value, and we are switching to Vendor B for our database; you know how not-fun it can be to tell them, "well, it's going to take at least a year to migrate all our stored procs to the new syntax". If you keep your application logic out of the database, replacement is a much simpler task.
Fifth, organizations typically hire far fewer DBA's than application developers. The more application logic that must be maintained by the DBA, the greater a hinderence to the development lifecycle they become.
Sixth, there is little-to-no traceability in terms of which applications are using which stored procedures. This will quickly lead to rigor-mortis. DBA's will be afraid to make small changes to stored procedures for fear of what it might break upstream. Next thing you know, you have six stored procedures that all do substantially similar things, and nobody knows which, if any of them, are even still being used.
Lastly, by placing the logic in the stored procedure, you take away any possibility for the application developer to improve the algorithms at the application layer. Performance becomes a fixed cost, and slow processes will remain slow until new database hardware is introduced or a new platform is purchased -- which isn't going to happen, because nobody wants to upgrade all those stored procs.
But I think,using Ad hoc queries instead of Stored Procedures is a bad choice
ReplyDeleteoh snap!
ReplyDeleteAgree to Jeff. In the present paradigm of software developments, business wants applications to be robust, flexible, distributed and platform independent. By pushing logics to database you loose on all of above aspects.
ReplyDeleteMajor risks involved in using stored procs
1. Logic can not be unit-tested.
2. Tendency to be database dependent logic (e.g use of database specific functions).
3. At enterprise level, it may enforce database vendors to be business partners (to get service level contracts and supports).
4. Logic ownership goes with DBAs and DB experts, rather than application developers and analysts.
Any decent ORM (object relational mapper) will handle all this easily and the code will always be a lot easier to maintain then to have 100+ sp's in your application. As a application developer I do not want to think about the database behind my application. And the same goes for a DBA, he or she should not have to know that much about the application(s) using the database.
ReplyDeleteIf the DBA spend his time optimizing the actual data storage instead of writing sp's and the application developer spend his time working on the domain you will spare a lot of time. You will both end up writing less code and everything will be testable.
When there is a very complex query that is hard to write using a orm, you can easily write a sp and execute it using your orm. But I would never write simple CRUD queries in sp's or any kind of business logic for that mather, this belongs in the application.
To me, maintaining sp's is a nightmare, while maintaining data access code written using a ORM is a lot easier.
I've worked with several fortune 500 companies who have huge enterprise apps that have 100's of stored procedures in their data access layer. I haven't experienced this allegid nightmare of maintaining SP's. Not arging pro or con here about which strategy is better, just that maintaining SP's has never been a real problem that I have observed. If is a scarecrow argument against SP's. And if you are having problems, as the first poster said, hire some more experienced developers with source control and DBA experience to rewrite yoru code and set into place ways of doing things so this becomes a non-issue.
ReplyDeleteAnon - Either they are not changing them or you are not seeing the problems. Using SP force you to mix data access and business logic. And SP languages have syntax that make it difficult to see the forest for the trees (i.e. the syntax gets in the way).
ReplyDeleteIt is not about "having problems". It is about facing reality.
Try this in your SP. "Find all references to...".
Another issue that the blogger claims is that using SPs reduces network traffic. If you put all the busines logic in the db, that means you HAVE to call the db for all business logic. That more than likely means more network traffic. And what if you have to accept changes before they even come near the database? Most large and highly active web sites have this issue.
SQL is not an ideal language with which to write validation or business logic code. I've seen too many 2,000-line sprocs in my time.
ReplyDeleteWhere I work, the application developers do write their own stored procedures, are highly skilled in T-SQL and writing/optimizing SQL/Stored procedures. Furthermore, they do unit test their procedures, and they do maintain the stored procedures in source control.
ReplyDeleteA good application developer will unit test the application code and stored procedures together to come up with the best code for both.
The application developers where I work are exceptional, and not only do they unit test their procedures and application code, we also have multiple test beds for functional testing, performance testing, and for integration testing.
So if you are doing a thorough and complete job as an application developer, I fail to see why a developer would favor ad hoc queries over stored procedures.
I agree that business logic should not be encapsulated inside of stored procedures. But that doesn't mean that you need to use ad hoc queries. You can perform business logic at the application layer and still use procedures for data access.
Robert Davis is quite right.
ReplyDeleteYou are all misunderstanding that all code must be placed in the stored procedures.
Business logic should not be encapsulated inside of stored procedures, but certain critical operations involving direct queries against the databases would be a lot faster using stored procedures instead of ad hoc queries.
You are forgetting the main purpose of the Stored Procedures: speed up the system.
All the other approaches like SOA, Object-oriented programming are focused on code re-use and maintenance.
As former developer and actual DBA, I know how much these type of approaches impact the systems. It's a choice between performance x maintenance.
Visual Studio and SQL Server management Studio are two different entities. So it takes a lot of time when we try an interaction with the database from Visual Studio then it takes a lot of time.
ReplyDeleteWhen we are using desktop applications then we do not realize this time delay, but while using web applications the time delay becomes an important factor because the database is on the remote server so a huge time delay is observed.
Normally, the query that we write in Visual Studio is first compiled and then executed in Sql Server which then returns the results. So, this is why a time delay is observed.
The answer to this is Stored Procedure usage. The Stored Procedure is stored in compiled format in SQL server so when we call it from Visual Studio application then it is just executed. Thus saving a lot of time.
An illustration of proper usage of STORED PROCEDURE has been displayed in the following video. You may see it:
http://www.visiontechno.net/studymats/storeprocedure.html
How to run stored procedure from C# applications
ReplyDeletehttp://net-informations.com/csprj/data-providers/cs-stored-procedures.htm
lee.