| Author |
Topic |
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
 Posted - 2002-06-14 : 10:00:59
|
| I am wondering if anyone is using these in production and I would like to hear about your experience.It seems to me that Indexed Views could really provide a lot of the aggragation that is requested of a warehouse, but from a relational schema. Additionally, it sorta makes unlimited the number of clusted indexes you can have on a given table. It's my understanding that the optimizer may choose to use the clustered index on a view even if that view is not referenced in the dml! That seems unbelievabley powerful.So somebody show off a bit, and wow us with your clever use of indexed views.<O> |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-06-14 : 10:16:10
|
| I haven't used them yet, to be honest I hadn't really seen a point before. But your datawarehousing scenario makes perfect sense.Damian |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-14 : 10:20:41
|
| A little light reading from bill.For the first time, I am kinda seeing a million uses for these things.Although, all the rules around creating a view that is eligable for indexing (deterministiec, SET options, etc) are a bit cumbersome . . .<O> |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-06-14 : 10:26:15
|
I don't use them (haven't seen any need for them just yet), but I'm gonna add my 2c in anyway. quote:
Additionally, it sorta makes unlimited the number of clusted indexes you can have on a given table.
So potentially you'll get stellar performance on your data retrieval operations. But think of the overhead it would put on any data modification operation. Every time you update/insert/delete something from a table, SQL Server will need to update your materialized views... Seems to me that you'll soon come to regret having "unlimited number of clustered indexes". |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-14 : 10:36:45
|
quote:
So potentially you'll get stellar performance on your data retrieval operations. But think of the overhead it would put on any data modification operation. Every time you update/insert/delete something from a table, SQL Server will need to update your materialized views... Seems to me that you'll soon come to regret having "unlimited number of clustered indexes".
The same can be said for any index right? Granted, there is generally trade-off between reporting performance and transactional performance. So an indexed view is right NOT RIGHT for many OLTP objects. However, almost all OLTP apps require a certain amount of reporting, and generally that can be very costly. Without indexed views, you can only index tables used for a complicated aggregation so much. But if you could just index your desired friggin rowset ..... for get about!!!<O>Edited by - Page47 on 06/14/2002 10:55:41 |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-06-14 : 10:44:06
|
| I'll agree with Izaltsman on this one.. I had an indexed view created on one table that had about 50000 rows added or removed daily. Without the index, inserts on the table were quick. With the index on the view it took almost double the time to make these inserts.If your going to index a view, make sure the tables that it refers to is not altered alot.-----------------------Take my advice, I dare ya |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-06-14 : 11:28:56
|
quote:
The same can be said for any index right?
Not quite. Since clustered indexes imply that data is physically sorted, data modifications on the clustered index are potentially more costly then modifications on a non-clustered index (which implies only logical sorting of the data). The more materialized views you have (each with its own clustered index) the greater the chance becomes of a data modification operation affecting the clustering key in one or more views and underlying tables). I predict massive page splits. And if you decide to calculate some aggregates at the same time, you need to remember -- they will be calulated on EVERY DAMN INSERT/DELETE/UPDATE... That would shurely bring the server to its knees.BTW, I don't buy into the idea that indexed views will allow you to keep your DB normalized, and keep aggregate data as well. True, the table schema will be normalized. But since indexed views are materialized (physically stored in the db), you ARE storing redundant data. Granted, SQL Server will ensure the integrity of the redundant data elements, and you will not have to write code to deal with it like you would if you were dealing with a non-normalized table schema... But from the academic perspective you can't call your database normalized. And that's not the important part (personally, I am a big fan of denormalizing where appropriate). What is important, is that regardless of whether SQL Server deals with the redundant data, or your own code does, the overhead is still incurred. With custom code you at least have an option of re-calculating your aggreagates during the off-peak times. But with indexed views, containing aggregates, the number crunching will be performed on every data modification. Trust me: you'll bring your server to its knees. Edited by - izaltsman on 06/14/2002 11:32:06 |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-14 : 11:29:54
|
| EDIT: I posted w/out seeing izaltsman's post, but i notice he's referring to aggregates in materialized views, while i am not.In many (most?) OLTP environments today the transaction load is high, but the posts to date on this thread have assumed that a transaction = a data modification. Not so. In fact I would wager a significant # of transactional systems are predominately performing reads. Not as a reporting system, but truly transactional. As an example, I recently developed a large scale collections system which, among many other things, ran a series of queries to display account information whenever the owner of that account was reached by a predictive dialer. During peak business hours the dialer might reach 5 people a second, and each contact results in more than a dozen queries.Given all those reads, a developer might be tempted to engage in denormalization to improve query times. A materialized view however is an excellent win-win design component in this situation (I would have used it had the system been 2K instead of 7.0). The "denormalized" table yields simpler, faster queries while maintaining normalization and data integrity.Thoughts?setBasedIsTheTruepath<O>Edited by - setbasedisthetruepath on 06/14/2002 11:32:33 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-14 : 11:52:53
|
izaltsman, I do respect your perspective on this and fully understand your thinking on the performance issue. I am granting that indexed views are not right for many situations;however, I am unwilling to believe that indexed view hold not value, ever.The client site that I am at currently performs a daily rebuild of the database from the ground up. I'm talking 16 gigs worth of data truncated and rebuilt from feeds from a mainframe everyday. It spends the other 18-20 hours of its day-life reporting. During the 4 to 6 hour load process the tables are trunced and all the indexes dropped to speed the load process. Then once all the data is in, all the indexes are rebuilt. Once the data is loaded there are no update,insert or delete statements issued (aside from against temp objects).However, because of the massive amounts of data, the reporting done during the day in unacceptable to the consumers. Indexes are optimized . . . even covering indexes applied to many queries . . . however, because of the joins and computations and the load, performance is not quite up to par.Now, if this weren't a friggen SQL7 system, I could use indexed views to cover some of these reporting queries. I would drop and recreate these view along with all the indexes as part of the loading process. I could potentially vastly improve reporting during the day.I'd like this thread to explore places where indexed view have worked for people. How they got there...how they implemented...results, etc. I'd love to hear lessons learned and ideas. I am not trying to solve a particular problem. I am just curious to hear about people's experiences.begin rantSometimes I get tired of posting "read bol" and "post ddl". I am just looking for a little intelligent discussion on the practical applications of this feature. Note: that's not to say that what has been said hasn't been intelligent  , its just that it hasn't quite been what I am looking for.....end rant<O> |
|
|
|
dsdeming
479 Posts |
Posted - 2002-06-15 : 10:03:09
|
I've looked at using them, but for me, this says it all. quote:
Although, all the rules around creating a view that is eligable for indexing (deterministiec, SET options, etc) are a bit cumbersome . . .
It reminds me of the 6.5 days when there was a mechanism for bypassing the normal insert/delete method of updating to do an update-in-place. It was supposed to be very fast, and I looked into implementing it. However, there were 7 or 8 conditions that had to be met, and they were so restrictive I just gave up. I wonder if anyone out there ever implemented update-in-place at all. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-08-22 : 09:24:08
|
| I'd like to resurrect this to see if anyone has done anything with view indexes lately. I recently ran some performance experiments around create a new table from my base schema and then creating an indexed view that mirros my new table. I was disheartened by the experiment outcomes. Basically, it seems to me that anything you can do with an indexed view, you can faster using traditional methods (so long as you write your triggers correctly).Jay White{0} |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-22 : 20:57:48
|
quote:
Basically, it seems to me that anything you can do with an indexed view, you can faster using traditional methods
I was going to add something along the lines of what you just wrote, but it seems everything I read says it for me, and better:quote:
When such an index is created, the view is executed and the result set is stored in the database in the same way a table with a clustered index is stored.
Along those lines, I don't see the benefit over create a regular table and populating it with the results that the view would provide, AND you avoid all of the restrictions an indexed view has.Not saying they aren't useful, but I'm not convinced that they're useful enough just yet. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-08-23 : 19:07:44
|
| SQL Server's Indexed views was a response to Oracles' materialised views. I remember Ellison bragging about the speed gains of using them (he didn't mentioned anything about Insert/Update/Delete performance). It seems that this put the MS marketing department in a twist and a directive was given to implement the same in SQL Server.I have never used thme in a production environment and most probably never will.But I must say that I am absoultely baffled by izaltsman comments on an indexed view reducing the Normalisation form of a database. Normalisation has NOTHING to do with storage. I am yet to read anything "academic" about normalisation being applied to physical storage let alone apply to views. From a user perspective, the view is just a relational expression. What/how the RDBMS does to achieve the result of this expression is outside the bounds of normalisation.DavidM"SQL-3 is an abomination.." |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-08-23 : 21:33:30
|
| WOW...jones'ing doesn't even start to explain...But what about patitioned views across many physical drives?Is this the same as partitioned indexes?Won't that thread out nicely?(I'm on an island with access to nothing right now, except for a modem and windows 98)Brett8-)SELECT POST=NewId()That's correct! It's an AlphaNumeric! |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-08-25 : 07:13:27
|
| Brett, partitioned views and indexed view are two seperate concepts.Jay White{0} |
|
|
|
|
Indexed View (discussion)