Why stored procedures

Why Consider Stored Procedures?
1. The first time the stored procedure is run, it gets compiled. This produces an execution plan—essentially a record of the steps that Microsoft® SQL Server™ must take to get the results specified by the T-SQL in the stored procedure. The execution plan is then cached in memory for future use. This improves the performance of the stored procedure in that SQL Server does not need to analyze the code again to figure out what to do with it; it can simply refer to the cached plan. This cached plan is available until SQL Server is re-started, or until it is aged out of memory due to low usage.
2. Performance : The cached execution plan used to give stored procedures a performance advantage over queries. However, for the last couple of versions of SQL Server, execution plans are cached for all T-SQL batches, regardless of whether or not they are in a stored procedure. Therefore, performance based on this feature is no longer a selling point for stored procedures.
3. Stored procedures can still provide performance benefits where they can be used to reduce network traffic. You only have to send the EXECUTE stored_proc_name statement over the wire instead of a whole T-SQL routine
4. Maintainability and Abstraction: In a perfect world, your database schema would never change and your business rules would never get modified, but in the real world these things happen
5. Also, by abstracting the implementation and keeping this code in a stored procedure, any application that needs access to the data can get it in a uniform manner. You don't have to maintain the same code in multiple places, and your users get consistent information.

References:
http://msdn.microsoft.com/en-us/library/ms973918.aspx
http://sqlserverpedia.com/wiki/Query_Processing_-_Caching_Execution_Plans

Comments

Post a Comment

Popular posts from this blog

Understanding Top line and Bottom line?

Understanding Top line and Bottom line? Top line – It’s the revenues generated by a company or what you might call the gross sales. When people talk about the company’s “topline growth”, they refer to an increase in its gross sales or revenues. When companies speak about the need to increase their topline, they are discussing about the need to focus on generating/increasing sales. This is money that is coming in before any expenses are deducted. Bottom line – The bottom line is the income that is left after all expenses have been deducted. You might also call this the company’s net profit. Some of the expenses that are usually being deducted are things like loan repayments, loan interests, administrative costs, taxes and even salary to employees. So there could be a big difference between the Top line revenues and the Bottom line revenues. A company’s bottomline can also be referred to as net earnings. Bottomline usually shows the efficiency of the company in controlling ...
Read more

How to activate PF UAN account

Image
1. Go here... http://uanmembers.epfoservices.in/ 2. Select "activate your UAN" 3. Enter your UAN, Mobile no., select Delhi - South as state (or your PF office), Then enter your  PF number (middle box will be empty). as shown below in picture 4. Get PIN on your mobile number 5. Enter PIN and submit 5. Enter your email address and create password Thanks, Jakir
Read more

Scrum Master vs Product Owner

The most common confusion in the 2 roles of Agile-SCRUM is between Scrum Master & Product Owner. The Scrum Master is in charge of making sure the Scrum process is followed. The Product Owner (PO) is the person in charge of making sure the product succeeds in the direction the company wants to go. The PO does this by setting priorities of product backlog items, making sure all needed parties are there for both the sprint planning and sprint review (both are very important). If something occurs and either the scope explodes or decisions need to be made, the PO is the person who gives the answers to the decisions. Product owner act as the feature prioritize(r) and main point of contact between business/customer and IT team. If product not succeed and probably reason is prioritization issue then product owner may be "single wringable neck" person. The Scrum Master is making sure the process gets followed and working through the adaptation of Scrum to your organization. By do...
Read more