Ottimizzazione delle prestazioni delle query (SQL Server Compact)

È possibile migliorare le prestazioni dell'applicazione SQL Server Compact 4.0 ottimizzando le query utilizzate. Nelle sezioni seguenti vengono illustrate le tecniche disponibili per l'ottimizzazione delle prestazioni delle query.

Migliorare gli indici

La creazione di indici utili costituisce uno dei principali modi per migliorare le prestazioni delle query. Gli indici utili consentono di trovare i dati con un numero inferiore di operazioni di I/O su disco e un minore utilizzo delle risorse di sistema.

Per creare indici utili, è necessario comprendere le modalità di utilizzo dei dati, i tipi di query e la frequenza con cui vengono eseguiti e come gli indici possono essere utilizzati da Query Processor per trovare rapidamente i dati.

Al momento della scelta degli indici da creare, esaminare le query critiche le cui prestazioni influiranno maggiormente sull'esperienza degli utenti. Creare indici per supportare specificamente queste query. Dopo l'aggiunta di un indice, eseguire nuovamente la query per verificare se le prestazioni risultano migliorate. In caso contrario, rimuovere l'indice.

Come nella maggior parte delle tecniche di ottimizzazione delle prestazioni, sono necessari compromessi. Con una maggiore quantità di indici, ad esempio, viene potenzialmente velocizzata l'esecuzione di query SELECT. Le operazioni DML (INSERT, UPDATE e DELETE), tuttavia, risulteranno notevolmente rallentate poiché ogni operazione richiede la gestione di più indici. Pertanto, se le query sono prevalentemente costituite da istruzioni SELECT può rivelarsi utile disporre di più indici. Se l'applicazione esegue molte operazioni DML, è consigliabile limitare il numero degli indici creati.

SQL Server Compact include il supporto per Showplan, che consentono di valutare e ottimizzare le query. SQL Server Compact utilizza lo stesso schema di Showplan di SQL Server 2008 R2, anche se in SQL Server Compact viene utilizzato un subset degli operatori. Per ulteriori informazioni sullo schema di Showplan Microsoft, visitare il sito Web all'indirizzo https://schemas.microsoft.com/sqlserver/2004/07/showplan/.

Nelle sezioni successive sono disponibili informazioni aggiuntive sulla creazione di indici utili.

Creare indici altamente selettivi

L'indicizzazione su colonne utilizzate nella clausola WHERE delle query critiche garantisce in genere un miglioramento delle prestazioni. Ciò dipende tuttavia dal livello di selettività dell'indice. La selettività è il rapporto tra le righe risultanti e le righe totali. Se il rapporto è basso, l'indice è altamente selettivo. È possibile eliminare la maggior parte delle righe e ridurre notevolmente le dimensioni del set dei risultati, creando così un indice utile. Un indice non selettivo, al contrario, non è altrettanto utile.

Un indice univoco dispone della massima selettività. Poiché può corrispondere una sola riga, l'indice si rivela estremamente utile per le query finalizzate alla restituzione di una riga esatta. Un indice su una colonna con ID univoco, ad esempio, consentirà di trovare rapidamente una determinata riga.

È possibile valutare la selettività di un indice eseguendo stored procedure sp_show_statistics su tabelle di SQL Server Compact. Per valutare la selettività di due colonne "Customer ID" e "Ship Via", ad esempio, è possibile eseguire le stored procedure seguenti:

sp_show_statistics_steps 'orders', 'customer id';

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS

------------------------------------------------------------

ALFKI               0            7                   0

ANATR               0            4                   0

ANTON               0           13                   0

AROUT               0           14                   0

BERGS               0           23                   0

BLAUS               0            8                   0

BLONP               0           14                   0

BOLID               0            7                   0

BONAP               0           19                   0

BOTTM               0           20                   0

BSBEV               0           12                   0

CACTU               0            6                   0

CENTC               0            3                   0

CHOPS               0           12                   0

COMMI               0            5                   0

CONSH               0            4                   0

DRACD               0            9                   0

DUMON               0            8                   0

EASTC               0           13                   0

ERNSH               0           33                   0

(90 rows affected)

e

sp_show_statistics_steps 'orders', 'reference3';

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS

------------------------------------------------------------

1               0            320                   0

2               0            425                   0

3               0            333                   0

(3 rows affected)

I risultati evidenziano che la colonna "Customer ID" è caratterizzata da un livello di duplicazione notevolmente inferiore. Di conseguenza, un indice su tale colonna risulterà molto più selettivo di un indice sulla colonna "Ship Via".

Per ulteriori informazioni sull'utilizzo di queste stored procedure, vedere sp_show_statistics (SQL Server Compact), sp_show_statistics_steps (SQL Server Compact) e sp_show_statistics_columns (SQL Server Compact).

