Solved

View vs stored proceedure

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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 …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now