March 10, 2010 at 10:00 am
My company is using a bunch of unindex views to return data for strongly-typed data sets. I cannot create indexes on the views even with Schemabinding as many of them use outer joins. So all this data is sitting unorganized when their queries hit them.
So I was wondering if anyone had any ideas about organizing data prior to filling the views. At first I went down the road of doing everything the view does in a proc where I could use temp tables and indexes as I wanted, but then realized that I couldn't select from the proc for the view.
Then I though by breaking down the view into several, I could organize them in bitz so I could put indexes on some while knowing once I hit the outer joins I couldnt do them all, but then I am not even sure I can select from a view in a view as I write this.
Do indexes on tables help filling the view? I assume so, and the base tables have crappy indexes too, but it table a and table b had good indexes, and I was selecting from view c without indexes, then wouldn't i be doing a scan anyway?
Thanks for your help ahead of time I need it.
March 10, 2010 at 10:20 am
If the underlying tables are properly indexed the views should work well, as long as you don't have multiply nested views.
March 10, 2010 at 10:41 am
Hi Lynn,
But how do I know the order which the data in the view is ordered? I mean if table 1 is indexed on column 1, indexed on table 2 column 1, and indexed on table 3 column 1 and all three columns are in my view, which column dictates the order?
Thats why I need the index for the query on the view...
March 10, 2010 at 10:48 am
KTG (3/10/2010)
Hi Lynn,But how do I know the order which the data in the view is ordered? I mean if table 1 is indexed on column 1, indexed on table 2 column 1, and indexed on table 3 column 1 and all three columns are in my view, which column dictates the order?
Thats why I need the index for the query on the view...
The only way the data in the view will be ordered in when an ORDER BY clause is applied to the select that is using the view. Just as the order of records in a table is not guaranatted, neither is the order of data in a view.
The only way to index a view to is to create an indexed view, and you have already determined that you can't do that due to the OUTER JOINS that exist in the current views.
You need to determine what indexes are needed on the underlying tables in the view based on the query that defines the views.
You can start by analizing the actual execution plans of the views. If you need help with that, please read the second article I reference below in my signature block and follow the instructions in that article when asking for help.
March 10, 2010 at 11:02 pm
Views do not contain any data unless indexed, so the question of the order of data in a view only applies to indexed views - which are logically ordered by the clustered index, just as for tables. Indexed views can support non-clustered indexes too, of course.
A view, like an in-line table-valued function (iTVF), is stored only as meta-deta: the logical description of the SELECT statement it contains. When you reference a view or iTVF, the definition is expanded into the whole query, and optimization starts from there. The expanded query therefore always only references objects with physical storage, tables or indexed views, for example. Since optimization starts at this step, it is clear that only indexes on tables and indexed views can be used.
Create useful indexes on the base tables and indexed views, and the optimizer will usually construct an efficient plan that is close to optimal. Use indexed views appropriately to pre-aggregate results. It can make sense to build higher-level views from other views, indexed views, iTVFs, and base tables...but it depends on the exact circumstances.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply