Caricamenti massivi e best practices. L’ordine corretto delle cose

Article
12/04/2012

In scenari di caricamenti massivi la best practice è sempre una, detta e ridetta:

DROP indici
caricamento massivo
CREATE indici

Bene.

Ora la domanda è: in che ordine eseguo le singole operazioni?

Intendo dire:

Supponendo che la mia tabella abbia sia un indice cluster, che uno o più indici noncluster: in che ordine eseguo le azioni da fare?

Chi cancello per primo? Il cluster o il non cluster? E, una volta terminato il caricamento, chi creo per primo?

Ma ci sarà una differenza nell’eseguire un’azione prima dell’altra?

Le domande dovrebbero essere banali ma, da quanto vedo, non è così chiara la risposta.

Provo a ragionare su due possibili serie di azioni:

Soluzione A
- drop cluster
- drop noncluster
- attività…
- create noncluster
- create cluster
Soluzione B
- drop noncluster
- drop cluster
- attività…
- create cluster
- create noncluster

Prima di proseguire, provate a chiedervi quali delle due strade prendereste.

Nel ragionare, ricordiamoci semplicemente che:

le foglie degli indici noncluster contengono, se esiste, la chiave cluster. Se non esiste il row-id della tabella HEAP.

Proviamo allora a rivedere con attenzione la Soluzione A che, se volessi raccontare nel dettaglio che cosa fa, diventerebbe:

io chiamo la drop cluster
il db engine aggiorna tutti gli indici noncluster eliminando, a livello foglia, il riferimento alla chiave cluster che non esiste più e portando un row-identifier in grado di identificare univocamente la riga a cui l’elemento indicizzato si riferisce
io chiamo la drop noncluster
faccio attività…
io chiamo la create noncluster
io chiamo la create cluster
il db engine aggiorna tutti i noncluster per portare, a livello foglia, la chiave cluster (eliminando il row-identifier scritto al momento della creazione degli indici e scrivendo la chiave cluster appena creata)

E’ evidente che, con questa soluzione, obblighiamo il db engine a fare del lavoro (assolutamente inutile) e tranquillamente evitabile.

Che senso ha, quando cancello o creo gli indici, far aggiornare i noncluster?

Giusto per essere precisi: i punti 2 e 7 sono attività NON necessarie.

Possiamo correggere la cosa semplicemente dando l’ordine corretto alle nostre istruzioni, utilizzando la Soluzione B:

drop noncluster
drop cluster
attività…
create cluster
create noncluster

Facendo un test su una tabella da 2 milioni di righe, con questa struttura:

indice cluster su chiave primaria
3 indici noncluster, uno per ogni colonna

Si ottengono questi numeri (msec):

Credo parlino da sè… :-)

Guardando le statistiche di I/O ecco che cosa avviene dietro le quinte:

Soluzione A

Soluzione B

Come si può verificare anche dall’immagine, con una corretta sequenza, l’attività di DROP non fa nulla (come è giusto che sia) e l’attività di CREATE lavora solo una volta su ciascun indice.

Il consiglio? Verificate l’ordine che date a queste attività nelle vostre procedure di caricamento massivo di dati…

Di seguito lo script per ricreare il test:

 USE test
GO

 SET NOCOUNT ON;
 SET STATISTICS TIME OFF;
 DECLARE @t datetime;
 
 PRINT '-->  Test NVARCHAR(MAX) INSERT';
 SET @t = GETDATE( );
 SELECT TOP 2000000 
     CAST( ROW_NUMBER( )OVER( ORDER BY C.Object_ID )AS nvarchar( 35 ))AS  Col1,
     CAST( ROW_NUMBER( )OVER( ORDER BY C.Object_ID )AS nvarchar( 35 ))AS  Col2,
     CAST( ROW_NUMBER( )OVER( ORDER BY C.Object_ID )AS nvarchar( 35 ))AS  Col3 INTO testCluster
   FROM Master.sys.All_Columns C CROSS JOIN Master.sys.All_Columns C2;
 SELECT 'create table', DATEDIFF( MILLISECOND , @t , current_timestamp );


