hi,
I am using SQL Servre 2000.
i have made tests trying to improve performance by using Indexed View.
i have a table T1, that has a datetime field F1.
i have another table T2, that holds links between records in T1.
T2 has 4 fields :
Recid - identity column
recid1 int - identifies a row in T1
recid2 int - identifies another row in T1
LT int - link type
i want to build a query that retrieves all the rows from T1 that has a certain link type, and T1.F1 equals
a certain date (or between date range, never mind).
well, i created a view V1 with a select like :
select T2.recid T2_recid, T2_recid1 T2_recid1, T2.recid2 T2_recid2, T2.LT T2_LT, T1.F1 T1_F1 from T2 join T1 on T2.recid1=T1.recid
than i added 2 indexes:
create unique clustered index IX_V1 on V1 (T2_recid)
create index IX_V1_LT on V1 (T2_LT,T1_F1)
than i ran a query such as:
select * from T2 join T1 on T2.recid1=T1.recid where T2.LT=X and T1.F1=Y
the execution time was about 20 seconds...
i also tried to reference V1 directly in the FORM clue, and not just hoping the optimizer to find out that
it should use it. so i made something like:
select * from V1 join T2 on V1.T2_recid1=T1.recid where V1.T2_LT=X and V1.T1_F1=Y
the same, the execution time was about 20 seconds...
thanks,
edo.
Indexed View - has no influence on performance
Juned
thank you very much for your answer.
in case i had an index on T1.F1, it might make sence to use this index and than cross to T2, although, as far as i understand, this clustered view i described should give the best performance. but, in this case (as i saw in the execution plan), the optimizer choose to make a Table scan for a table of ~2 millions of records........
i even tried to change the select clue so only T2.LT , T1.F1 exist. in such case, everything should be found in the index of the view, there is no need to even fetch the records or join the tables... but still: Table scan for a table of ~2 millions of records........
don't you think this should be considered as a bug
could you please, supply me with a working example of using indexed view
thanks again,
edo.
Rizshe
The use of indexed view for a particular query is a cost-based decision. In some cases, if the optimizer sees a trivial query it may not even consider the indexed view substitution. Please take a look at the blog below:
http://blogs.msdn.com/sqltips/archive/2005/10/05/Top_N_costly_query_plans.aspx
I have a query there which will help you get the cost for the query, optimization level etc.
Nick Rumble
well, thank you very much!
the answer was there (in 2005 only!): i was in shock to find out that this feature is not supported in standard edtion ! !!
ok, microsoft has the right to not include such must-have feature in there standard edition, "especially" while they have no other solution for cross-table indexes (BTW it's a feature i had in unknown databases like paradox). there are many pages in the "SQL Server Books Online" about indexed view but i think the only page they mention that it's supported in enterprise&developer editions only is the page of editions-comparison. of course i missed it. and i beleave i'm not the first nor the last...
finally, i referenced the view directly by it's name and used the WITH (NOEXPAND) hint. the performances now are great. the final-subtree-cost (showed in the Execution plan) decreased from 22 (with index on T1.F1) to 0.136, which is ~160 times better.
"thanks" to microsoft. and thank you, Umachandar Jayachandran, again.
edo.
Zach Salzbank