?
Solved

View vs stored proceedure

Posted on 2004-09-04
2
Medium Priority
?
204 Views
Last Modified: 2010-05-18
Quick question.
Is a stored proceedure better to use than a view?
I have a requirement to do a couple of mathematical calculations on a few fields in my table.
I can use a view for this, but is this going to be slower than a stored proc?
Thanks
0
Comment
Question by:maunded
2 Comments
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 500 total points
ID: 11982608
The best approach depends primarily on how often the calculations will be needed, and whether it's the same calculation each time or if there's a variable or function involved.

If there's no variable or function and if there are a lot of rows returned or the calculations are performed frequently, it may be best to do the calculation once for each row and store the result in a permanent table. Then, the total or whatever only has to be fetched instead of having the same thing calculated over and over.

Otherwise, stored procedures offer greater flexibility than views, but if a stored procedure only does what a view can also do, the performance should be about the same (all other things being equal). Either way, of course, performance can vary depending on what else the server's doing over a period of time.

You can also experiment with it to confirm this. Create the view, then copy and paste the SELECT statement and put it in a stored procedure, then run each and time them.
0
 
LVL 1

Author Comment

by:maunded
ID: 11983142
Thats great, you have answered my question.
Basically I wanted to know if there was any performance difference between views and stored procs, since there isnt much if any), I can go with a view.
Thanks for your help.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question