Solved

View vs stored proceedure

Posted on 2004-09-04
2
191 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 125 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server maintenance plan 8 53
The AZure backup problem 11 51
how to make geography query faster?  SQL 7 39
Split string into 3 separate fields 5 18
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

679 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