Descrivi le caratteristiche dell'implementazione delle inserzioni di massa di dati in SQL in termini di ottimizzazione delle prestazioni, integrità e blocchi. A cosa prestare particolare attenzione quando si lavora con grandi volumi di dati?

Risposta

L'inserimento di massa di dati (bulk insert) è un compito comune durante la migrazione, l'importazione o il riempimento di grandi tabelle. L'efficienza di tale operazione dipende da diversi fattori:

1. Utilizzo di inserzioni batch (Batch Insert): Suddividi i dati in pacchetti ragionevoli (batch) — solitamente migliaia di righe alla volta. Questo riduce il carico sui registri delle transazioni e diminuisce i blocchi.
2. Disattivazione di indici e vincoli durante il bulk insert: La rimozione temporanea o la disattivazione di indici secondari e chiavi esterne possono accelerare l'inserimento. Dopo il completamento dell'operazione, ricrea gli indici.
3. Controllo sulle transazioni: Esegui le inserzioni all'interno di transazioni con un numero fisso di righe, per evitare l'accumulo di registri troppo grandi (log file).
4. Utilizzo di strumenti speciali: Ad esempio, BULK INSERT o COPY (PostgreSQL) — essi funzionano più velocemente rispetto agli INSERT normali in un ciclo.
5. Caricamento solo delle colonne necessarie: Escludi i dati superflui — questo riduce il volume del traffico e il tempo di elaborazione.

Esempio (SQL Server):

BULK INSERT my_table
FROM 'C:\data\bulkdata.csv'
WITH (
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '
',
  BATCHSIZE = 5000,
  TABLOCK
);

TABLOCK riduce i conflitti di blocco durante le inserzioni di massa.

Domanda trabocchetto

Domanda: È possibile disattivare e ricreare indici in qualsiasi momento per accelerare l'inserimento di massa, se la tabella è coinvolta in transazioni?

Risposta: No, se la tabella è coinvolta in transazioni attive, disattivare o ricreare gli indici può portare a blocchi, violazioni dell'integrità dei dati o persino perdita di dati, se la transazione viene annullata. Questa operazione dovrebbe essere eseguita solo al di fuori delle transazioni, o pianificare in anticipo le finestre di manutenzione.

Esempio di codice:

-- Errato:
BEGIN TRAN;
ALTER INDEX ALL ON my_table DISABLE;
-- ... bulk insert ...
ALTER INDEX ALL ON my_table REBUILD;
COMMIT;

Tale disattivazione è inaccettabile all'interno di lunghe transazioni!

Esempi di errori reali dovuti alla mancanza di conoscenza delle sfumature dell'argomento

---

Storia 1: In un progetto, inserimenti di massa paralleli in una tabella con diversi indici univoci hanno portato a frequenti deadlock e a un calo drastico delle prestazioni. La soluzione è stata la disattivazione temporanea degli indici non chiave durante il periodo di importazione e la riduzione della dimensione delle operazioni batch.

---

Storia 2: Gli sviluppatori hanno dimenticato di disattivare il controllo delle chiavi esterne durante il caricamento dei dati, e ogni inserimento verificava l'esistenza di record correlati in altre tabelle di grandi dimensioni. Ciò ha aumentato il tempo di caricamento da 40 minuti a 9 ore. Dopo la disattivazione dei vincoli, l'inserimento ha richiesto 12 minuti.

---

Storia 3: Un tentativo di inserire un grande file con una singola query (senza batching e senza transazioni) ha portato al riempimento del registro delle transazioni (transaction log full) e all'arresto anomalo del server del database. Dopo essere passati al processamento batch, il problema è scomparso.

---