Articoli DevLeap

SQL Everywhere (aka SQL Mobile): Accesso ai dati Parte 1

Articolo da me pubblicato su Computer Programming n. 159. Riporto as was :-)

Il 6 Aprile Microsoft ha annunciato l’uscita di un nuovo prodotto denominato SQL Everywhere: come dice il termine stesso il prodotto gira su qualunque piattaforma Microsoft. In realtà SQL Everywhere non è nient’altro che l’attuale SQL 2005 Mobile senza la limitazione che ad oggi ne impone l’utilizzo solo su piattaforma Windows CE e Windows XP Tablet Edition (ricordo che i nuovi Ultra-Mobile PC sono comunque basati su Windows XP Tablet Edition).

In pratica Microsoft ha deciso di togliere la limitazione (di cui abbiamo parlato più volte sui forum di ThinkMobile.it) sulla licenza che ad oggi consente l’utilizzo in produzione di SQL 2005 Mobile su Windows CE e Windows XP Tablet Edition.

Non è quindi un nuovo prodotto, ma bensì un nuovo nome per un prodotto esistente.

Come abbiamo avuto modo di introdurre nell’articolo precedente sui meccanismi di replica di SQL 2005 Mobile, SQL Everywhere (che per gli amici resterà sempre SQLCE, il primo nome di tale prodotto uscito nel 1998) è la versione “ridotta” (striminzita sarebbe il termine più corretto) di SQL Server nato per la piattaforma Windows CE. L’accesso ai dati dal .NET Framework o .NET Compact Framework è pressoché identico nella forma all’utilizzo di Sql Server: il provider nativo System.Data.SqlServerCe implementa, infatti, le stesse interfacce del fratello maggiore System.Data.SqlClient.

La libreria System.Data.SqlServerCe offre però due meccanismi di accesso al database non presenti nel fratello maggiore: SqlCeResultSet e Base Table Cursor. Obiettivo dell’articolo è capire il ruolo di questi due signori e soprattutto le caratteristiche legate alle performance e all’occupazione di memoria.

Il primo meccanismo è spesso pubblicizzato in conferenze ed eventi come una delle novità della versione 2005 di SQLCE, mentre il secondo è pressoché ignorato pur essendo il metodo di accesso più veloce ad una tabella sia per recuperare l’intero set di record, che per effettuare filtri sui dati in base ad un indice, sia per recuperare il singolo record.

Una prefazione

Da sempre nel mondo .NET esiste la diatriba fra DataReader e DataSet e purtroppo in molti forum, riviste, ma soprattutto negli esempi, si predilige l’utilizzo di quest’ultimo. Potremmo scusare chi abusa del DataSet affermando che un DataSet (o una DataTable) può essere “bindata” direttamente ai controlli dell’interfaccia utente Windows (diverso è il ragionamento su applicazioni ASP.NET), mentre un DataReader non lo è e quindi occorre valorizzare i valori dei controlli da codice, così come dobbiamo preoccuparci di rileggere i valori dei controlli per passargli agli statement di update.

Un esempio di codice è meglio di mille parole: supponiamo di voler estrarre tutti i record della tabella tabArticoli e legare IdArticolo e Descrizione ad una ListBox. Questo il codice che utilizza un DataSet da una funzione del layer User Interface. Il codice calcola i tempi di esecuzione con Environment.TickCount, un meccanismo non accuratissimo che restituisce il numero di millisecondi da quando il sistema è stato avviato.

public static void AllSelectStarDS(ComboBox lstArticoli, Label lblTime)

{

    int start = Environment.TickCount;

    lstArticoli.DataSource = null;

    lstArticoli.Items.Clear();

    SqlCeConnection conn = new SqlCeConnection();

    conn.ConnectionString = “…”;
    SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM tabArticoli",
                           conn);

    SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);

    DataSet ds = new DataSet();

    try

    {

        try

        {

            da.Fill(ds, "Articoli");

            lstArticoli.DisplayMember = "ArticoloDex";

            lstArticoli.ValueMember = "IdArticolo";

            lstArticoli.DataSource = ds.Tables["Articoli"].DefaultView;

        }

        finally

        {

            conn.Dispose();

            cmd.Dispose();

            da.Dispose();

        }

    }

    catch (SqlCeException ex)

    {

        MessageBox.Show("Errore Lettore");

    }

    int end = Environment.TickCount;

    lblTime.Text = (end - start).ToString();

}

