ACCESS 2000: Ottimizzazione delle query in Microsoft Access 2000

Difficoltà media: sono richieste conoscenze di base di creazione di macro, gestione di codice e di interoperabilità.

In questo articolo viene illustrato come ottimizzare le prestazioni delle query in Microsoft Access 2000. Gli argomenti trattati riguardano il Query Optimizer del modulo di gestione di database Microsoft Jet, la determinazione dei tempi delle query, l'analisi delle prestazioni e alcuni suggerimenti di progettazione su come migliorare le prestazioni delle query.

In questo articolo si presuppone che il database contenga delle tabelle locali anziché tabelle collegate. Se le tabelle sono collegate, le informazioni contenute in questo articolo restano comunque valide, sebbene subentrino ulteriori aspetti che possono influire sulle prestazioni delle query in tabelle collegate. Per ulteriori informazioni sull'ottimizzazione delle prestazioni in tabelle collegate, nella Microsoft Knowledge Base eseguire una ricerca della seguente stringa:

Query Optimizer

Il modulo di gestione di database Microsoft Jet contiene numerosi componenti, ma quello più importante per le query, oltre che il più complesso, è Query Optimizer. Si tratta di un'utilità di ottimizzazione basata sul costo che assegna pertanto a ciascuna attività di query un costo in termini di tempo scegliendo alla fine l'elenco delle attività meno dispendioso da eseguire per generare l'insieme di risultati richiesti. Più tempo è richiesto per l'esecuzione di un'attività, più costosa o dispendiosa è considerata tale attività.

Per decidere quale strategia di query utilizzare, Query Optimizer utilizza delle statistiche basate sul numero di record e di pagine di dati presenti in una tabella, sull'ubicazione della tabella, sulla presenza o meno di indici, sulla loro univocità e così via. Sulla base di queste statistiche viene quindi scelta la strategia di query interna migliore per svolgere una particolare query.

Le statistiche vengono aggiornate ogni volta che si compila una query. Una query viene contrassegnata per la compilazione ogni volta che si salvano delle modifiche alla query o alle tabelle sottostanti e quando il database viene compattato. Se una query viene contrassegnata, la compilazione e l'aggiornamento delle statistiche verranno eseguiti in occasione della successiva esecuzione della query. La compilazione di una query richiede solitamente da uno a quattro secondi.

Se viene aggiunto un numero significativo di record al database, si consiglia di aprire e salvare le query per ricompilarle. Ad esempio, se si progetta e si sottopone a verifica una query utilizzando un piccolo insieme di dati di esempio, si consiglia di ricompilare la query dopo aver aggiunto ulteriori record al database. In questo modo si garantiranno prestazioni ottimali della query quando l'applicazione è in uso.

NOTA: non è possibile visualizzare gli schemi di ottimizzazione del modulo di gestione di database Jet né specificare le modalità di ottimizzazione di una query. Ciononostante è possibile utilizzare Database Documenter per scoprire se sono presenti indici nelle tabelle di database e se tali indici sono univoci.

Per ulteriori informazioni su Database Documenter, fare clic sul numero dell'articolo della Knowledge Base riportato di seguito (gli articoli con prefisso "Q" contengono informazioni in inglese):

Determinazione dei tempi delle query

Esistono due grandezze di tempo significative per una query di selezione: il tempo richiesto per visualizzare il primo schermo di dati e il tempo richiesto per ottenere l'ultimo record. Se una query restituisce un solo schermo di dati, questi due valori coincideranno, mentre se una query restituisce molti record, questi due valori potranno differire.

Se questi due valori coincidono quando si visualizza una query di selezione in visualizzazione Foglio dati, si vedrà uno schermo di dati e un numero totale di record restituiti dalla query, ad esempio "Record 1 di N ". Se il modulo di gestione di database Jet impiega meno tempo per visualizzare il primo schermo di dati di quanto non impieghi a completare la query e a recuperare l'ultimo record, verrà visualizzato lo schermo di dati ma nessun valore N nella stringa "Record 1 di N ". Il valore N rimarrà vuoto finché la query non sarà stata completata o non sarà stato recuperato l'ultimo record.