SET @t = GETDATE( );
ALTER TABLE testCluster
 ADD ColInt int NOT NULL IDENTITY( 1 , 1 );
ALTER TABLE testCluster ADD  CONSTRAINT [PK] PRIMARY KEY CLUSTERED ( colInt ) 
SELECT 'pk', DATEDIFF( MILLISECOND , @t , current_timestamp );


/* generate index */
 SET @t = GETDATE( );
create index idx1 on testCluster(col1)
create index idx2 on testCluster(col2)
create index idx3 on testCluster(col3)
SELECT 'create index', DATEDIFF( MILLISECOND , @t , current_timestamp );
GO


/* ************************** SOLUZIONE A ************************** */
/* drop index */
DECLARE @t datetime;
SET @t = GETDATE( );
    /* drop pk cluster, drop index */
    ALTER TABLE testCluster DROP CONSTRAINT [PK]    
    DROP INDEX idx1 ON testCluster;
    DROP INDEX idx2 ON testCluster;
    DROP INDEX idx3 ON testCluster;
    
SELECT '--- drop index', DATEDIFF( MILLISECOND , @t , current_timestamp );


SELECT 'some stuff...';


SET @t = GETDATE( );

    /* create index, create pk cluster */
    CREATE INDEX idx1 ON testCluster( col1 );
    CREATE INDEX idx2 ON testCluster( col2 );
    CREATE INDEX idx3 ON testCluster( col3 );
    ALTER TABLE testCluster ADD  CONSTRAINT [PK] PRIMARY KEY CLUSTERED ( colInt ) 

SELECT '--- recreate  index', DATEDIFF( MILLISECOND , @t , current_timestamp );
GO


/* ************************** SOLUZIONE B ************************** */
/* drop index */
DECLARE @t datetime;
SET @t = GETDATE( );
    /* drop index, drop pk cluster */
    DROP INDEX idx1 ON testCluster;
    DROP INDEX idx2 ON testCluster;
    DROP INDEX idx3 ON testCluster;
    ALTER TABLE testCluster DROP CONSTRAINT [PK]

SELECT '--- drop index', DATEDIFF( MILLISECOND , @t , current_timestamp );


SELECT 'some stuff...';


SET @t = GETDATE( );

    /* create pk cluster, create index */
    ALTER TABLE testCluster ADD  CONSTRAINT [PK] PRIMARY KEY CLUSTERED ( colInt ) 
    CREATE INDEX idx1 ON testCluster( col1 );
    CREATE INDEX idx2 ON testCluster( col2 );
    CREATE INDEX idx3 ON testCluster( col3 );

SELECT '--- recreate  index', DATEDIFF( MILLISECOND , @t , current_timestamp );
GO


/* pulizia */
DROP TABLE testCluster;
GO

Enjoy.

Comments

Anonymous
December 06, 2012
Ciao Andrea, ottima guida, mi ha impressionato. L'ho messa subito in pratica. La procedura su cui l'ho messa in pratica (caricamento massivo di circa 2 milioni di righe) ha immediatamente dato un buon riscontro: dai soliti 18 minuti siamo passati a soli 10. Grazie!!
Anonymous
December 07, 2012
Ciao Andrea! A quanto dici, aggiungo una cosa, ossia la compressione. Anche questa e' meglio rimuoverla prima del caricamento e quindi riabilitarla successivamente. Una cosa che e' poco nota, pero', e' che se si fa il drop dell'indice cluster, l'opzione di compressione rimane cmq impostata sull'HEAP ed e' quindi necessario riportare l'opzione DATA_COMPRESSION a NON manualmente.

Share via

Caricamenti massivi e best practices. L’ordine corretto delle cose

Comments

Additional resources