Guardate un po' queste linee di codice in C# (che
chiameremo CodiceSemplice ):
daLettura = new System.Data.SqlClient.SqlDataAdapter("SELECT * FROM TabellaGigante", cnnLocale);
DataSet ds = new DataSet();
DataTable dt = new DataTable();
ds.Tables.Add(dt);
this.txtInizio.Text = DateTime.Now.ToString();
cnnLocale.Open();
daLettura.Fill(ds);
cnnLocale.Close();
this.txtFine.Text = DateTime.Now.ToString();
TimeSpan ts = Convert.ToDateTime(this.txtFine.Text).Subtract(Convert.ToDateTime(this.txtInizio.Text));
string msg = String.Format("Per caricare una tabella di {0} records ci ho messo {1} minuti!", ds.Tables[0].Rows.Count.ToString(), ts.ToString());
this.textBox1.Text = msg;
Ho scritto una piccola applicazione Windows Forms per testare per bene
il caricamento di un DataSet da un sorgente dati come SQL Server. Ho letto
infatti alcune cose interessanti sulla property
EnforceConstraints del DataSet e sui metodi
BeginLoadData e EndLoadData
della classe DataTable. Ho voluto provare come si comportano, e ho ottenuto qualche
risultato.
Eseguiamo il codice, quanto tempo ci
metto?
Il codice sopra
dichiara un SqlDataAdapter, un DataSet e una DataTable. In breve, popolo un DataSet
con una sola DataTable usando un SqlDataAdapter ("SELECT * FROM
TabellaGigante"). La tabella è in un database SQL Server su localhost ed in
questo momento contiene 585.285 records. Nel codice, uso un TimeSpan per vedere quanto
tempo ci metto a: aprire la connection, riempire il dataset, chiudere la
connection.
Sul mio PC qua al lavoro, questo tempo è pari a 27
secondi
.
Cosa possiamo fare per ottimizzare i tempi di
caricamento?
Nel codice sopra non faccio nulla di particolare. .NET deduce automaticamente la struttura della tabella al momento
della chiamata al metodo .Fill del SqlDataAdapter. Questo
ovviamente viene fatto a run-time, quindi l'esecuzione risulta essere più lenta,
anche se impercettibilmente. Difatti, potremmo dire noi la struttura della
DataTable (definendo DataColumn, PrimaryKey e quant'altro) e definire che:
daLettura.MissingSchemaAction = MissingSchemaAction.Error;
ovvero: se durante il Fill ti accorgi che la struttura della DataTable
in memoria e la struttura della tabella sul database sono diverse, sollevami
un'exception (al contrario di quanto accade di default, dove MissingSchemaAction
=
Add). Oppure, potremmo leggere lo schema da un file XML precedentemente
prodotto dall'accoppiata FillSchema +
WriteXMLSchema. Le possibilità sono diverse.
Poi, ho letto alcune considerazioni su EnabledConstraints
(property del DataSet) e
BeginLoadData/EndLoadData (metodi
della DataTable). Questi consentono di ottimizzare i caricamenti di
un DataSet, perchè disabilitano gli eventi e le exceptions che normalmente .NET deve gestire.
Quindi, in pratica, prima di chiamare la Fill, impostare EnabledConstraints =
false e chiamare BeginLoadData per ogni DataTable che sto per andare a caricare.
Il codice è diventato così (questo lo chiameremo
CodiceOttimizzato
):
daLettura = new System.Data.SqlClient.SqlDataAdapter("SELECT * FROM TabellaGigante", cnnLocale);
DataSet ds = new DataSet();
DataTable dt = new DataTable("TabellaGigante");
daLettura.FillSchema(dt, SchemaType.Mapped);
ds.Tables.Add(dt);
daLettura.MissingSchemaAction = MissingSchemaAction.Error;
ds.EnforceConstraints = false;
foreach(DataTable t in ds.Tables)
t.BeginLoadData();
this.txtInizio.Text = DateTime.Now.ToString();
cnnLocale.Open();
daLettura.Fill(ds, "TabellaGigante");
cnnLocale.Close();
this.txtFine.Text = DateTime.Now.ToString();
TimeSpan ts = Convert.ToDateTime(this.txtFine.Text).Subtract(Convert.ToDateTime(this.txtInizio.Text));
foreach(DataTable t in ds.Tables)
t.EndLoadData();
ds.EnforceConstraints = true;
string msg = String.Format("Per caricare una tabella di {0} records ci ho messo {1} minuti!", ds.Tables[0].Rows.Count.ToString(), ts.ToString());
this.textBox1.Text = msg;
Il tempo di caricamento adesso è di 48 secondi contro i
27 di prima. 
Perchè?
Proviamo a violare un constraint della nostra
tabella
Voglio far notare che i tempi che prendo riguardano
solamente l'esecuzione del metodo Fill. Il paragrafo precedente
comunque pone un interrogativo a cui non ho dato ancora risposta. Per me, non è
conveniente chiamare BeginLoadData ed EndLoadData. Per me, non è conveniente
nemmeno impostare EnabledConstraints = false. A meno che ...... a meno che la
nostra DataTable "TabellaGigante" non abbia qualcosa che non va, qualcosa che
viola almeno un contraint. Adesso vi spiego meglio.
Ho aggiunto un campo chiamato vuoto in TabellaGigante, di
tipo varchar(255), che ammette NULL.
Poi ho eseguito questo SQL:
UPDATE TabellaGigante SET vuoto = NULL
Tutti i 585.285 della nostra tabella contengono il valore NULL dentro il
campo vuoto. Ok. Adesso faccio una cosa molto semplice: prendo
CodiceSemplice (per ora il più performante) e lo modifico al
volo aggiungendo queste linee di codice:
daLettura.FillSchema(dt, SchemaType.Mapped);
dt.Columns["vuoto"].AllowDBNull = false;
Cioè: prima di chiamare la Fill, definisco lo schema
della DataTable (deducendolo tramite SqlDataAdapter), ma un attimo
dopo ne modifico via codice un constraint. Dico che il campo
vuoto NON può ammettere NULL. Mi aspetto che la chiamata a
Fill mi ritorni un'exception di qualche tipo. Effettivamente
accade proprio così, ma solo dopo aver aspettato ben 5
minuti e 40 secondi. GULP!
Ho fatto la stessa cosa anche sul blocco che ho chiamato
CodiceOttimizzato: anche qui ho cambiato il constraint.
Risultato? La chiamata a Fill impiega 50 secondi
, anche se (attenzione!!) tutto il codice ci ha messo comunque 5 minuti ed oltre. La
Fill è veloce, solo che poi all'atto della riattivazione di tutti
i constraints, .NET deve scandire la tabella (ed è proprio qui
che l'exception viene sollevata, al contrario di prima).
Risultati?
Vediamo se afferro il nocciolo della questione.
La soluzione in assoluto più veloce è non usare un bel
niente, lasciare che .NET faccia tutto da solo. Se però per qualche motivo la tabella contiene dati
non validi (come magari può accedere in produzione), ecco che la velocità viene
distrutta e le prestazioni peggiorano orribilmente. A questo
punto, BeginLoadData/EndLoadData in combinazione con EnabledConstraints
conviene. Attenzione però a riattivare i constraints: .NET ci mette davvero una
vita a scandire la tabella; nell'esempio sopra, tutti i records hanno
vuoto = NULL: io ho provato a mettere vuoto =
"abcde", e a mettere in un solo record NULL. Le cose non cambiano
di molto.
Ultima nota: prima di chiamare
ds.EnabledConstraints = true, la property HasErrors del
DataSet vale false. Dopo la
chiamata, giustamente, HasErrors è
true.