Aggiornamento delle tabelle con indici columnstore su SQL Server 2012

[et_pb_section admin_label=”section”][et_pb_row admin_label=”row”][et_pb_column type=”4_4″][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left” use_border_color=”off” border_color=”#ffffff” border_style=”solid”]

Già dalla versione 2012, in SQL Server sono disponibli gli indici columnstore, che sono particoalri tipi di indice destinati a offrire performance elevate su tabelle che ospitano svariati milioni di righe, più di 10 milioni da best practice.

Se nelle versioni recenti, 2014 e 2016, questi indici supportano l’aggiornamento consentendo la modifica alle tabelle che stanno dietro, nella versione 2012 ogni tabella che ha associato un indice di questo tipo diventa di fatto di sola lettura, cosa che la rende un tantino scomoda.

Nelle strategie di datawarehousing, infatti, se è vero che le operazioni di aggiornamento non sono continue come in un ambiente transazionale, è certamente vero che gli aggiornamenti ai dati sono quotidiani, settimanali, comunque periodici.

Mamma Microsoft dice che per apportare le modifiche alle tabelle si deve distruggere l’indice (disabilitarlo quantomeno), quindi ricostruirlo alla fine del processo di modifica.

• To update a table with a columnstore index, drop the columnstore index, perform any required INSERT, DELETE, UPDATE, or MERGE operations, and then rebuild the columnstore index.
• Partition the table and switch partitions. For a bulk insert, insert data into a staging table, build a columnstore index on the staging table, and then switch the staging table into an empty partition. For other updates, switch a partition out of the main table into a staging table, disable or drop the columnstore index on the staging table, perform the update operations, rebuild or re-create the columnstore index on the staging table, and then switch the staging table back into the main table.
• Place static data into a main table with a columnstore index, and put new data and recent data likely to change, into a separate table with the same schema that does not have a columnstore index. Apply updates to the table with the most recent data. To query the data, rewrite the query as two queries, one against each table, and then combine the two result sets with UNION ALL. The sub-query against the large main table will benefit from the columnstore index. If the updateable table is much smaller, the lack of the columnstore index will have less effect on performance. While it is also possible to query a view that is the UNION ALL of the two tables, you may not see a clear performance advantage. The performance will depend on the query plan, which will depend on the query, the data, and cardinality estimations. The advantage of using a view is that an INSTEAD OF trigger on the view can divert updates to the table that does not have a columnstore index and the view mechanism would be transparent to the user and to applications. If you use either of these approaches with UNION ALL, test the performance on typical queries and decide whether the convenience of using this approach outweighs any loss of performance benefit.

Se dovessimo farlo in una stored procedure, la cosa sarebbe tipo questa:

ALTER INDEX <indexname> ON <tablename> DISABLE;
UPDATE / INSERT / DELETE on data table;
ALTER INDEX <indexname> ON <tablename> REBUILD;

Peccato che facendo così all’esecuzione dell’istruzione di modifica dei dati (inserimento, modifica o cancellazione che sia) ci ritrovi stampato a video un bell’errore che in sostanza dice che una tabella con un indice columnstore non può essere aggiornata.

Technet continua a dirci che nello stesso batch SQL non sono supportate istruzioni di modifica di indice e di modifica dei dati.

Io ne sono venuto a capo con una hint da aggiungere alle query, che è “OPTION(RECOMPILE)” ; le query diventano tipo così:

ALTER INDEX <indexname> ON <tablename> DISABLE;
UPDATE / INSERT / DELETE on data table OPTION(RECOMPILE);
ALTER INDEX <indexname> ON <tablename> REBUILD;

La hint obbliga l’ottimizzatore a rielaborare un nuovo piano di esecuzione; in queste condizioni lo stesso capisce che l’indici è disabilitato e che la tabella può essere aggiornata.

Alla fine basta ricostruire l’indice e tutto funziona a meraviglia.

[/et_pb_text][/et_pb_column][/et_pb_row][/et_pb_section]