Strategie di progettazione dell'applicazione per il miglioramento delle prestazioni del database

Non modificate la proprietà text di un oggetto SQLStatement dopo averlo eseguito. Utilizzate invece un'istanza SQLStatement per ogni istruzione SQL e utilizzate i parametri delle istruzioni per fornire valori diversi.

Prima dell'esecuzione di qualsiasi istruzione SQL, il runtime la prepara (compila) al fine di determinare i passi da effettuare internamente per eseguirla. Quando si chiama SQLStatement.execute() su un'istanza SQLStatement che non è mai stata eseguita in precedenza, l'istruzione viene preparata automaticamente prima dell'esecuzione. In occasione delle successive chiamate del metodo execute() , se la proprietà SQLStatement.text non ha subito modifiche, l'istruzione risulta ancora preparata e, di conseguenza, viene eseguita più rapidamente.

Per ottenere il massimo vantaggio dal riutilizzo delle istruzioni, se i valori cambiano tra un'esecuzione e l'altra, utilizzate i parametri per personalizzare le istruzioni. I parametri vengono specificati mediante la proprietà di array associativo SQLStatement.parameters . A differenza di quanto avviene quando si modifica la proprietà text dell'istanza SQLStatement, se si modificano i valori dei parametri delle istruzioni non è necessario che il runtime prepari di nuovo le istruzioni.

Quando si riutilizza un'istanza SQLStatement, l'applicazione deve memorizzare un riferimento a tale istanza dopo che è stata preparata. Per tenere un riferimento all'istanza, dichiarate la variabile come variabile di classe-ambito invece che di funzione-ambito. A questo scopo è consigliabile strutturare l'applicazione in modo tale che l'istruzione SQL sia contenuta in una sola classe. Anche i gruppi di istruzioni che vengono eseguite congiuntamente possono essere contenuti in una sola classe. Questa tecnica utilizza il modello di progettazione Command. Se le istanze vengono definite come variabili membro della classe, permangono per tutto il tempo in cui l'istanza della classe che le contiene continua a esistere nell'applicazione. Come minimo, è possibile semplicemente definire una variabile che contiene l'istanza SQLStatement al fuori di una funzione, in modo che tale istanza permanga in memoria. Ad esempio, dichiarate l'istanza SQLStatement come variabile membro in una classe ActionScript o come variabile senza funzione in un file JavaScript. È poi possibile impostare i valori dei parametri dell'istruzione e chiamare il relativo metodo execute() quando si desidera eseguire effettivamente la query.

Utilizzate gli indici dei database per aumentare la velocità di esecuzione per il confronto e l'ordinamento dei dati.

Quando create un indice per una colonna, il database memorizza una copia dei dati della colonna. I dati della copia vengono memorizzati in ordine numerico o alfabetico. L'ordinamento consente al database di confrontare rapidamente i valori (ad esempio quando si utilizza l'operatore di uguaglianza) e ordinare i dati dei risultati utilizzando la clausola ORDER BY .

Gli indici del database vengono mantenuti costantemente aggiornati, causando un leggero rallentamento delle operazioni di modifica dei dati (INSERT o UPDATE) nella tabella. In genere, tuttavia, l'aumento della velocità di recupero dei dati può essere significativo. Per via di questo compromesso a livello di prestazioni, è consigliabile evitare di indicizzare ogni colonna di ogni tabella e utilizzare invece una strategia mirata per definire gli indici. Per pianificare la vostra strategia di indicizzazione, attenetevi alle seguenti linee guida:

• Indicizzate le colonne che vengono utilizzate nelle operazioni di join delle tabelle, nelle clausole WHERE o nelle clausole ORDER BY

• Create un unico indice per le colonne che vengono utilizzate spesso insieme

• Per le colonne che contengono dati di testo da recuperare in ordine alfabetico, specificate la sequenza di confronto COLLATE NOCASE per l'indice

Precompilate le istruzioni SQL durante i periodi di inattività dell'applicazione.

La prima esecuzione di un'istruzione SQL è più lenta perché il testo SQL viene preparato (compilato) dal motore del database. Poiché la preparazione e l'esecuzione di un'istruzione possono richiedere molte risorse, è consigliabile precaricare i dati iniziali e quindi eseguire le altre istruzioni in background:

1. Caricate per primi i dati che servono all'applicazione.

2. Quando le operazioni di avvio iniziali dell'applicazione sono state completate, oppure durante un altro periodo di “inattività” dell'applicazione, eseguite le altre istruzioni.

Supponiamo, ad esempio, che l'applicazione non abbia la necessità di accedere al database per visualizzare la schermata iniziale. In questo caso, aspettate che venga visualizzata la schermata iniziale prima di aprire la connessione al database. Infine, create le istanze SQLStatement ed eseguitene il maggior numero possibile.

Supponiamo in alternativa che l'applicazione visualizzi immediatamente alcuni dati, ad esempio i risultati di una query, al momento dell'avvio. In tal caso, procedete all'esecuzione dell'istanza SQLStatement relativa a quella query. Dopo il caricamento e la visualizzazione dei dati iniziali, create le istanze SQLStatement per le altre operazioni di database e, se possibile, eseguite altre istruzioni necessarie in un secondo momento.

Se riutilizzate le istanze SQLStatement, in pratica, il tempo aggiuntivo che serve per preparare l'istruzione è un “inconveniente” che si verifica una tantum e probabilmente non ha un impatto rilevante sulle prestazioni generali.

Raggruppate più operazioni di modifica dei dati SQL in una transazione.