Creare indici a più colonne

Gli indici a più colonne costituiscono estensioni naturali degli indici a colonna singola. Gli indici a più colonne si rivelano utili per la valutazione di espressioni di filtro corrispondenti a un set di prefissi di colonne chiave. L'indice composto CREATE INDEX Idx_Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) consente ad esempio di valutare le query seguenti:

• ... WHERE "Last Name" = 'Doe'

• ... WHERE "Last Name" = 'Doe' AND "First Name" = 'John'

• ... WHERE "First Name" = 'John' AND "Last Name" = 'Doe'

Non è invece utile per la query seguente:

• ... WHERE "First Name" = 'John'

In occasione della creazione di un indice a più colonne, è consigliabile inserire le colonne più selettive più a sinistra nella chiave. In questo modo, l'indice risulterà più selettivo in caso di corrispondenza con diverse espressioni.

Evitare l'indicizzazione di tabelle di piccole dimensioni

Una tabella viene definita di piccole dimensioni se il contenuto corrisponde a una o poche pagine di dati. Evitare l'indicizzazione di tabelle di dimensioni estremamente ridotte, poiché si rivela in genere più efficiente eseguire un'analisi di tabella. È così possibile risparmiare il caricamento e l'elaborazione delle pagine di indice. Non creando un indice su tabelle di dimensioni estremamente ridotte, viene rimossa la possibilità che Query Optimizer ne selezioni una.

SQL Server Compact archivia i dati in pagine di 4 KB. È possibile eseguire il conteggio approssimativo delle pagine utilizzando la formula seguente. Il conteggio effettivo può tuttavia essere leggermente superiore a causa dell'overhead del motore di archiviazione.

<somma delle dimensioni delle colonne in byte> * <n. di righe>

<n. di pagine> = -----------------------------------------------------------------

4096

Si supponga ad esempio di disporre di una tabella con lo schema descritto di seguito.

La tabella include 2820 righe. In base alla formula, per archiviarne i dati sono necessarie circa 16 pagine:

<n. di pagine> = ((4 + 4 + 8 + 2 + 4) * 2820) / 4096 = 15,15 pagine

Scegliere gli elementi da indicizzare

È consigliabile creare sempre indici sulle chiavi primarie. È in genere utile creare inoltre indici sulle chiavi esterne, poiché le chiavi primarie e le chiavi esterne vengono spesso utilizzate per unire in join le tabelle. Gli indici su queste chiavi consentono a Query Optimizer di considerare algoritmi di join di indici più efficienti. Se la query unisce tabelle in join utilizzando altre colonne, per lo stesso motivo è in genere utile creare indici su tali colonne.

In caso di creazione di vincoli di chiave primaria e di chiave esterna, SQL Server Compact crea automaticamente i relativi indici, che vengono sfruttati per l'ottimizzazione delle query. È opportuno limitare le dimensioni delle chiavi primarie ed esterne. In questo modo, i join vengono eseguiti più velocemente.

Utilizzare indici con clausole di filtro

È possibile utilizzare gli indici per velocizzare la valutazione di determinati tipi di clausole di filtro. Nonostante tutte le clausole di filtro riducano il set di risultati finale di una query, alcune possono inoltre ridurre la quantità di dati di cui deve essere eseguita l'analisi.

Un argomento di ricerca (SARG) limita una ricerca specificando una corrispondenza esatta, un intervallo di valori o una combinazione di due o più elementi uniti tramite AND. Può assumere uno dei formati seguenti:

• colonna operatore <costante o variabile>

• <costante o variabile> operatore colonna

Gli operatori SARG includono =, >, <, >=, <=, IN, BETWEEN e talvolta LIKE (in casi di corrispondenza di prefissi, ad esempio LIKE 'John%'). Un argomento di ricerca può contenere più condizioni unite tramite AND. Gli argomenti di ricerca possono essere costituiti da query corrispondenti a un valore specifico, ad esempio:

• "Customer ID" = 'ANTON'

• 'Doe' = "Last Name"

Gli argomenti di ricerca possono inoltre essere costituiti da query corrispondenti a un intervallo di valori, ad esempio:

• "Order Date" > '1/1/2002'

• "Customer ID" > 'ABCDE' AND "Customer ID" < 'EDCBA'

• "Customer ID" IN ('ANTON', 'AROUT')

Un'espressione che non utilizza operatori SARG non migliora le prestazioni, poiché Query Processor di SQL Server Compact deve valutare ogni riga per determinare se soddisfa la clausola di filtro. Pertanto, un indice non è utile su espressioni che non utilizzano operatori SARG. Gli operatori non SARG includono NOT, <>, NOT EXISTS, NOT IN, NOT LIKE e le funzioni intrinseche.

