| Author |
Topic |
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
 Posted - 2008-03-04 : 18:47:06
|
I can't seem to find an adequate answer, so I am hoping someone here can shed some light on it..I have created an indexed view which joins 2 tablesThe two source tables:customer 1.8 MM records, 2.7 GB space used, 519 MB indexes/ 2.2 datahistorical 78 MM records, 44 GB space used, 9 GB indexes/35GB datathe indexed view shows space used of 78 MM records (as expected) but a size of over 50 GB space used.The unique clustered index on the view contains 4 columns and byte length of 15 ( int, char(3), char(7), char(4))..Did I do something incorrect? or is it normal for the indexed view to use that much space? Does this store the results of the view entirely as a table?Is it normal for the size to be that large?I should note that the performance gains from the indexed view are significant as predicted...typical query results return with 80-90% improvement in time...despite nearly identical query plans.
Poor planning on your part does not constitute an emergency on my part. |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-04 : 20:09:47
|
Hmmm...maybe I should be more specific?Is it normal for the indexed view to be the combined size of both source tables?
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-04 : 23:09:14
|
| Did you check index size of the view? |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-05 : 07:39:18
|
total size used for the is 50GB,like I noted above. Index size is 175,505 KB. I am only seeking to find out if this is what should happen on an indexed view...that is a lot of memory to chew up, effectively storing the data twice. It seems that it is storing the entire results of the view as it's own entire table...not just the index.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-03-05 : 08:47:44
|
| It would help if you show us your DDL for the tables involved, the indexes, and your view definition. Unless it is an aggregate view or it has lots of calculated columns, typically indexed views are not worth it when the underlying tables are properly indexed and the SQL is clean and efficient.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-05 : 16:01:33
|
quote:
Originally posted by jsmith8858
It would help if you show us your DDL for the tables involved, the indexes, and your view definition. Unless it is an aggregate view or it has lots of calculated columns, typically indexed views are not worth it when the underlying tables are properly indexed and the SQL is clean and efficient.- Jeffhttp://weblogs.sqlteam.com/JeffS
From what I read, my scenario as a data-mining source, the indexing of the view can help speed up performance, at the expense of some physical space. Data is only updated to these tables once per month.Query return time was dramatically different which is lending some consideration to using the indexed view.I am not asking so much if the indexed view is worth it or not, more if it is "normal" for it take up so much space considering the size of the source tables, or if I am doing something wrong.I will post back with the DDL for the two tables, with the caveat that in some instances there is intentional denormalization (such as Balance may occur in both tables)There are no computed columns, or aggregates in the view. The row count of the view is identical to the row count of the historical table (as expected)Prior to exploring use of an indexed view: Historical Table (joined inside of)---------- multiple Primary Views UsedCustomer table My intent was to change that to:Historical Table (joined as source for) -------------Indexed ViewCustomer TableIndexed View *source for------------multiple Primary Views used Performance improvement was between 60 and 80% time difference (better with the indexed view).I will post back shortly with the requested DDL.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-05 : 16:22:18
|
| <removed the DDL> |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-03-05 : 19:10:54
|
| I would focus on creating useful indexes that aren't just trying to cover all columns in your source tables and do not even think about indexed views. It doesn't appear that the indexes you have shown will be very useful unless you always 100% of the time query the tables the exact same way, which I doubt. For example, if you filter your tables on "ProcessDate" or "Customer" or "LoanNumber", none of your indexes are of any use at all. In fact, they are pretty useless because they simply cover pretty much what the PK already handles. If you are trying to create large "covering" indexes, again, like indexed views, that is premature optimization until you first create a set of small, standard indexes and are positive that you cannot simply use them to optimize your performance.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-05 : 19:52:25
|
Gotcha... I have just rebuilt these data sets in an attempt to fix a denormalized de-saster before my arrival. it has been a long process to not "break" the existing reporting done from them. I now have the columns in the best order for how they are queried and trying to get a better handle on optimizing using indexes.I will lose the indexed view and those multi column indexes. those larger ones were fairly recent additions, and I hadn't really evaluated the performance. I have had a hard time muddling through the tuning wizard, so it has been a lot of trial and (mostly) error...LOLThanks for the insight. Was more of an experiment into the concept of indexed views...especially given the size.My original question remains unanswered however...is it normal for an indexed view (like that) to be the aggregate size of the source tables?
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
|
Indexed view size exceeds source tables size?
- Jeff