No Performance gain with Indexed view ?
-
Hemant,
If you're using Developer edition, this should automatically use the View without a hint (it's the same optimizer as Enterprise Edition). I think Eddie was checking that you weren't using Standard Edition, which would require the use of an index hint.
An indexed view CAN make a difference in the right circumstances. By indexing the view, you actually materialize the underlying data (i.e. that data is copied into a physical structure), which is NOT the same as having a clustered index on a table that an ordinary view references. This means that your query on the indexed view requires no calculations, no joins, no aggregations and no temp tables - all the data is physically present in a mini clustered table. You can even add secondary indexes to the indexed view if necessary.
As you're probably aware, there are many restrictions on the statements and functions allowed, and it looks as if many of the portions of your query which are causing the poor performance cannot be directly improved by using an indexed view (LEFT OUTER JOIN, MAX() ... for example)
I don't entirely agree with Colin (which is unusual!) - the fact that the materialised data persists is as much a strength of an indexed view as it is a weakness (it's only a weakness if the underlying table is updated frequently - this means your materialised data has to be recalculated). If your view includes aggregated columns and joins, then having the materialised data is a positive advantage.
I'm also not sure what Colin means by a "covered clustered index". Any covered non-clustered index on a table will act as a clustered index, and you don't need to go to the trouble of building an indexed view. Colin - can you elaborate?
-
One more thing ...
Don't simply accept the argument that indexing a boolean column is a waste of time. It can be a very valuable index! Like any other index, it depends on data distribution. If all rows have a pretty even distribution of 0s and 1s (say, 45% / 55%), then an index will be useless. Even if the distribution is 5% / 95% , chances are the index will be useless. However, this is true of ANY datatype, not just booleans.
If you have a million row table, and 10 rows are flagged as boolean 1, with all others being boolean 0, then it is absolutely a good index (so long as you are looking for the boolean 1 records
)One catch, though:
SELECT ....
FROM Table
WHERE Bit_Col = 1
would use an index on Boolean_Col, but would scan, not seek. This is because the constant "1" is an integer, and so the column data gets implicitly converted to integer before the comparison is made, forcing an index scan (still more efficient than a clustered index scan, though)
To enable the optimizer to use an index SEEK, you'd need to write:
SELECT ....
FROM Table
WHERE Bit_Col = CAST (1 as BIT)
-
Hello,
I do not have very much experience with indexed views but the problem I belive has affected me most are that my SET options differ from the SET options that were enabled when I created my indexed view. Read this for example:
http://msdn2.microsoft.com/en-us/library/ms175088.aspx
Excerpt:
The SET statement can dynamically change the session options; therefore, issuing SET statements in a database that has indexes on views and indexed computed columns must be carefully performed. For example, an application can make a connection in which the default settings allow for an indexed view or indexed computed column to be referenced; however, if the connection calls a stored procedure or trigger that has a first statement of SET ANSI_WARNINGS OFF, this statement overrides previous defaults or settings for ANSI_WARNINGS. In this case, the optimizer ignores all indexed views or indexed computed columns when it processes any statement in the stored procedure or trigger
Hope this can be helpful. Of course it couldbe quickly tested with doing your test select in the same connection window you used when you created the materialized view...
Regards,
Hanslindgren
-
Thanx Philip Yale for your directions and guidance
. I will look more deeply into your thoughts and try to work on that. One more thing...I haven't indexed the boolean columnThanx Hanslindgren for your directions also
Will definitly look into the linka and see whatz there for me.Thanx again guys,
Regards,
Hemant.
-
Philip - a covered non clustered index takes it's selectivity based upon the first column, should this column not be as selective as it could be, or becomes that way, then the optimiser may disregard the index and default for the clustered index, or another more selective non clustered index. Disregarding the indexed views, a clustered index will be used even if it is less selective, as the optimiser tends to favour clustered indexes. Of course "it just depends" and one set of circumstances may not be the same as the next.
I accept that covered indexes don't have to be in such precise column order as they used to be - I've been doing this since sql 6.0 and old habits die hard !!!
As the indexed view creates a clustered index then the optimiser is likely to use this index if it is less selective than a secondary index, e.g. optimiser ignores non clustered secondary index - create as clustered or indexed view, optimiser uses it.
Persisting duplicated or aggregated data this way isn't really within best practices I guess but that's not a discussion I want to get into at this time!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
OK Colin - I see where you're coming from now
BTW - I did attend Kimberley Tripp's Reading seminar - fantastic! "Inspiring" is an over-used word for some people, but I think she merits it!
Viewing 6 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply