Solved

View vs stored proceedure

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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

911 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

20 Experts available now in Live!

Get 1:1 Help Now