Questo codice, nell’esempio che stiamo facendo e che è disponibile come zip sotto http:// www.thinkmobile.it/files scegliendo SQL 2005 Mobile Performance, è stato brutalmente scritto nella user interface. Sappiamo da sempre che sarebbe bene separarlo dall’interfaccia utente e gestirlo dal layer di accesso ai dati. Per non complicare troppo il listato e arrivare al punto che stiamo trattando mi sono concesso questo “lusso”. Nella demo indicata trovate anche il codice posizionato correttamente in uno strato di accesso ai dati.

Una nota: Enrironment.TickCount restituisce il tempo in millisecondi da quando il device è stato acceso quindi non è adatto per calcolare prestazioni di codice che impiega meno di un millisecondo per essere eseguito e soprattutto occorre riavviare il sistema ogni tanto per evitare che tale numero, quando cresce, diventi negativo e di conseguenza sballi le statistiche.

Provando a capire i tempi di accesso medi (calcolati sulla media di  una serie di dieci test), i risultato che otteniamo realmente sul mio device (i-mate JasJar) sono i seguenti:

È alquanto improbabile, credo concordiate con me, prelevare 10.000 record dal db per inserirli nella combo box a meno di non voler far impazzire i nostri utenti. Pensate però anche al caso in cui si debbano leggere i 10.000 record per effettuare un calcolo.

Proseguiamo l’esempio di estrazione dei dati da una sola tabella cercando di migliorarlo passo passo: il primo errore che abbiamo commesso è utilizzare SELECT * in quanto il database deve prima cercare i campi appartenenti alla tabella per poi estrarre il contenuto dei record e ancor più importante è assolutamente inutile portarsi nel dataset tutti i campi della strtuttura quando in realtà lavoriamo solo con IdArticolo e Descrizione. Pensate che scegliendo solo questi due campi arriviamo al tempo di accesso di:

Sembra ininfluente dal punto di vista delle performance, ma in realtà abbiamo allocato in memoria (rispetto alla tabella di esempio che contiene altri 4 campi) il 37% in meno di spazio. Ogni processo su Windows CE ha a disposizione solo 32 MB di RAM, a prescindere dalla RAM fisica del device. Risparmiare memoria è la prima regola da seguire.

Il secondo, o terzo ormai, errore che abbiamo fatto riguarda sempre la memoria: perché allocare la struttura di un DataSet quando noi in realtà lavoriamo su una sola tabella che viene legata alla ListBox oppure su cui dobbiamo effettuare dei calcoli ? Usiamo quindi la struttura più snella della DataTable che comunque consente anche il binding. Ecco il listato:

public static void AllSelectFieldsDT(ComboBox lstArticoli, Label lblTime)

{

    int start = Environment.TickCount;

    lstArticoli.DataSource = null;

    lstArticoli.Items.Clear();

    SqlCeConnection conn = new SqlCeConnection();

    conn.ConnectionString = “…”;
    SqlCeCommand cmd = new SqlCeCommand("SELECT IdArticolo, ArticoloDex FROM tabArticoli", conn);

    SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);

    // N.B. Serve anche System.Xml

    DataTable dt = new DataTable();

    try

    {

        try

        {

            da.Fill(dt);

            lstArticoli.DisplayMember = "ArticoloDex";

            lstArticoli.ValueMember = "IdArticolo";

            lstArticoli.DataSource = dt;

        }

        finally

        {

            conn.Dispose();

            cmd.Dispose();

            da.Dispose();

        }

    }

    catch (SqlCeException ex)

    {

        MessageBox.Show("Errore Lettore");

    }

    int end = Environment.TickCount;

    lblTime.Text = (end - start).ToString();

}

