ADO.NET e inserimento righe multiple

[Nicola Ottomano]

Salve a tutti,
dovendo caricare un file plain-text delimitato (.csv) su SQL Server il
modo migliore è la BULK INSERT.
Quello che mi chiedo è quale sia il modo più veloce per caricare dei
dati, contenuti in memoria, dentro una tabella usando ADO.NET.

Mi spiego. Ho alcune migliaia di record che vengono caricati in un
dataset. Una volta caricati vengono modificati alcuni parametri e
salvati su una seconda tabella.

Il lavoro, fino ad ora, era gestito con dei DTS.

Poichè l'azienda vorrebbe svincolarsi da SQL Server per venire incontro
alle richieste di alcuni grossi clienti, stiamo prendendo in
considerazione il fatto di creare un nostro motore simil-DTS che possa
essere indipendente dal DBMS che si trova sotto.

Girando su google ho trovato le seguenti informazioni per velocizzare
l'INSERT di migliaia (o anche milioni) di righe:

1-Usare ExecuteNonQuery invece di ExecuteQuery
2-Assegnare tutte le righe ad una DataTable e, solo successivamente,
eseguire l'update.
3-Scrivere "a mano" il CommandText invece di lasciarlo fare al
CommandBuilder

Molte di queste informazioni le ho reperite su questa pagina:
http://sqlite.phxsoftware.com/forums/t/134.aspx

Voi avreste altri consigli per ottimizzare la cosa?
Grazie

Nicola

[Matteo Migliore]

> Girando su google ho trovato le seguenti informazioni per velocizzare
> l'INSERT di migliaia (o anche milioni) di righe:
>
> 1-Usare ExecuteNonQuery invece di ExecuteQuery
> 2-Assegnare tutte le righe ad una DataTable e, solo successivamente,
> eseguire l'update.
> 3-Scrivere "a mano" il CommandText invece di lasciarlo fare al
> CommandBuilder
>
> Molte di queste informazioni le ho reperite su questa pagina:
> http://sqlite.phxsoftware.com/forums/t/134.aspx
>
> Voi avreste altri consigli per ottimizzare la cosa?

Il punto 2 non è determinante, usare un DataTable non cambia
le cose, viene comunque eseguita una query alla volta,
e non potrebbe essere altrimenti :-).

Il consiglio è di usare la stored procedure sp_executesql
per eseguire i command, in questo modo SqlServer
può mettere in cache il piano di esecuzione e migliorare
notevolmente le performance. Se il DBMS è diverso
dovresti studiare un sistema che a seconda del
DB target utilizzi il sistema più efficiente, penso
che sia possibile farlo anche in Oracle e altri DBMS.

Ah, ovviamente metti tutto in transazione!

> Grazie

Prego! ;-)

--
Matteo Migliore
Blog: http://blogs.ugidotnet.org/matteomigliore

"Avrai ottenuto la migliore architettura, non
quando non avrai più niente da aggiungere,
ma quando non avrai più niente da eliminare."

[Nicola Ottomano]

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, perchè i dati li ho già in un dataset (o al massimo
in un datatable), quindi dovrei solo fare un "dump" dei dati su una
tabella nel modo più veloce possibile (che su milioni di record
giornalieri fanno sentire la differenza).

Nicola

[Mauro Servienti [MVP]]

Ciao Nicola Ottomano,

You wrote :

aggancia tutte le insert in uno solo command separando il testo di ogni
command con un ";"

> Nicola

.m

--
Mauro Servienti
Microsoft MVP - Visual C# / MCP
http://mvp.support.microsoft.com
http://blogs.ugidotnet.org/topics
whynot [ at ] topics [ dot ] it

[Mauro Servienti [MVP]]

Ciao Matteo Migliore,

You wrote :

> Il consiglio è di usare la stored procedure sp_executesql
> per eseguire i command, in questo modo SqlServer
> può mettere in cache il piano di esecuzione e migliorare
> notevolmente le performance. Se il DBMS è diverso
> dovresti studiare un sistema che a seconda del
> DB target utilizzi il sistema più efficiente, penso
> che sia possibile farlo anche in Oracle e altri DBMS.

il query plan viene calcolato, ottimizzato e messo in cache anche per
un singolo CommandText, il vincolo perchè ciò avvenga è ad esempio che
vengano usati parameters

[Matteo Migliore]

> il query plan viene calcolato, ottimizzato e messo in cache anche per un
> singolo CommandText, il vincolo perchè ciò avvenga è ad esempio che
> vengano usati parameters

Ah, ho sempre letto di usare la SP, non lo sapevo :-)-. In merito all'altro
suggerimento,
se i record sono molti non è possibile mandare un commmand unico, o no?
Non si rischia di andare oltre la lunghezza massima?

[Nicola Ottomano]

Mauro Servienti [MVP] ha scritto:

> Ciao Nicola Ottomano,
>
> You wrote :
>> 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, perchè i dati li ho già in un dataset (o al massimo
>> in un datatable), quindi dovrei solo fare un "dump" dei dati su una
>> tabella nel modo più veloce possibile (che su milioni di record
>> giornalieri fanno sentire la differenza).
>>
>
> aggancia tutte le insert in uno solo command separando il testo di ogni
> command con un ";"

Questo può valere anche su milioni di transazioni? Oppure mi può
convenire spezzettare in blocchi come, ad esempio, si fa con l'opzione
BATCHSIZE di SQLServer?

Nicola

[Andrea Montanari]

salve,

Matteo Migliore wrote:
>> il query plan viene calcolato, ottimizzato e messo in cache anche

>> per un singolo CommandText, il vincolo perchč ciň avvenga č ad

>> esempio che vengano usati parameters
>
>
> Ah, ho sempre letto di usare la SP, non lo sapevo :-)-. In merito
> all'altro suggerimento,

> se i record sono molti non č possibile mandare un commmand unico, o

> no? Non si rischia di andare oltre la lunghezza massima?

e' tendenzialmente consigliabile eseguire un batch di piu' istruzioni di
insert per limitare i roundtrip anche se, ovviamente, i packet trasferiti
sono tendenzialmente gli stessi..
riguardo a SQL Server, sarebbe preferibile utilizzare operazioni bulk al
fine di limitare l'impatto ma, ovviamente, non e' possibile utilizzarle
predisponendo un batch manualmente e non utilizzando le api specifiche che
non mi risulta siano pubbliche.. ad ogni modo, questo diventerebbe pero' un
problema specifico di ogni dbms e richiederebbe la costruzione di un
"driver" per ogni singolo dbms che possa sfruttare le peculiarita' di ogni
singolo strumento..
attenzione che anche l'esecuzione di un "insieme" di comandi insert puo' non
essere supportata (Jet engine)..
tornando a SQL Server, andrebbe anche verificata per ogni batch la
dimensione dello stesso, in quanto SQL Server (e forse anche altri dbms)
utilizza un'impostazione relativa al network packet size che non andrebbe
"sforato", che di default e' di 4096 byets, che assicura un batch size di
268.435.456 bytes.. non sono completamente sicuro che i provider sottostanti
ad esempio ad Ado.Net automaticamente gestiscano il problema..
saluti
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.1 - DbaMgr ver 0.65.1 and further SQL Tools
--------- remove DMO to reply

[Nicola Ottomano]

Innanzitutto voglio ringraziare coloro che hanno risposto al mio quesito.

Ora vengo alla parte dolente.
Ho fatto alcune prove con un DataReader ed un DataTable (i Dataset li ho
scartati a priori perchè troppo "massicci").

I mezzi in gioco sono i seguenti:
-SQL Server 2005 Enterprise
-Due tabelle identiche con 21 colonne (tabella in Input e tabella in output)
-380.000 record circa presenti nella prima tabella

