December 19, 2016 at 7:22 pm
Sorry if this is a really noob question. I did search before posting.
I have a table that has 4 indexes. For example, one index is year. With roughly 20 years, if we assume an even distribution the index will retrieve 5% (for a given year).
I also have a simple view of that table which drops 4 columns (such as DimensionCheckSum used for SCD2 processing), and creates 3 simple derived columns (concatenation of some columns, a constant value, a sum, etc).
The view is the single table (i.e. no join) with schemabinding.
The end user will access the data via the view.
Questions:
1) In this scenario, are there performance gains from adding indexes to the view?
2) Corollary to #1: does a query against the view utilize the indexes on the table for better performance?
Another reason I ask: the data load of the table is using a somewhat complex MERGE to implement SCD2 processing. If the view is indexed, the table load fails with this error:
ERROR: CLI execute error: [Microsoft][SQL Server Native Client 10.0][SQL Server]Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.
If I leave the indexes off the view, the code works.
Thanks...
December 20, 2016 at 2:27 am
Scott In Sydney (12/19/2016)
1) In this scenario, are there performance gains from adding indexes to the view?
Unlikely. The typical use case for indexes on views is to materialise a complex query that has joins or, commonly, aggregations.
2) Corollary to #1: does a query against the view utilize the indexes on the table for better performance?
Maybe. Depends whether SQL uses the indexed view or goes to the base tables of the view.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2016 at 3:40 am
GilaMonster (12/20/2016)
2) Corollary to #1: does a query against the view utilize the indexes on the table for better performance?
Maybe. Depends whether SQL uses the indexed view or goes to the base tables of the view.
Can you expand on this? For example, would:
SELECT * FROM vwTable WHERE YEAR=2000
use the year index on the Table?
December 20, 2016 at 4:00 am
Again, depends on whether the optimiser chooses to use the indexed view itself (which is materialised and stored as if it was a table), or ignore the materialised view and go to the base table.
And that choice depends on the indexes on the view, the indexes on the table and the query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply