I have to write a query with lot of computations. Is it a good idea to create indexed view with this computed columns instead of writing a stored proc? Thanks!!
+3
A:
It depends!
If you create an indexed view you'll be trading increased costs in terms of greaer storage space requirements and slower inserts, updates & deletes for increased speed of accessing these computed values. If you only want to use these values once or occassionally, you might be better off computing them on demand in a SP but, like I said, it depends!
There are other factors to consider to, including: over how many records do these computations need to execued? If it's just a few, the indexed view approach may not be appropriate because it may affect all rows unless you limit it with sutable WHERE/HAVING clauses - remember that an indexed view isn't parameterised.
Daniel Renshaw
2010-08-19 15:41:55
Will partioning table help for index view?
2010-08-19 15:46:25
Partitioning a *table* and indexing a *view* are two very different things. Are you asking whether partioning the table would make it easier to implement the view or make the view perform faster or have I misunderstood?
Daniel Renshaw
2010-08-19 15:49:02
Write SP to accept parameters and make a select statement in SP for Indexed view with where calues. I am really getting confused which one would be better. With partition indexes are not recreated.
2010-08-19 15:50:53
Parttioning and view (Summary: This document describes the indexed views capability of SQL Server 2005 and SQL Server 2008, including the new support for partition-aligned views added to SQL Server 2008. ) http://msdn.microsoft.com/en-us/library/dd171921(SQL.100).aspx this is the link I am reading and getting confused.
2010-08-19 15:52:58
Are you sure that article accurately reflects your problem? Your original queston didn't mention partitioning so I would advise aviding everyting to do with partitoning (either tables or views) unless you're sure you need to use that feature. You don't have to have a partitioned table or have a partitioned view in order to index the view.
Daniel Renshaw
2010-08-19 16:08:36
What I have to do is table have 100thousands of records I want to show data for particular USER for his different items ( 100s) with different calculations like his budgeted amount per week, per month, per year, against actual spending for each of his item and etc. he can set time frequency ( by day, by week, by month) on each item. I was planning to have By month as default value for each item and show computed values based on this defalut value for each item ( he may request to view all his items by any time frequncy).
2010-08-19 16:16:07
How frequently will these values be looked up? If it's not especially frequently, sounds like a job for a paramterised SP to me.
Daniel Renshaw
2010-08-19 16:25:38
Frequency to look up – very difficult for me to tell. There are 1000s of user on the system. On his budget page he can view is actual budget by any frequency any time he wants. I don’t know how often user would do this? Depends on how closely each user manages his finances. That’s why I am getting confused if user is coming to this page quite often and looking at his budget, is going to be big issue on database side?
2010-08-19 16:32:27
In that case it might be a good idea to do this as an SP and then implement some form of caching at a higher level (i.e. in the calling application).
Daniel Renshaw
2010-08-19 16:36:03
User can set his individual budget item by any frequency so calculating budget amount of his all items to selected frequency and then calculating his actual spending for selected frequency should be okay to do in stroed proc?
2010-08-19 16:44:17
I'm afriad that question is rather too specifc to the task you're trying to do and your application. I don't have nearly enough informaton to answer it with any authority. However, the SQL that calculates these values would be roughly the same whether it's in an SP or in an indexed view. I suggest implementing it as an SP initially and then move it into a view if performance testing suggests the SP is not up to the job.
Daniel Renshaw
2010-08-19 16:50:09
As you suggested I would go with SP first and if there are any performance issues with lot of these calcutions then I will try indexed view.Thanks
2010-08-19 16:55:07
A:
I would only suggest an indexed view when you are dealing with a very high amount of records found in places like Datawarehouse or Datamarts. Here you will be doing much more reading of the data then writing to it so the index will help you more than hurt you.
I have100 - thousands of records in a table but I want to show data for particular USER for his different items ( 100s) with different calculations like his budgeted amount per week, per month, per year, against actual spending and etc. Is it better to write a stored proc?
2010-08-19 16:00:18
Based on my understanding of what your doing I would put it into a SP. Then if your having trouble with speed I verify you have proper indexing on your filtered columns and joins. If your getting lookups because of the calculated fields you can always just include them in the indexes include to make it covering.
2010-08-19 17:02:30