Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

View vs stored proceedure

Posted on 2004-09-04
2
Medium Priority
?
199 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

719 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