Supermassive Bulk Load

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 @ Thursday, March 13, 2008 11:06 AM

Print

Comments on this entry:

# re: Supermassive Bulk Load

Left by Marco Russo at 3/14/2008 12:27 AM
Gravatar
Andrea,
su ADO.NET 2.0 c'è la stessa funzionalità di ODBC, in una classe che si chiama SqlBulkCopy (disponibile solo nel data provider di SQL Server).
Abbiamo usato questa funzionalità su SqlBulkTool (http://www.sqlbi.eu/sqlbulktool.aspx) che è disponibile gratuitamente, liberamente, con tutti i sorgenti.
Su SSIS ci sono ovviamente funzioni analoghe (il Fast Load del driver OLE DB e il SqlDestination adapter).
Una cosa interessante è un problema che esiste quando la tabella ha un indice clustered. Su SQL 2005 si può ovviare così: http://sqlblog.com/blogs/alberto_ferrari/archive/2007/07/02/bulk-insert-of-sorted-data.aspx
Su SQL 2008 dovrebbe essere risolto (alla SQL Server Conference 2007 abbiamo assistito alla segnalazione "in diretta" al team di sviluppo da chi ne faceva parte, Stefano Stefani).
Anyway, ODBC ha ancora il vantaggio di essere onnipresente, con ADO.NET 2.0 devi avere il .NET Framework 2.0 a bordo. Comunque sempre meglio che obbligare qualcuno a installare il motore di SSIS... :)

Marco

# re: Supermassive Bulk Load

Left by Gian Maria at 3/14/2008 8:42 AM
Gravatar
Confermo anche io ho spesso utilizzato il SqlBulkCopy e funziona decisamente bene.
alk.
Comments have been closed on this topic.
«January»
SunMonTueWedThuFriSat
2930311234
567891011
12131415161718
19202122232425
2627282930311
2345678