Tag Info

New answers tagged

1

I have now moved from dev to production. the table FactBackOrder has 4,183,289 rows. We're in sql server 2012. Microsoft SQL Server 2012 (SP1) - 11.0.3412.0 (X64) Mar 2 2014 01:25:09 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) But I don't want to use Columnstore indexes - these tables are ...


6

Aarons answers covered this question well. Two things to add: Aggregation indexed views can lead to cross-row contention and deadlocks. Normally, two inserts do not deadlock (except for rather rare conditions such as lock escalation or lock hash collisions). But if both inserts address the same group in the view they will contend. The same point stands for ...


9

As you've noted, the view itself only materializes a small number of rows - so even if you update the whole table, the additional I/O involved with updating the view is negligible. You probably already felt the biggest pain you're going to feel when you created the view. The next closest will be if you add a gazillion rows to the base table with a bunch of ...


0

Concurrent Update (Postgres 9.4) Postgres 9.4 does exactly that, incremental updates to a materialized view, with its new concurrent update feature. To quote the doc… Prior to PostgreSQL 9.4, refreshing a materialized view meant locking the entire table, and therefore preventing anything querying it, and if a refresh took a long time to acquire the ...



Top 50 recent answers are included