Supponiamo che eseguiate numerose istruzioni SQL che comportano l'aggiunta o la modifica di dati (istruzioni INSERT o UPDATE ). È possibile ottenere un notevole aumento delle prestazioni eseguendo tutte le istruzioni nell'ambito di una transazione esplicita. Se non si dà inizio esplicitamente a una transazione, ciascuna istruzione viene eseguita in una propria transazione individuale creata automaticamente. Al termine dell'esecuzione di ogni transazione (istruzione), il runtime scrive i dati risultanti nel file del database sul disco.

Consideriamo invece ora gli sviluppi a seguito della creazione esplicita di una transazione e dell'esecuzione delle istruzioni nell'ambito di tale transazione. Il runtime apporta tutte le modifiche in memoria, poi le scrive tutte insieme, contemporaneamente, nel file del database una volta che è stato eseguito il commit della transazione. La scrittura dei dati sul disco rappresenta di solito la fase dell'operazione che richiede più tempo. Di conseguenza, una sola operazione di scrittura su disco, invece che un'operazione di scrittura per ciascuna istruzione SQL, consente un notevole miglioramento delle prestazioni.

Elaborate in più parti i set di risultati di grandi dimensioni delle query SELECT utilizzando i metodi execute() (con il parametro prefetch ) e next() della classe SQLStatement.

Supponiamo che eseguiate un'istruzione SQL che restituisce un set di risultati di grandi dimensioni. L'applicazione elabora ciascuna riga di dati in modo ciclico, ad esempio per formattare i dati o creare oggetti a partire dai dati stessi. L'elaborazione di questi dati può richiedere molto tempo e causare problemi di rendering, ad esempio una mancanza di risposta o il blocco dello schermo. Come illustrato in Operazioni asincrone , una possibile soluzione è rappresentata dalla suddivisione delle attività di elaborazione in più blocchi. L'API del database SQL semplifica la suddivisione dell'elaborazione dei dati.

Il metodo execute() della classe SQLStatement è dotato di un parametro prefetch opzionale (il primo parametro). L'eventuale valore fornito specifica il numero massimo di righe di risultati che vengono restituite dal database al termine dell'esecuzione:

dbStatement.addEventListener(SQLEvent.RESULT, resultHandler); 
dbStatement.execute(100); // 100 rows maximum returned in the first set

Dopo che è stato restituito il primo set di dati dei risultati, potete chiamare il metodo next() per continuare l'esecuzione dell'istruzione e recuperare un altro set di righe di risultati. Analogamente al metodo execute() , il metodo next() accetta un parametro prefetch che specifica il numero massimo di righe da restituire:

// This method is called when the execute() or next() method completes 
function resultHandler(event:SQLEvent):void 
{ 
    var result:SQLResult = dbStatement.getResult(); 
    if (result != null) 
    { 
        var numRows:int = result.data.length; 
        for (var i:int = 0; i < numRows; i++) 
        { 
            // Process the result data 
        } 
         
        if (!result.complete) 
        { 
            dbStatement.next(100); 
        } 
    } 
}

Potete continuare a chiamare il metodo next() finché non sono stati caricati tutti i dati. Come illustrato nell'esempio precedente, è possibile verificare se il caricamento dei dati è stato completato o meno. Verificate la proprietà complete dell'oggetto SQLResult che viene creato al termine di ogni esecuzione del metodo execute() o next() .

Utilizzate più oggetti SQLConnection asincroni con un unico database per eseguire più istruzioni contemporaneamente.

Se un oggetto SQLConnection si connette a un database utilizzando il metodo openAsync() , l'oggetto viene eseguito in background anziché nel thread di esecuzione runtime principale. Inoltre, ogni oggetto SQLConnection viene eseguito in un thread di background distinto. Utilizzando più oggetti SQLConnection, potete quindi eseguire più istruzioni SQL contemporaneamente.

L'uso di questo approccio può comportare alcuni svantaggi. Il principale è che ogni oggetto SQLStatement aggiuntivo richiede una quantità supplementare di memoria. Le esecuzioni contemporanee determinano inoltre un carico di lavoro aggiuntivo per il processore, soprattutto sui computer che hanno una sola CPU o un solo core di CPU. Questo approccio è quindi sconsigliato per i dispositivi mobili.

Un ulteriore problema è rappresentato dal fatto che non sempre si possono sfruttare i vantaggi offerti dal riutilizzo degli oggetti SQLStatement perché ogni oggetto SQLStatement è collegato a un unico oggetto SQLConnection. Di conseguenza, un oggetto SQLStatement non può essere riutilizzato se l'oggetto SQLConnection associato è già in uso.

Se scegliete di utilizzare più oggetti SQLConnection connessi a un unico database, tenete presente che ognuno di questi oggetti esegue le proprie istruzioni in una transazione a se stante. Tenete conto di queste transazioni separate, ad esempio l'aggiunta, la modifica o l'eliminazione di dati, contenute in qualsiasi blocco di codice che modifica i dati.

Paul Robertson ha creato una libreria di codice open source che vi consente di sfruttare i vantaggi offerti dall'uso di più oggetti SQLConnection riducendo al minimo i possibili inconvenienti. Questa libreria utilizza un pool di oggetti SQLConnection e gestisce gli oggetti SQLStatement associati. In questo modo garantisce la possibilità di riutilizzare gli oggetti SQLStatement e la disponibilità di più oggetti SQLConnection per l'esecuzione contemporanea di più istruzioni. Per ulteriori informazioni e per scaricare la libreria, visitate http://probertson.com/projects/air-sqlite/ .