Utilizzare Query Optimizer

Quando vengono determinati i metodi di accesso per le tabelle di base, Query Optimizer di SQL Server Compact determina se esiste un indice per una clausola SARG. Se esiste, Query Optimizer valuta l'indice calcolando il numero di righe restituite. Viene quindi stimato il costo della ricerca delle righe risultanti mediante l'indice. L'accesso indicizzato verrà scelto se presenta un costo inferiore rispetto all'analisi di tabella. Un indice è potenzialmente utile se la prima colonna o il set di prefissi delle colonne viene utilizzato nell'argomento di ricerca e tale argomento stabilisce un limite inferiore, un limite superiore o entrambi per limitare la ricerca.

Comprendere la differenza tra tempo di risposta e tempo totale

Il tempo di risposta è il tempo necessario per la restituzione del primo record da parte di una query. Il tempo totale è il tempo necessario per la restituzione di tutti i record da parte della query. Per un'applicazione interattiva, il tempo di risposta è importante poiché rappresenta il tempo percepito dall'utente prima di ricevere una conferma visiva dell'elaborazione di una query. Per un'applicazione batch, il tempo totale riflette la velocità effettiva complessiva. È necessario determinare i criteri di prestazioni per l'applicazione e le query e quindi completare la progettazione in modo corrispondente.

Si supponga ad esempio che la query restituisca 100 record e venga utilizzata per popolare un elenco con i primi cinque record. In questo caso, non è importante il tempo necessario per restituire tutti i 100 record. Si desidera invece che la query restituisca i primi record rapidamente, in modo da consentire il popolamento dell'elenco.

Numerose operazioni di query possono essere eseguite senza archiviare i risultati intermedi. Queste operazioni vengono definite "in pipeline". Esempi di operazioni in pipeline sono costituiti da proiezioni, selezioni e join. Le query implementate con queste operazioni possono restituire risultati in modo immediato. Altre operazioni, ad esempio SORT e GROUP-BY, richiedono l'utilizzo di tutto l'input prima di restituire risultati alle operazioni padre. Queste operazioni vengono definite come basate sulla materializzazione. Le query implementate con queste operazioni sono solitamente caratterizzate da un ritardo iniziale determinato dalla materializzazione. Dopo questo ritardo iniziale, i record vengono in genere restituiti molto rapidamente.

È consigliabile evitare la materializzazione nelle query con requisiti specifici in termini di tempo di risposta. L'utilizzo di un indice per l'implementazione di ORDER-BY, ad esempio, garantisce un tempo di risposta inferiore rispetto all'ordinamento. Nella sezione seguente è disponibile una descrizione più dettagliata.

Indicizzare le colonne di ORDER-BY/GROUP-BY/DISTINCT in modo da ottimizzare il tempo di risposta

Le operazioni ORDER-BY, GROUP-BY e DISTINCT rappresentano tipi di ordinamento. L'ordinamento viene implementato da Query Processor di SQL Server Compact in due modi. Se i record sono già ordinati in base a un indice, è sufficiente utilizzare soltanto l'indice. In caso contrario, è necessario utilizzare una tabella di lavoro temporanea per ordinare innanzitutto i record. Questo ordinamento preliminare può causare ritardi iniziali significativi su dispositivi con CPU di potenza ridotta e memoria limitata e deve essere preferibilmente evitato nei casi in cui si rivela importante il tempo di risposta.

Nel contesto degli indici a più colonne, affinché un determinato indice venga considerato da ORDER-BY o GROUP-BY è necessario che le colonne di ORDER-BY o GROUP-BY corrispondano al set di prefissi delle colonne dell'indice, nell'ordine esatto. L'indice CREATE INDEX Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) consente ad esempio di ottimizzare le query seguenti:

• ... ORDER BY / GROUP BY "Last Name" ...

• ... ORDER BY / GROUP BY "Last Name", "First Name" ...

Non è invece in grado di ottimizzare quanto segue:

• ... ORDER BY / GROUP BY "First Name" ...

• ... ORDER BY / GROUP BY "First Name", "Last Name" ...

Affinché un indice a più colonne venga considerato da un'operazione DISTINCT, l'elenco di proiezione deve corrispondere a tutte le colonne dell'indice, anche se non necessariamente nell'ordine esatto. L'indice precedente consente di ottimizzare le query seguenti:

• ... DISTINCT "Last Name", "First Name" ...

• ... DISTINCT "First Name", "Last Name" ...

Non è invece in grado di ottimizzare quanto segue:

• ... DISTINCT "First Name" ...

