Merits of Using Stored Procedure over Ad-Hoc queries

Stored procedure is a set of Structured Query Language statements with a user defined name. Stored procedures are physically stored within the database in precompiled form for ready to use purpose allowing applications to use it by any number of times & by any number of programs.

Ad hoc queries are the open queries which are written at application side or in SSSMS generally for requirement specific purpose.

Problems of Ad-hoc Queries:

As mentioned in description ad-hoc queries are open queries which are generally used for some specific requirement. The major demerit of using ad-hoc queries are,

Ad-hoc queries are not stored in Sql server causing no pre-stored query plan due to which whenever the ad-hoc is run the first thing Sql engine does is that it generates the executions plan every time where in case of Stored procedures a precompiled plan is ready used. Each time plan generation activity causes the execution duration & delay in result presentation.

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. Let’s see some important information about the same,

·         Reduce Network Traffic: Excessive network traffic is a big performance killer in production databases. In a case where users are frequently pushing request to database server through some client application (because of ad hoc queries) this may cause a excessive network traffic because the complete ad-hoc request will hit the server via network & depending on the size of query & data size the network will be used in same proportion. In this situation Store Procedures will help in reducing such network traffic by holding group of statements and returning required result with a single call.

Stored procedures are stored in database system & can be bring into use via calling by name & appropriate procedures.

Note:  Lengthy transactions in store procedures should be avoided to prevent lock problems. In real world this have been observed that while using transactions into procedures most of the developers put the complete script into transactions (some selects from huge tables etc.) which causes the other objects to be busy during the complete execution of stored procedure.

·         Privilege based execution

Stored procedures can be grant to be executed as per the user 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.

·         Re-use of Execution Plan

As mentioned in fist point 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, In case of financial application the logic for printing the amount on various documents can be used on various instances just by calling the concerned stored procedure by providing the proper parameter.

·         Ease of Maintenance

     In real world change in business rules defined for a project, over a time is normal. If such business rules are controlled with in store procedures rather than ad-hoc then it is very for the maintenance. In this case developer need to make changes in every piece of code & recompile.