Questa domanda:
Quello che io vorrei capire è come fa SQL Server a fare una bulk insert
da file. Se riuscissi a replicare la cosa, potrei fare una sorta di bulk
insert da memoria
apparsa in un messaggio sui NG Microsoft, unitamente ad un post di Davide, mi ha riportato con la mente quando, qualche anno addietro, mi sono trovato nella condizione di chiedermi: "Quale è il modo più veloce di effettuare inserimenti massivi di dati con SQL Server?"
Mi posi questa domanda poichè stavo lavorando ad un sistema di gestione di cocorsi a premi, ed il cliente aveva richiesto la possibilità di generare fino a 100.000.000 (cento milioni!) di coupon per ogni campagna, quindi "almeno" cento milioni di righe a "botta", transazionalmente e in tempi "umani". Poichè mi sembra che questa sia, tutto sommato, una domanda ricorrente, ho pensato di scrivere questo post. Innanzitutto, ecco alcune doverose precisazioni:
- Poichè non ho ancora avuto modo di studiare seramente SQL Server 2008 (in fondo sono un umile dev), non posso "assicurare" che la nuova release del prodotto non disponga di una funzionalità analoga migliore (es: SSIS)
- Il livello di astrazione della soluzione adottata è *basso*, e di conseguenza decisamente poco "comodo"
Adottai quindi una soluzione basata su una caratteristica non molto nota di SQL Server che, in queste condizioni:
- La tabella è vuota
- La tabella non ha una chiave primaria
- L'inserimento avviene in modalità BULK
lavora in modalità "non logged", e quindi "risparmia" molto lavoro (e, conseguentemente, molto tempo). Si trattava, quindi, di poter utilizzare l'API di bulk copy partendo da dati in memoria, e non dal "classico" file su disco. A partire dalla versione 7.0, il driver ODBC di SQL Server permette di effettuare quanto descritto sopra utilizzando le funzioni bcp_*; per comodità, io utilizzati il compilatore di C++ incluso in Visual Studio 6 (preistoria, lo so <g>)
In pratica:
- Includere odbcss.h
- invocare bcp_init specificando i flag necessari ad utilizzare la memoria quale flusso di origine dei dati
- utilizzare bcp_bind per ogni colonna della tabella che vogliamo valorizzare, specificando il puntatore della zona di memoria che contiene i dati che devono essere utilizzati per valorizzare la colonna
- implementare un ciclo che valorizza le succitate zone di memoria per ogni riga da inserire nella tabella, e invocare bcp_sendrow per ogni iterazione
- invocare bcp_batch ogni "tot" iterazioni ("tot" si ricava facendo tuning sui vostri server, non esistono "numeri magici")
- invocare bcp_done per committare tutte le righe
Nel mio caso, implementare batch paralleli usando il multi-threading non aveva dato esiti positivi perchè un singolo thread era in grado di saturare la banda disponibile verso il controller, ma non escludo che su "macchine" più moderne questa strategia possa dare qualche buon frutto (il server a mia disposizione era decisamente "scarso"). Questa soluzione (che riutilizzammo anche per un progetto gestito da Microsoft Consulting) fu argomento di un vecchissimo (maggio 2000!) workshop UGISS, quindi nella sezione downloads potrebbe (parola di lupetto, non ne ho idea) essere tutt'ora disponibile la combo "slide+codice demo". Ove così non fosse e inetressasse a qualcuno, fatemi un fischio e ripubblicherò il materiale sul sito aziendale.
posted @ giovedì 13 marzo 2008 13:06