🟡 Il “colosso”: domare una tabella da 600 milioni di righe

🔵 Introduzione

Questo post l’ho scritto almeno due mesi fa. L’ho scritto prima ancora di scriverlo. Mi spiego, potrei essere frainteso. Un’attività progettuale prevedeva di convertire dati tabellari sulla scorta di una matrice che avrebbe immagazzinato il classico valore vecchio e valore nuovo (chi mastica il nostro mestiere, almeno una volta nella vita si è dovuto imbattere in questo tema o almeno sa di che si tratta).

Chiarisco il tema: necessità di convertire i dati partendo da una matrice. Cosa si converte, solitamente, in un database? In questo caso i codici identificativi dei clienti, la numerazione dei rapporti, le categorie dei conti correnti, le causali dei movimenti, il codice Abi della Banca.

Tutto chiaro! Così mi è parso. Sono stato fiducioso dall’inizio e fino a questo momento in cui sto scrivendo. Ma c’è sempre, diciamolo, quel qualcosa che rende le attività mai perfettamente lineari come si vorrebbe, come ci si attenderebbe.

Ho pensato al mio “colosso” (posso chiamarlo così perché si è da subito rivelato tale), all’entità, alla tabella stessa che tra i colleghi della mia azienda è sempre stata come l’ultima sfida, il “mostro” finale da sconfiggere.

L’ho voluto battezzare quindi così: “colosso”.

Come convertire allora più di 600 milioni di dati immagazzinati in una sola tabella? Se non volessimo chiamarlo “colosso”, potremmo provare ad essere più professionali e definirla come una VLT (Very Large Table) ma, per quanto io mi ritenga molto professionale, non fa al caso mio.

🟣 Valutiamo la situazione iniziale

Qualche dettaglio per poter poi seguire meglio l’evoluzione:

·        Tabella potenzialmente partizionabile; nota dolente: è stata manualmente partizionata per un solo breve periodo e poi niente più

·        Presenza di 15 indici di cui la metà costruiti nel tempo con il solo scopo di adempiere ai suggerimenti di SQL Server (avevo dimenticato, qui giriamo su questo DBMS)

·        Impossibilità di scaricare il contenuto su file system, causa mancanza di spazio (non infierite, per varie ragioni non era possibile chiedere l’ampliamento)

·        Impossibilità di agire solamente con semplici istruzioni di UPDATE massive, causa presenza della molteplicità di indici e causa anche il volume della matrice che, di fatto, richiedeva l’aggiornamento di tutte, e dico tutte, le righe del “colosso”

Ho via via bocciato, durante il percorso, una serie di teorie che sapevo aprioristicamente non mi avrebbero portato da nessuna parte e, da subito, ho intrapreso la mia strada, chiaramente attingendo a tutte le risorse note (esperienza) e non note (web).

Con l’unica possibilità rimasta, quindi di lavorare direttamente in tabella, ho approfondito ogni scenario, nel limite della mia conoscenza, cercando però di esplorare ogni ipotesi.

Ogni istruzione di UPDATE apporta un costo in termini di tempo. Quindi dovevo valutare se considerare sostenibile un’unica UPDATE o tanti aggiornamenti parziali o per singola riga.

Mi sono convinto ad affrontare ogni soluzione in modo prototipale, quindi prima utilizzando solamente una piccola quantità di dati appoggiati su una tabella secondaria utilizzata ai miei scopi di indagine. Poi ho aumentato i dati cercando di comprendere la sostenibilità delle elaborazioni.

🔴 Applicazione strategie di aggiornamento

Ecco dunque arrivare al modo in cui la tabella è stata letta, aggiornata, coccolata, sistemata, rimessa col vestito nuovo e con i dati freschi di conversione.

Partendo dalla sensazione che la sola scansione di una tabella così corposa avrebbe potuto mettermi in crisi, ho valutato di aprire un cursore senza alcun ordinamento; quindi, esattamente nel modo in cui i dati sono immagazzinati. Certo vi mancano informazioni (ricordo nuovamente che il “colosso” ha una storia per me molto profonda; l’ho visto crescere e nutrirsi anno dopo anno di moltissimi dati), ma mi era chiaro che la sola apertura del cursore con un qualsiasi ordinamento avrebbe potuto destabilizzare il sistema rendendo onerosa la lettura.

In effetti, non era necessario l’ordine di estrazione perché ho optato per un aggiornamento riga per riga (o quasi, ma ora cerco di chiarire il tutto). Ho creato una procedura SQL con questi criteri:

·        Memorizzazione della matrice di conversione; per quanti fossero i dati, il peso di reggere in memoria qualche mega era sempre minore che accedere alla matrice stessa (SELECT) ad ogni riga

·        Lettura di tutta la tabella senza alcun ordinamento

·        Per ogni riga, valutazione (IF) se i singoli campi fossero effettivamente oggetto di conversione o meno (a breve capirete meglio); ad esempio, valutando se il codice del cliente fosse uno tra quelli previsti nella matrice di conversione