• ... DISTINCT "Last Name" ...

Riscrivere sottoquery per l'utilizzo di JOIN

È talvolta possibile riscrivere una sottoquery in modo da utilizzare JOIN e ottenere prestazioni più elevate. La creazione di un join offre il vantaggio di consentire la valutazione delle tabelle in un ordine diverso rispetto a quello definito dalla query. L'utilizzo di una sottoquery offre in genere il vantaggio di non richiedere necessariamente l'analisi di tutte le righe della sottoquery per la valutazione della relativa espressione. Una sottoquery EXISTS, ad esempio, può restituire TRUE esaminando la prima riga risultante.

Per determinare tutti gli ordini che includono almeno un articolo con sconto del 25% o superiore, ad esempio, è possibile utilizzare la sottoquery EXISTS seguente:

SELECT "Order ID" FROM Orders O

WHERE EXISTS (SELECT "Order ID"

FROM "Order Details" OD

WHERE O."Order ID" = OD."Order ID"

AND Discount >= 0.25)

È inoltre possibile riscrivere la sottoquery utilizzando JOIN:

SELECT DISTINCT O."Order ID" FROM Orders O INNER JOIN "Order Details"

OD ON O."Order ID" = OD."Order ID" WHERE Discount >= 0.25

Limitare l'utilizzo di outer join

Gli outer join vengono gestiti in modo diverso rispetto agli inner join, poiché Query Optimizer non tenta di riorganizzare l'ordine di join delle tabelle di outer join come nel caso delle tabelle di inner join. Viene innanzitutto eseguito l'accesso alla tabella esterna (la tabella di sinistra nell'outer join sinistro e la tabella di destra nell'outer join destro) e quindi alla tabella interna. Questo ordine di join fisso potrebbe determinare piani di esecuzione non ottimali.

Per ulteriori informazioni su una query che contiene INNER JOIN, vedere la Microsoft Knowledge Base.

Utilizzare query con parametri

Se l'applicazione esegue una serie di query che differiscono soltanto per alcune costanti, è possibile migliorare le prestazioni utilizzando una query con parametri. Per restituire gli ordini di diversi clienti, ad esempio, è possibile eseguire la query seguente:

SELECT "Customer ID" FROM Orders WHERE "Order ID" = ?

Le query con parametri garantiscono prestazioni più elevate grazie alla possibilità di compilare la query una sola volta ed eseguire il piano compilato più volte. A livello di programmazione, è necessario mantenere l'oggetto comando contenente il piano di query memorizzato nella cache. Eliminando l'oggetto comando precedente e creandone uno nuovo viene eliminato il piano nella cache ed è necessario ricompilare la query. Se si deve intercalare l'esecuzione di diverse query con parametri, è possibile creare diversi oggetti comando, ognuno dei quali determina la memorizzazione nella cache del piano di esecuzione per una query con parametri. In questo modo, è possibile evitare le relative ricompilazioni.

Eseguire una query solo quando necessario

Query Processor di SQL Server Compact è un potente strumento per l'esecuzione di query sui dati archiviati in un database relazionale. A qualsiasi strumento per l'elaborazione di query è tuttavia associato un costo intrinseco. Prima dell'esecuzione effettiva, deve essere compilato, ottimizzato e generato un piano di esecuzione. Ciò è applicabile, in particolare, alle query semplici che vengono completate rapidamente. Per tale motivo, implementando personalmente la query è talvolta possibile ottenere un miglioramento significativo delle prestazioni. Se in un componente critico è importante ogni millisecondo, è consigliabile considerare l'alternativa di implementare personalmente le query semplici. Per query estese e complesse, è comunque preferibile avvalersi di Query Processor.

Si supponga ad esempio di dover cercare l'ID cliente per una serie di ordini organizzati in base all'ID ordine. Questa operazione può essere eseguita in due modi. È innanzitutto possibile eseguire la procedura seguente per ogni ricerca:

1. Aprire la tabella di base Orders.

2. Trovare la riga utilizzando lo specifico "Order ID".

3. Recuperare il valore "Customer ID".

In alternativa, è possibile eseguire la query seguente per ogni ricerca:

SELECT "Customer ID" FROM Orders WHERE "Order ID" = <the specific order id>

La soluzione basata sulla query è più semplice ma meno veloce rispetto alla soluzione manuale, poiché Query Processor di SQL Server Compact deve convertire l'istruzione SQL dichiarativa nelle stesse tre operazioni implementabili manualmente. I tre passaggi vengono quindi eseguiti in sequenza. La scelta del metodo da utilizzare dipende dalla maggiore importanza attribuita nell'applicazione alla semplicità o alle prestazioni.