SQL SERVER - I DATABASE GRANDI SONO SEMPRE LENTI?

Poniamoci una domanda, diamoci una risposta: i database di grandi dimensioni sono sempre più lenti? Oppure esiste delle motivazioni alla base che possono essere risolte?

Fonte: Greglow Blog [link]

Un problema che, di solito, arriva dopo

Durante il design di una applicazione custom, specialmente nell'ottica di uno sviluppo agile, si tende a concentrarsi sul refactoring del codice, trascurando il refactoring del DB. La differenza fondamentale è che, quando una tabella raggiunge importanti dimensioni, apportare delle modifiche può consistere in un processo molto lento.

Foto: Biblioteca Nazionale Cinese [wikipedia] 

Per fare un esempio: immaginatevi una biblioteca comunale senza un servizio efficiente di indicizzazione dei libri, se fosse chiesto un libro, per trovarlo, dovrebbero essere passati in rassegna tutti gli scaffali prima di trovarlo. Analogamente una grande tabella, senza un buon indice, è un secchio pieno di record difficili da cercare.

Strategie di archiviazione

Una strategia può essere quella di partizionare le tabelle di dati storici, su base annuale o addirittura mensile, in modo tale da avere tabelle di piccole dimensioni. Il costo di elaborazione viene scaricato lato client rendendo necessario una aggregazione dei dati nel caso in cui una query interroghi un periodo di tempo che coinvolge più tabelle.

Esempio: interrogazione dati su più tabelle

Strategie di indicizzazione

La strategia migliore è l'indicizzazione delle tabelle, anche se più complessa perché richiede una progettazione del DB accurata e professionale. Indicizzare una tabella significa fornire al software RDBMS una strategia veloce per estrarre dati senza bisogno di cercarli.

Immaginiamo di avere una tabella di archiviazione di dati storici di andamento di una sonda di temperatura, sarà composta da due colonne: tempo (timestamp) e valore (value). Comprensibilmente, i criteri con cui vengono estratti i dati da questa tabella sono su base temporale (il valore della sonda dal giorno X al giorno Y) quindi la tabella dovrà essere indicizzata secondo la colonna di tempo (timestamp).

Esempio: strategia di indicizzazione

Immaginando di avere la stessa tabella di archiviazione, ma relativa a più sonde, avremmo le seguenti colonne: tempo (timestamp), sonda (tag) e valore (value); le ricerce in questo caso saranno legate non solo ad un criterio temporale (timestamp) ma anche di sonda (tag), quindi la tabella dovrà essere indicizzata secondo queste due colonne.

A basso livello, creare un indice di una tabella significa inventarsi un indirizzo univoco che punta ad una ed una sola riga della tabella, per questo motivo l'interrogazione è immediata!

Svantaggio 1: progettazione

Esistono degli svantaggi quando si utilizzano gli indici: il primo svantaggio è che devono essere progettati bene. La progettazione di un indice deve essere fatta con criterio, non possono essere impostate tutte le colonne come indice altrimenti non si ha alcun beneficio. 

Consiglio:

inserire un indice in base alle colonne su cui si effettua una clausola WHERE più di frequente.

Svantaggio 2: retroattività

Immaginiamo di dover ottimizzare una tabella. Dopo aver letto questo articolo, conosciamo i benefici degli indici e decidiamo di implementarne uno, abbiamo anche individuato correttamente le colonne su cui costruirlo quindi non ci resta che crearlo, il problema è che per creare un indice deve essere speso del tempo per calcolarlo! Ogni riga della nostra tabella dovrà essere elaborata per calcolare l'indice di riga quindi se la tabella è fatta di 10.000 righe dovranno essere effettuate altrettante elaborazioni.

Consiglio:

creare il prima possibile un indice, prima che sia troppo tardi

Svantaggio 3: ricostruzione

Una volta costruito un indice, il lavoro non è concluso! Una tabella è soggetta a inserimenti, modifiche e cancellazioni, queste operazioni potrebbero incidere sugli indici e, dopo un po' di tempo, diminuire l'efficienza della interrogazione (l'inefficienza di un indice è misurata dalla "frammentazione"). Per ripristinare l'efficienza di un indice è necessario eseguire una operazione di "ricostruzione" che, analogamente alla costruzione, ha un costo in termini di elaborazione proporzionati alle dimensioni della tabella.

Consiglio:

programmare la ricostruzione degli indici a periodi regolari,

meglio in orari in cui il DB non è in uso 

(esempio: ogni primo Lunedì del mese alle 03:00 di notte)