September 9, 2009 at 11:24 am
Hopefully I can describe this better than my topic 😀
Basically I have a dimension table that is part of an ETL process where there are insert and update statements ran against it. There is also an indexed view that utilizes a few columns from this table.
The problem is, if I drop the views and run the Load on this table it takes less than a second. If the views are in place, it takes around 16 minutes. Checking the execution plans, it appears quite a lot is being done to the views even though they are not referenced at all in the query.
I have tried joining them in the insert and update statement and using the with(noexpand) hint but that doesn't seem to do anything.
Aside from dropping the views and loading the table then recreating the views, is there a better method? Note; droping the views, running the load and recreating the views takes about 2 minutes, so this is much more efficient, but no where near the 1 second it takes without the views.
Link to my blog http://notyelf.com/
September 9, 2009 at 12:25 pm
First of all it doesn't matter that you aren't referencing them at all in your queries because an indexed view is schemabound and heavily dependent on the underlying data. An indexed view is also called a materialized view and it VERY similar to a table. Think of it this way, you have two tables that make up a third table. When you make a change in either of the two base tables a trigger updates the third table. This is similar to what you are experiencing. Is there a particluar reason this view is indexed and more importantly are you using Enterprise Edition?
CEWII
September 9, 2009 at 12:33 pm
We are using SQL Server 2005 Standard Edition.
We have a massive fact table that contains Invoices on a very granular level. The indexed view aggregates much of this into a smaller more pallatable form that we report off of (cuts about 3 million records down to 500k and some 100 columns down to 12).
It does substantially increase performance, but obviously this one hiccup annoys me 😀
Link to my blog http://notyelf.com/
September 9, 2009 at 1:10 pm
Then you might as well drop the index on that table because it won't be used except if it is explicitly referenced.. SQL 2005 Std can create indexed views but the optimizer is coded to ignore the index, it treats it as if it isn't there. But you have overhead to maintain it even though the optimizer won't use it.. But:
http://technet.microsoft.com/en-us/library/cc917715.aspx
Q. Why isn't my indexed view being picked up by the query optimizer for use in the query plan?
A. There are three primary reasons the indexed view may not be being chosen by the optimizer:
(1) You are using a version other than Enterprise or Developer edition of SQL Server. Only Enterprise and Developer editions support automatic query-to-indexed-view matching. Reference the indexed view by name and include the NOEXPAND hint to have the query processor use the indexed view in all other editions.
If your SELECT queries reference it directly it can be used.. I'd drop and rebuild it myself.
CEWII
September 9, 2009 at 2:30 pm
Drop and rebuild the Indexed view so it is just a view? I didn't quite all understand that one 😀
Link to my blog http://notyelf.com/
September 9, 2009 at 2:39 pm
Drop the index, leave the view. Don't rebuild the index..
If the query using the view makes an explicit reference to the index using hints then and only then will the optimizer use that index. Most people miss that part and wonder why they didn't get a performance boost. Without the index the view is only executed at the moment its used. Not when the underlying objects are manipulated.
CEWII
September 9, 2009 at 2:49 pm
got it.
I did just that and yes the performance is the exact same.
I did check the execution plan before however, and the Clustered index on the View was being used. All the same, the non indexed view works just the same.
Link to my blog http://notyelf.com/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply