July 7, 2011 at 7:35 am
Hi friends,
as i know Oracle offers several refreshmode options for their materialized views (on demand, on commit, periodically). Does Microsoft SQLServer offer the same functions for their indexed views?
If not, how can I else use indexed views on SQLServer if my purpose is to export data on a daily+ on-demand basis, and want to avoid performance overhead problems? Does a workaround exist?
July 7, 2011 at 7:36 am
No. Indexed views, like indexes are maintained automatically as part of the transactions that modify the base tables.
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
July 7, 2011 at 7:49 am
If you don't want to pay for the overhead, you can disable the clustered index on the view and re-enable it with ALTER INDEX REBUILD before running the daily export job.
Hope this helps
Gianluca
-- Gianluca Sartori
July 7, 2011 at 7:54 am
Gianluca Sartori (7/7/2011)
If you don't want to pay for the overhead, you can disable the clustered index on the view and re-enable it with ALTER INDEX REBUILD before running the daily export job.
However it might be quicker just to generate the resultset on the fly for the export than to materialise a view just for that. The materialisation will take data and log space and just as long as running the query for the view normally.
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
July 7, 2011 at 7:55 am
Awesome idea. But why do you need to rebuild just after reenable? Sounds like doing the work twice!
July 7, 2011 at 8:10 am
Ninja's_RGR'us (7/7/2011)
Awesome idea. But why do you need to rebuild just after reenable? Sounds like doing the work twice!
You re-enable a clustered index (or any index infact) by rebuilding it. There's no ALTER INDEX .. ENABLE command
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
July 7, 2011 at 8:13 am
GilaMonster (7/7/2011)
Ninja's_RGR'us (7/7/2011)
Awesome idea. But why do you need to rebuild just after reenable? Sounds like doing the work twice!You re-enable a clustered index (or any index infact) by rebuilding it. There's no ALTER INDEX .. ENABLE command
Tx. Do you know why they chose to deviate from the standards in this case?
July 7, 2011 at 8:26 am
GilaMonster (7/7/2011)
Gianluca Sartori (7/7/2011)
If you don't want to pay for the overhead, you can disable the clustered index on the view and re-enable it with ALTER INDEX REBUILD before running the daily export job.However it might be quicker just to generate the resultset on the fly for the export than to materialise a view just for that. The materialisation will take data and log space and just as long as running the query for the view normally.
Completely agree. However I ended up using materialized views to optimize performance in some situations when I had no other option, usually because I could not modify the code.
For instance, in merge replication, you have no access to the triggers code and they usually implement join filters using DISTINCT on (many) nested views: the deeper you go into the join tree and the query plans become more and more complicated. When the number of tables involved is huge, the optimizer can easily end up with a poor plan and a nice way to help it is preparing a portion of the query as an indexed view.
-- Gianluca Sartori
July 7, 2011 at 8:44 am
Ninja's_RGR'us (7/7/2011)
GilaMonster (7/7/2011)
Ninja's_RGR'us (7/7/2011)
Awesome idea. But why do you need to rebuild just after reenable? Sounds like doing the work twice!You re-enable a clustered index (or any index infact) by rebuilding it. There's no ALTER INDEX .. ENABLE command
Tx. Do you know why they chose to deviate from the standards in this case?
No, but I'd guess so that it was clear what you're doing when you enable a disabled index.
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
July 7, 2011 at 8:51 am
GilaMonster (7/7/2011)
Ninja's_RGR'us (7/7/2011)
GilaMonster (7/7/2011)
Ninja's_RGR'us (7/7/2011)
Awesome idea. But why do you need to rebuild just after reenable? Sounds like doing the work twice!You re-enable a clustered index (or any index infact) by rebuilding it. There's no ALTER INDEX .. ENABLE command
Tx. Do you know why they chose to deviate from the standards in this case?
No, but I'd guess so that it was clear what you're doing when you enable a disabled index.
I think I would have made that command read something like ALTER INDEX X ON dbo.Whatever ENABLE WITH REBUILD.
And throw and error if the with rebuild is ommited.
I need to send me resume in :w00t:
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply