Importare un file di testo in SQL Server (Ce ed Express)

Milk Data Logging 

In azienda ho una bascula (per pesare il latte munto in stalla) che ha una porta seriale. Per controllare la produzione di ciascuna munta e per registrare le consegne alla cooperativa di raccolta ho fatto un paio di anni fa un programmino con Borland Delphi, al volo come al solito, che riceve sulla seriale la lettura in continuo del peso. Le variazioni di peso vengono registrate su un file di testo (essendo una versione 0.0.0.0.0.0...1). Una volta verificato il funzionamento avrei dovuto modificare il programma per farlo girare come servizio in background, registrando i dati in un database.

Indovinate un po', sono passati poco più di due anni e il programmino sta ancora girando, con il file di testo che ha superato il milione di righe.

Ora, tralasciando il fatto che con questo post mi sono definitivamente sputtanato, viene la parte interessante.

Come importare un file di testo in una tabella di database, o meglio, come farlo in modo efficiente ? E poi, c'è differenza tra usare SQL Server Express o SQL Server Compact Edition ?

Dopo aver googlato un po' per documentarmi, ho deciso di fare alcune prove, partendo dal classico comando di INSERT sino a SqlBulkCopy. I diversi metodi usati sono:

  1. Loop di comandi SQL INSERT;
  2. Preparazione di un comando SQL INSERT con parametro, e poi loop con variazione del valore del parametro ed esecuzione del comando;
  3. Uso di una transazione al cui interno viene eseguito l'inserimento dei dati come al punto 2;
  4. Uso di un ResultSet ottenuto da un comando TableDirect con insert di UpdatableRecord al posto di SQL INSERT;

Inoltre, solo per SQL Server Express, perchè SqlBulkCopy non è disponibile per SQL Server Compact Edition:

  1. Uso del comando SqlBulkCopy () a cui viene passata una DataTable contenente le righe lette dal file di testo (Step 1 e 2);
  2. Uso del comando SqlBulkCopy () a cui viene passato (poichè implementa l'interfaccia iDataReader) direttamente l'OdbcDataReader del file di testo;

E questi i primi risultati (mancano i metodi BulkCopy, al momento non ancora implementato), con un file di testo di 1.238.000 righe (47 caratteri ciascuna):

Using SQL Server Compact Edition
====================================================================
Import from text file (modify cmd each iteration)....: 00.11.29.9476

Deleting imported table..............................: 00.00.43.3449

Import from text file (with param and cmd prepare)...: 00.06.05.5234

Deleting imported table..............................: 00.00.43.6101

Transactional import from text file..................: 00.06.23.0232

Transactional deleting imported table................: 00.00.43.2467

Direct table import from text file (ResultSet).......: 00.06.22.4188

Deleting imported table..............................: 00.00.43.3742

Using SQL Server Express Edition
====================================================================

Import from text file (modify cmd each iteration)....: 00.37.19.1644

Deleting imported table..............................: 00.01.49.0421

Import from text file (with param and cmd prepare)...: 00.32.54.1606

Deleting imported table..............................: 00.00.40.2665

Transactional import from text file..................: 00.08.30.1257

Transactional deleting imported table................: 00.00.17.2543

La prima cosa che balza agli occhi è che in SQLCe non sembra esservi differenza tra i vari metodi (escludendo il primo del tutto inefficiente), mentre per SQL Express vi è un netto peggioramento se non utiliziamo una transazione che racchiuda l'attività di import e di cancellazione.

Facendo alcune prove, interrompendo ed abortendo col debugger le attività di import del metodo 2 (with param and cmd prepare) con SQLCe mi sono accorto di una cosa molto curiosa: nessun record viene inserito nella tabella. In pratica è come se internamente SQLCe in automatico crei una transaction che non viene commit-ata se non viene effettata la corretta chiusura della connection, perdendo tutte le modifiche.

Mi piacerebbe sapere se è vero e se qualcuno ne sa qualcosa al riguardo.

And the winner is...

  • SQL Server Compact Edition
    Import from text file (with param and cmd prepare):  6' 05" 523,4 millisecondi
  • SQL Server Express Edition
    Transactional import from text file (with param and cmd prepare):   8' 30" 125,7 millisecondi

Appena posso finisco SqlBulkCopy e aggiorno il post.

posted @ domenica 23 dicembre 2007 12:25

Print

Comments on this entry:

# re: Importare un file di testo in SQL Server (Ce ed Express)

Left by Lorenzo Barbieri at 23/12/2007 12:38
Gravatar
Io valutere anche BCP da riga di comando: http://msdn2.microsoft.com/en-us/library/ms162802.aspx

Non ho mai provato se c'è anche per la Express.

# re: Importare un file di testo in SQL Server (Ce ed Express)

Left by Lorenzo Barbieri at 23/12/2007 13:24
Gravatar
valuterei...

# SqlBulkCopy at work

Left by Il Blog di Nicol at 28/12/2007 12:05
Gravatar
SqlBulkCopy at work
Comments have been closed on this topic.
«ottobre»
domlunmarmergiovensab
262728293012
3456789
10111213141516
17181920212223
24252627282930
31123456