Il caricamento da una tabella all'altra ha dato pressochè gli stessi
risultati sia col DataReader che col DataTable, circa 1 min e 30 sec.
Troppi. :(

Se faccio una bulk insert da file csv ci mette 20 sec al massimo.

Secondo voi il collo di bottiglia potrebbe essere dato dalle varie
INSERT INTO?
In tal caso, come farebbe la Bulk Insert a inserire i dati a quella velcità?

Porebbe essere un problema di lentezza dato da OleDB risolvibile con un
provider specifico?

L'intero minuto e mezzo lo perde qui (DRIN è un OleDbDataReader)

For i = 0 To DRRowCount - 1
DRIN.Read()
For z = 0 To DRIN.FieldCount - 1
Select Case DRIN(z).GetType.Name
Case "Double"
Fields(z).Value = DRIN.GetDouble(z)
Case "String"
Fields(z).Value = DRIN.GetString(z)
Case "DateTime"
Fields(z).Value = DRIN.GetDateTime(z)
Case Else
Fields(z).Value = DRIN.GetValue(z)
End Select
Next
cmd.ExecuteNonQuery()
Next

Prima di eseguire quel codice pongo che:
cmd.CommandText = "INSERT INTO TabTest VALUES (?,?, ...) "

Ovviamente il tutto è racchiuso tra una BeginTransaction e una Commit.

Nicola

[Matteo Migliore]

> Il caricamento da una tabella all'altra ha dato pressochè gli stessi
> risultati sia col DataReader che col DataTable, circa 1 min e 30 sec.
> Troppi. :(

Prova a vedere se con questo va meglio:
http://blogs.ugidotnet.org/matteomigliore/archive/2007/08/09/87595.aspx

[Nicola Ottomano]

Matteo Migliore ha scritto:

> Prova a vedere se con questo va meglio:
> http://blogs.ugidotnet.org/matteomigliore/archive/2007/08/09/87595.aspx
>

Grazie mille ma mi è di poca utilità

In realtà non devo fare una semplice copia ma devo modificare i dati nel
passaggio da una tabella all'altra.
Ora, quindi, sto cercando di trovare la via più veloce per leggere e
scrivere grosse quantità di dati e poi implementerò la logica di programma.
Il tutto, come dicevo qualche post fa, per creare un motore simile a
quello dei DTS\SSIS.

Tornando al problema, col semplice cambio da Oledb a SQLClient sono
riuscito a rubare altri 50" portando l'esecuzione intorno ai 35".
Non male come risultato. A questo punto posso pensare che il vero collo
di bottiglia fosse Oledb, ma qui il problema non si pone più, perchè
potrei gestire i vari wrapper tramite l'AbstractFactory.

Nicola

[Andrea Saltarello]

> Quello che io vorrei capire è come fa SQL Server a fare una bulk insert
> da file.

Lo fa usando l'API di bulk copy, che:
1) AFAIK è utilizzabile solo via ODBC o Ole Db
2) è SQL Server only

Più precisamente, la API di bulk copy è esposta:
1) dal driver ODBC di SQL Server v7 (o superiore) mediante le funzioni bcp_*
2) dal provider OleDb mediante l'interfaccia IFastLoad

Qualche anno addietro ho utilizzato la strategia "1" (funzioni bcp_* via
ODBC) ottenendo una performance media di 1 milione di righe inserite
transazionalmente in 29 secondi: se pensi possa aiutarti, posso pubblicarlo
da qualche parte così lo scarichi. Tieni conto che:
1) il codice è scritto in C perchè l'API BCP richiede di fornire i dati in
ingresso mediante puntatori alle zone di memoria che li contengono
2) il codice è SQL Server only.

HTH,

--
.A
GUISA - http://www.guisa.org
UGIdotNET - http://www.ugidotnet.org
Read my blog at: http://blogs.ugidotnet.org/pape

[Nicola Ottomano]

Andrea Saltarello ha scritto:
[CUT]

> Qualche anno addietro ho utilizzato la strategia "1" (funzioni bcp_* via
> ODBC) ottenendo una performance media di 1 milione di righe inserite
> transazionalmente in 29 secondi: se pensi possa aiutarti, posso
> pubblicarlo da qualche parte così lo scarichi. Tieni conto che:
> 1) il codice è scritto in C perchè l'API BCP richiede di fornire i dati
> in ingresso mediante puntatori alle zone di memoria che li contengono
> 2) il codice è SQL Server only.

Ciao Andrea,
è da molto che non ci si sente :)

Grazie mille per la proposta, ma a questo punto mi accontenterei di un
leggero calo di performance ma non posso assolutamente legarmi a SQL
Server, anche perchè è il motivo per cui stiamo migrando da DTS\SSIS a
qualcosa di platform-indipendent.
Purtroppo alcuni grossi clienti stanno migrando ad Oracle e non vogliono
installare SQL Server solo per effettuare una conversione dei dati.
Noi, d'altra parte, dobbiamo poter lavorare indipendentemente con Oracle
e SQL Server.
E' per questo che sarei disposto ad avere un calo di performance del
30%, a patto di essere svincolato da una sola piattaforma.

Al momento il caricamento di quella famosa tabella da 380.000 record (e
21 campi) mi porta via 35" che paragonati ai 25" di una Bulk Insert non
sono male.
I dolori verranno quando dovrò caricare, modificare e salvare tabelle da
3/4 GB... là devo trovare qualche modo di "spezzettare" il carico di
lavoro i blocchi più piccoli.

Nicola