·        Se almeno un campo della riga era da convertire, esecuzione istruzione di UPDATE

·        Ad ogni n righe, esecuzione di COMMIT

·        Per poter tenere monitorata l’esecuzione della procedura, ad ogni n righe veniva data evidenza dello stato di avanzamento della lettura

Ho già detto però che sulla tabella gravano 15 indici. Ci possiamo immaginare abbastanza facilmente dei tempi di aggiornamento della singola riga per aggiornare i singoli indici. Ma, appunto, davvero servivano? Chiaro, no. Vi ho anticipato e servito una risposta piuttosto scontata.

Passetto indietro, quindi. Prima dell’esecuzione della procedura, ho provveduto a disabilitare tutti gli indici ad eccezione di uno. Quale? Quello che definisce la chiave primaria della tabella, ovvero quello che rispecchia esattamente la condizione di aggiornamento dell’istruzione di UPDATE. Anche qui, nulla di sorprendente. Certo, ora è banale, ora che è nuovamente servita su un piatto bella e confezionata.

D’altra parte, il prototipo che avevo introdotto aveva già fatto emergere, alla prima prova, il costo eccessivo per aggiornare ogni riga. Il sistema, infatti, per ogni istruzione di UPDATE, avrebbe dovuto scansionare tutta la tabella per trovare la corretta riga da aggiornare.

Facciamo ancora un passetto in avanti. La mole di dati era davvero consistente e ho ragionato che solo le logiche più corpose dovevano passare dall’aggiornamento tramite procedura SQL. Quindi, all’interno ho inserito solo le logiche più complesse ma anche, di contro, quelle che avrebbero ottenuto l’aggiornamento di un minore numero di righe. Per intenderci, su 600 milioni di righe, l’esecuzione della procedura doveva aggiornare “solamente” 5 milioni di righe. Ecco dunque aggiornata la prima parte di dati.

Ora arriva il momento di poter effettuare un aggiornamento massivo di tutte le righe. Perché? Perché un campo conteneva il valore del codice Abi della Banca che era oggetto di conversione. La scelta quindi di optare per un aggiornamento massivo è stata piuttosto logica. Meglio attendere magari una o due ore per aggiornare tutte le righe in un colpo solo, piuttosto che effettuare l’aggiornamento tramite procedura che avrebbe, nelle mie attese, appesantito l’elaborazione andando perciò ad aggiornare ogni singola riga.

Anche qui un piccolo passo indietro. A che serviva mantenere abilitato anche quell’ultimo indice che copriva le colonne della chiave primaria? Infatti, è stato disabilitato prima di effettuare l’aggiornamento massivo tramite UPDATE.

Ora siamo quasi alla fine. Una volta che anche l’istruzione di UPDATE è terminata, si dovevano nuovamente riabilitare gli indici. Ma questa è stata finalmente la conclusione.

⚪ Conclusione

Come concludere ora se non con estrema soddisfazione! E’ stata una faticata, eccome, ma lungo tutto il percorso ho compreso cose che prima non avevo valutato. Ho pescato da quel vecchio detto di “mangiare l’elefante con il cucchiaino”, cercare di smembrare le cose, capire le potenzialità degli strumenti a disposizione, confrontarsi con i colleghi e approfondire in rete.

Non ce l’avrei fatta con le mie sole competenze se mi avessero chiuso da solo in una stanza, senza alcuna connessione a internet, dandomi semplicemente il compito di convertire il “colosso”.

Sono contento di avere creato una procedura molto competitiva e solida, considerando tutto quello che c’era da tenere in evidenza.

Ho ampliato il bagaglio di conoscenza e così, di conseguenza, gli strumenti che potrò mettere a disposizione per me e per i colleghi.

Non c’è bisogno di conoscere ogni sfaccettatura dei linguaggi e, in questo caso, di SQL Server. Più importante è la capacità di adattarsi alle esigenze che ogni volta, in ambito informatico, sono diverse dalle volte precedenti.

Sono più che convinto di avere introdotto ogni strategia possibile, considerando i limiti in cui ho dovuto muovermi. Ho preso gli strumenti del mio mestiere e ho preso sottobraccio il mio “colosso”.

Qualche curiosità? Per quanto abbia cercato di ottimizzare in ogni modo le prestazioni, tutto il processo è durato all’incirca 24 ore:

·        Disabilitazione degli indici ad eccezione di quello relativo ai campi della chiave primaria

·        Esecuzione della procedura

·        Disabilitazione dell’ultimo indice ancora attivo

·        Esecuzione dell’istruzione UPDATE per l’aggiornamento massivo

·        Ricostruzione degli indici

Mettiamoci poi, qua e là, la compattazione del file di log che stava anch’esso assumendo dimensioni notevoli, e il gioco è fatto.

Ora sono pronto per la prossima sfida, anche se per un po’ vorrei non fosse necessario affrontare un’ulteriore simile battaglia.

Voi vi sareste tirati indietro?