In questo caso, ottenendo gli stessi risultati, abbiamo abbassato di un altro 10%  l’utilizzo di memoria necessaria all’operazione.

Se però, la prima regola è risparmiare memoria, visto che stiamo leggendo i dati per farci un calcolo o eseguire il binding ad un controllo perché non usiamo un DataReader che non alloca oggetti per record e campi, ma semplicemente ci consente di estrarre i dati portandoli nel controllo ? Così facendo evitiamo il cosiddetto double-buffering cioè prelevare i dati da DB per metterli nella DataTable (o DataSet) per poi rileggerli dalla DataTable per metterli nel controllo: stiamo anche facendo due giri sui dati per portarli prima nella struttura di memoria e poi portarli nel controllo che li visualizza.

Testimoniamo il tutto riportando prima il codice che utilizza il DataReader per poi vedere se effettivamente i tempi di esecuzione si abbassano:

public static void AllSelectFieldsDR(ComboBox lstArticoli, Label lblTime)

{

    int start = Environment.TickCount;

    lstArticoli.BeginUpdate();

    lstArticoli.DataSource = null;

    lstArticoli.Items.Clear();

    SqlCeConnection conn = new SqlCeConnection();

    conn.ConnectionString = “…”;

    SqlCeCommand cmd = new SqlCeCommand("SELECT IdArticolo, ArticoloDex FROM tabArticoli", conn);

    SqlCeDataReader dr = null;

    try

    {

        try

        {

            conn.Open();

            dr = cmd.ExecuteReader();

            lstArticoli.DisplayMember = "ArticoloDex";

            lstArticoli.ValueMember = "IdArticolo";

            int indexArticoloDex = dr.GetOrdinal("ArticoloDex");

            while(dr.Read())

            {

                lstArticoli.Items.Add(dr.GetString(indexArticoloDex));

            }

        }

        finally

        {

            conn.Dispose();

            cmd.Dispose();

            dr.Dispose();

            lstArticoli.EndUpdate();

        }

    }

    catch (SqlCeException ex)

    {

        MessageBox.Show("Errore Lettore");

    }

    int end = Environment.TickCount;

    lblTime.Text = (end - start).ToString();

}

Abbiamo sicuramente il “problema” di eseguire a mano il ciclo sui dati visto che il DataReader non può essere “bindato” direttamente su un controllo, ma con la seguente tabella dovremmo aver chiaro il perché lo dovremmo fare sempre:

Abbiamo raddoppiato le performance in lettura senza contare che i 10000 record non sono stati portati in memoria prima di essere legati al controllo: questo significa poca RAM utilizzata e pochi interventi del Garbage Collector che nel caso precedente doveva deallocare tonnellate di oggetti orfani.

Se utilizziamo un comando prepared, utile per i comandi usati più spesso, arriviamo ai seguenti dati di performance:

Abbiamo migliorato ancora e non di poco: siamo partiti da 1,06 secondi per arrivare a 0,39 per 100 record e da 151 secondi a 66 per 10.000 record, ma possiamo fare ancora meglio. Prima di vedere il metodo più veloce di accesso ai dati diamo uno sguardo al tanto sbandierato SqlCeResultSet che a detta di molti ha performance quasi uguali al DataReader, ma la possibilità di essere “bindato” all’interfaccia utente. Il codice lo trovate nella demo completa scaricabile: lo ometto per brevità. Avremo poi modo nel prossimo articolo di descrivere le sue funzionalità.

Direi che questa è la testimonianza che non siamo poi tanto vicini al DataReader perché viaggiamo a circa la metà…che non è esattamente vicino J

Mentre i metodi precedenti possono lavorare anche con tabelle in JOIN nello statement di SELECT, l’ultimo metodo, che come promesso è il più veloce, consente di lavorare su una sola tabella. Si chiama Base Table Cursor e come dice la parola indica l’utilizzo di un cursore su una tabella: prima di spaventarsi alla parola cursore che come sappiamo da anni è il nemico delle performance e scalabilità per SQL Server, pensiamo al fatto che SQLCE gira in-process (su tutte le piattaforme) rispetto all’applicazione che lo utilizza, quindi non è un peccato mortale, anzi, utilizzare un cursore diretto su una tabella.

Ecco i dati e poi il codice seguito da una spiegazione.

La parola più corretta da usare dopo il test non si può scrivere in un articolo, ma è impressionante il guadagno al crescere dei dati: 6,7 secondi contro 12 del DataReader con comando prepared (ricordo che i comandi prepared non è detto che restino nella cache di SQLCE per sempre!) e quindi dovremmo prendere circa 15 secondi come valore di riferimento medio per il DataReader; e un 24 secondi contro 66 mi sembra un ottimo guadagno.

Ecco il codice che utilizza un comando Prepared (come per il DataReader) con un Base Table Cursor

private static SqlCeCommand _cmdAllBaseTableCursor;

public static void AllBaseTableCursor(ComboBox lstArticoli, Label lblTime)

{

    int start = Environment.TickCount;

    lstArticoli.BeginUpdate();

    lstArticoli.DataSource = null;

    lstArticoli.Items.Clear();

    try

    {

        try

        {

            if (_cmdAllBaseTableCursor == null)

            {

                SqlCeConnection conn = new SqlCeConnection();

                conn.ConnectionString = “…”;

                SqlCeCommand cmd = new SqlCeCommand("tabArticoli", conn);

                cmd.Connection.Open();

                cmd.CommandType = CommandType.TableDirect;

                _cmdAllBaseTableCursor = cmd;

            }

            SqlCeDataReader dr = _cmdAllBaseTableCursor.ExecuteReader();

            lstArticoli.DisplayMember = "ArticoloDex";

            lstArticoli.ValueMember = "IdArticolo";

            int indexArticoloDex = dr.GetOrdinal("ArticoloDex");

            while (dr.Read())

            {

                lstArticoli.Items.Add(dr.GetString(indexArticoloDex));

            }

        }

        finally

        {

            // Non distruggo command e connection

            lstArticoli.EndUpdate();

        }

    }

    catch (SqlCeException ex)

    {

        MessageBox.Show("Errore Lettore");

    }

    int end = Environment.TickCount;

    lblTime.Text = (end - start).ToString();

}

L’unica differenza con il codice del DataReader, in effetti il Base Table Cursor è solamente il cursore che poi va letto con un metodo di accesso ai dati, è nella costruzione del comando che fa riferimento alla tabella tabArticoli e non a uno statement di SELECT e all’indicazione del CommandType = CommandType.TableDirect. Volendo estrarre un subset di record con un filtro o recuperare un singolo record il codice è leggermente diverso rispetto a un DataReader e avremo il prossimo articolo per far luce su questo punto. Nel prossimo articolo vedremo anche come eseguire bulk insert di dati cercando ancora una volta di ottimizzare il processo.

Se la vostra applicazione fa uso di classi custom e collection custom, alimentate dal codice di accesso ai dati, ancora una volta, per leggere i dati da una sola tabella, il metodo migliore è usare un Base Table Cursor.

Sperando di aver testimoniato quello che cerchiamo di dire da anni ad ogni nostra conferenza o corso mobile e cioè: si può far andar forte, anzi, molto forte, un’applicazione mobile che usa SQLCE, vi rimando al prossimo articolo dove analizzeremo la selezione di una serie di righe (filtri sui record) e recupero del singolo record. Vi saluto dandovi una anticipazione: il tempo di accesso ad un singolo record utilizzando un DataSet, un SqlCeResultSet e un Base Table Cursor su 10.000 record:

Parola che non si può scrivere in un articolo ma che comincia con M e finisce per A J

Roberto Brunetti

http://blogs.devleap.com/rob

http://thinkmobile.it