Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chisholmd
Starting Member
5 Posts |
 Posted - 2004-03-08 : 15:39:17
|
| I have a few monster queries with waaay too many joins, my plan was to go back and address them in the next version.It seems like a materialized view might save me from having to create a bunch of new intermediate tables. Does anybody use them? Whats the performance/maintainence been like?davethink things not words -O.W.Holms |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2004-03-08 : 16:49:37
|
| if you're designing a reporting database you should consider denormalizing to a star-schema (ralph kimbal) design via an etl job at night and then have you're queries hit that.Preferably, it should also be located on a separate server so that it does not impact the performance of your oltp system.There are alot of constraints on having indexed views the performance as rated by a few systems has been anywhere up to 30% improvement. It depends on how good your clustered index is and how well the query plans take advantage of it. |
 |
|
|
chisholmd
Starting Member
5 Posts |
Posted - 2004-03-08 : 19:08:04
|
| Well the queries are part of the oltp system. The db is heavly normalized and in a few circumstances I have this large queries to deliver all the information in one go. I have to analys each one indivudualy to see if it could be improved by splitting it into to selects, or if I should maintain a reference table of some information as it is generating for easier retrieval without having to join my way to it.After reading a bit about materialized views (index views?) it sounded like I could take a short cut by just making these few monster queries into materialized views and trade disk space for CPU time.I guess I'll have to experiement to find out for myself. They might not be good candidates for this solution at all.Thanksthink things not words -O.W.Holms |
|
|
|
chisholmd
Starting Member
5 Posts |
Posted - 2004-03-08 : 20:46:38
|
| Wow I just read my previous post and it reads as if it was written by well a moron.Here is a cleaned up version:Well the queries are part of the oltp system. The db is heavily normalized and in a few circumstances I have these large queries which attempt to deliver all the information needed in just one procedure.I have to analys each query individualy to see if it could be improved by splitting it into two or more statements, or I could also add a few reference tables that would be populated in the course of things. This would make for easiet retrieval.After reading a bit about materialized views (index views?) it sounded like I could take a short cut by just making these few monster queries into materialized views and trade disk space for CPU time.I guess I'll have to experiement to find out for myself. They might not be good candidates for this solution at all.Thanksdavethink things not words -O.W.Holms |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-03-08 : 21:51:39
|
| I have found indexed views in SQL server to be incredibly limited. I have tried to implement them in half a dozen projects without a huge amount of success. In fact, I've had to rip them out on several occasions because of other problems they have caused. I can't see them being especially useful in an OLTP environment, especially for objects that are updated frequently.My opinion is that indexed views (SQL Server) and materialized views (oracle) were implemented solely to improve the TPC-C results. Anyway, post some DDL for your tables and views (including all indexes) and we can help you with other suggestions. Include your slow running queries too.-ec |
|
|
|
|
|
|
materialized views