Questo comportamento dipende dal fatto che il modulo di gestione di database Jet sceglie una delle due strategie di prestazioni: completare la query e quindi visualizzare i dati oppure visualizzare i dati e quindi completare la query. Non è possibile scegliere quale strategia utilizzare, in quanto il modulo di gestione di database Jet selezionerà sempre la più efficiente.

Analisi delle prestazioni

Se si utilizza Microsoft Access 7.0, Access 97 o Access 2000 sarà possibile utilizzare l'Analizzatore prestazioni per analizzare le query nel database. L'analisi delle prestazioni delle query è strettamente legata al modulo di gestione di database Jet, per questa ragione l'Analizzatore prestazioni suggerisce di aggiungere degli indici solo qualora gli indici vengano effettivamente utilizzati dal modulo di gestione di database Jet per ottimizzare la query. Questo significa che l'Analizzatore prestazioni è in grado di fornire suggerimenti di prestazioni più specifici per il database rispetto ai suggerimenti generali riportati di seguito nella sezione "Suggerimenti per migliorare le prestazioni delle query" di questo articolo.

Per eseguire l'Analizzatore prestazioni in Microsoft Access 7.0, Microsoft Access 97 o Microsoft Access 2000, scegliere Analizza dal menu Strumenti, quindi Prestazioni.

Suggerimenti per migliorare le prestazioni delle query

Per migliorare le prestazioni delle query, seguire i seguenti suggerimenti:

• Compattare il proprio database. Questa operazione consente di velocizzare le query in quanto i record di una tabella vengono riorganizzati in modo che si trovino in pagine di database adiacenti ordinate in base alla chiave primaria della tabella. In questo modo si migliorano le prestazioni delle analisi sequenziali dei record di una tabella, in quanto per recuperare tutti i record sarà sufficiente leggere il numero minimo di pagine di database. Una volta compattato il database, eseguire ciascuna query per compilarla utilizzando le statistiche di tabella aggiornate.
• Indicizzare ciascun campo utilizzato per impostare i criteri per la query e indicizzare i campi su ciascun lato di un join oppure creare una relazione tra questi campi. Quando si crea una relazione, il modulo di gestione di database Microsoft Jet crea un indice nella chiave esterna oppure utilizza l'indice esistente.
  
  NOTA: il modulo di gestione di database Microsoft Jet ottimizza automaticamente una query che unisce una tabella di Microsoft Access presente sul disco rigido e una tabella del server ODBC, se la tabella di Microsoft Access è di dimensioni ridotte e i campi di join sono indicizzati. In questo caso, Microsoft Access mostrerà un miglioramento delle prestazioni in quanto verranno richiesti al server solo i record necessari. Assicurarsi che le tabelle unite provenienti da origini diverse vengano indicizzate sui campi di join.
• Quando si definisce un campo in una tabella, scegliere il tipo di dati più ridotto per i dati del campo. Assegnare inoltre ai campi che si intende utilizzare nei join gli stessi tipi di dati o tipi di dati compatibili, ad esempio Contatore e Numerico, se la proprietà FieldSize è impostata a Intero lungo.
• Quando si crea una query, aggiungere solo i campi necessari. Nei campi utilizzati per impostare i criteri, fare clic per deselezionare la casella di controllo Mostra, se non si vuole che tali campi vengano visualizzati.
• Se la proprietà RecordSource di una maschera o di un report è impostata a un'istruzione SQL, salvare tale istruzione sotto forma di query, quindi impostare la proprietà RecordSource in base al nome della query.
• Evitare campi calcolati nelle sottoquery. Se si aggiunge una query contenente un campo calcolato che comporta un'altra query, nella query di livello superiore l'espressione presente nel campo calcolato potrebbe mostrare prestazioni ridotte. Nell'esempio sottostante, la query Q1 è utilizzata come input per la query Q2:
  Q1: SELECT IIF([MyColumn]="Yes","Order Confirmed","Order Not Confirmed") AS X FROM MyTable;
  Q2: SELECT * FROM Q1 WHERE X="Order Confirmed";
  Poiché l'espressione IIf nella query Q1 non può essere ottimizzata, anche la query Q2 non potrà essere ottimizzata. Infatti se un'espressione non ottimizzabile è nidificata all'interno di una sottoquery, l'intera query non potrà essere ottimizzata.
  
  Di seguito è riportato un metodo alternativo per costruire la query in questione:
  Q1: SELECT * FROM MyTable WHERE MyColumn = "Yes";
  Se le espressioni sono necessarie nell'output, tentare di inserirle in un controllo di maschera o di report. Ad esempio, è possibile modificare la query precedente in una query con parametri che chiede all'utente di specificare il valore di MyColumn, basando quindi una maschera o un report su tale query. Nella maschera o nel report sarà quindi possibile aggiungere un controllo calcolato che visualizza "Hello" o "Goodbye" a seconda del valore di MyColumn.
  
  Costruire la query come mostrato di seguito:
  PARAMETERS [Per visualizzare ordini confermati, specificare Sì. Per visualizzare ordini non confermati, specificare No.] Text;
  SELECT *
  FROM MyTable
  WHERE MyColumn = [Per visualizzare ordini confermati, specificare Sì. Per visualizzare ordini non confermati, specificato No.];
  Nel controllo calcolato della maschera o del report, digitare:
  =IIF([MyColumn]="Yes","Order Confirmed","Order Not Confirmed")
• Quando si raggruppano i record in base ai valori in un campo collegato, specificare Raggruppa per il campo presente nella stessa tabella del campo di cui si sta calcolando il totale, ovvero nel quale si sta calcolando un'aggregazione. Se ad esempio nel database di esempio Northwind si crea una query che calcola i totali del campo Quantità nella tabella Dettagli sugli ordini e i valori vengono raggruppati in base al campo IDOrdine, si raccomanda di specificare Raggruppa per il campo IDOrdine nella tabella Dettagli sugli ordini. Se si specifica Raggruppa per il campo IDOrdine nella tabella Ordini, Microsoft Access dovrà unire tutti i record e quindi eseguire l'aggregazione, anziché eseguire l'aggregazione e in seguito unire solo i campi necessari.
  
  Per aumentare la velocità, utilizzare Raggruppa in più campi possibili. In alternativa, utilizzare la funzione First, dove appropriata.
  
  Se una query di totali include un join, considerare la possibilità di raggruppare i record in una query da aggiungere a un'altra query che esegue il join. In questo modo sarà possibile migliorare le prestazioni in alcune query.
• Evitare criteri di query troppo restrittivi per i campi calcolati e non indicizzati, dove possibile, utilizzando invece espressioni criterio ottimizzabili.
• Se si utilizzano dei criteri per limitare i valori di un campo utilizzato in un join tra tabelle con relazione uno a molti, verificare se la query viene eseguita più rapidamente con i criteri inseriti sul lato "uno" o sul lato "molti" del join. In alcune query è possibile ottenere prestazioni superiori aggiungendo i criteri al campo sul lato "uno" del join anziché sul lato "molti".
• Indicizzare i campi utilizzati per l'ordinamento.
• Se i dati non vengono modificati spesso, utilizzare query di creazione di tabelle per generare tabelle dai risultati della query. Utilizzare le tabelle così ottenute anziché le query stesse come base per maschere, report o altre query e aggiungere gli indici rispettando le linee guida riportate sopra.
• Evitare l'utilizzo di funzioni di aggregazione sui domini, quali la funzione DLookup per accedere ai dati da una tabella non presente nella query. Le funzioni di aggregazione sui domini sono specifiche di Microsoft Access, il che significa che il modulo di gestione di database Jet non sarà in grado di ottimizzare le query che utilizzano tali funzioni. Aggiungere invece alla query la tabella a cui la funzione stava accedendo oppure creare una sottoquery.
• Se si sta creando una query a campi incrociati, utilizzare le intestazioni di colonna fisse quando possibile.
• Utilizzare gli operatori Between...And, In e = nei campi indicizzati.
• Per le query di aggiornamento collettive che utilizzano origini dati ODBC, ottimizzare le prestazioni sul server impostando la proprietà FailOnError a Sì.

Per ulteriori informazioni sull'ottimizzazione delle prestazioni Microsoft Access 2000, scegliere Guida in linea di Microsoft Access dal menu ?, digitare ottimizzazione delle prestazioni nella casella dell'Assistente di Office o della Ricerca libera, quindi scegliere il pulsante Cerca per visualizzare l'argomento.
Per ulteriori informazioni sull'utilizzo degli indici, fare clic sul numero dell'articolo della Knowledge Base riportato di seguito (gli articoli con prefisso "Q" contengono informazioni in inglese):