July 19, 2001 at 5:08 am
Generally speaking, if you have a query with several views joined (also with several tables), do the views optimize as well as having temp tables with indexes on the foreign keys?
July 22, 2001 at 6:11 pm
Generally speaking no but With SQL2K you can have indexes on views so if you're running sql2K its the way to go. Here is some info on indexed views http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/indexedviews1.asp
I pulled the following from the link above:
Performance Gains from Indexed Views
Using indexes to improve query performance is not a new concept, however, indexed views provide additional performance benefits that cannot be achieved using standard indexes. Indexed views can increase query performance in the following ways:
Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
Tables can be prejoined and the resulting data set stored.
Combinations of joins or aggregations can be stored.
July 22, 2001 at 11:17 pm
So, basicly it stores all the data in an index almost like it was a seprate indexed table.
July 23, 2001 at 9:25 pm
Thats about it. You have to have a clustered key to make it work, along with a bunch of other rules.
I don't entirely agree with Leon's answer - indexed views are certainly the right option in some cases, especially if you're aggregating - but if you've done a good job on indexing your base tables then queries against your views should perform pretty well. Before you go with temp tables or an indexed view, I'd take a hard look at the query plan to see what else you can change first.
A good way to find out for sure would be test the query against your view of views (!), then just try running the same query without the views (one big sql statement). Keep in mind the effects of data caching - best to run each query once to load the cache, then a second time to see what you get. You could also compare the query plans to see if SQL is unable to deciper the more complex views effectively. If you find a case where this is true (entirely possible) it would be great if you could post it here.
Andy
December 8, 2002 at 3:48 pm
SQL server creates the result sets for views in storage as needed. Temporary tables are created on disk in tempdb unless you have the "tempdb in RAM" option specified and enough memory to back up your request.
So, as usual, it depends. If you have large queries over static data that join tables, consider creating indexed views for them.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply