Meer informatie

Query Optimizer voor de Microsoft Jet-database-engine

De Jet-database-engine bevat verschillende onderdelen, maar het belangrijkste (en meest complexe) onderdeel voor query's is de Optimizer. De Optimizer is op kosten gebaseerd. Dit betekent dat de Optimizer kosten op basis van tijd toewijst aan elke querytaak en vervolgens de minst dure lijst met uit te voeren taken kiest die de gewenste resultatenset oplevert. Hoe langer het duurt om een taak uit te voeren, hoe duurder die taak is.

De Optimizer maakt gebruik van statistische gegevens om te beslissen welke querystrategie moet worden gebruikt. Hieronder ziet u een aantal factoren waarop deze statistische gegevens zijn gebaseerd:

• Het aantal records in een tabel
• Het aantal gegevenspagina's in een tabel
• De locatie van de tabel
• Of er indexen aanwezig zijn
• Hoe uniek de indexen zijn

Op basis van deze statistische gegevens selecteert de Optimizer de beste interne querystrategie voor een bepaalde query.

De statistische gegevens worden telkens bijgewerkt wanneer er een query wordt gecompileerd. Een query wordt gemarkeerd voor compilatie wanneer u wijzigingen aan de query (of de onderliggende tabellen) opslaat en wanneer de database wordt gecomprimeerd. Als een query wordt gemarkeerd voor compilatie, worden de statistische gegevens de volgende keer dat de query wordt uitgevoerd, gecompileerd en bijgewerkt. Het compileren kan een tot vier seconden duren.

Als u een groter aantal records toevoegt aan de database, moet u de query's openen en opslaan om de query's opnieuw te compileren. Als u bijvoorbeeld een query ontwerpt en vervolgens test met een kleine hoeveelheid voorbeeldgegevens, moet u de query opnieuw compileren nadat er extra records aan de database zijn toegevoegd. Wanneer u dit doet, moet u ervoor zorgen dat de optimale queryprestaties worden bereikt wanneer uw toepassing in gebruik is.

Opmerking U kunt geen optimalisatieschema's van de Jet-database-engine bekijken en u kunt ook niet opgeven hoe u de query wilt optimaliseren. U kunt echter wel de Database Documenter gebruiken om te bepalen of er indexen aanwezig zijn en hoe uniek deze zijn.

Klik voor meer informatie over het probleem dat kan optreden bij gebruik van de Database Documenter in Access 2000, op het volgende artikelnummer in de Microsoft Knowledge Base:

Query-timing

Er zijn twee significante tijdmetingen voor een Select-query:

• Tijd om het eerste scherm met gegevens weer te geven
• Tijd om de laatste record te verkrijgen

Als een query maar één scherm met gegevens retourneert, zijn deze twee tijdmetingen identiek. Als een query veel records retourneert, kunnen deze tijdmetingen sterk van elkaar verschillen.

Als de twee metingen identiek zijn wanneer u een Select-query bekijkt in de gegevensbladweergave, ziet u een scherm met gegevens en het totaal aantal records dat is geretourneerd door de query, zoals 'Record 1 van N'. Als het voor de Jet-database-engine sneller is om het eerste scherm met gegevens weer te geven dan om de query te voltooien en vervolgens de laatste record op te halen, ziet u een scherm met gegevens maar geen N in 'Record 1 van N'. De waarde N is leeg totdat de query is voltooid of totdat u naar de laatste record bladert.

Dit gedrag is het gevolg van de selectie door de Jet-database-engine van een van twee mogelijke prestatiestrategieën:

• De query voltooien en vervolgens gegevens weergeven
• Gegevens weergeven en vervolgens de query voltooien

U kunt niet zelf regelen welke strategie er wordt gebruikt. De Jet-database-engine selecteert de meest efficiënte strategie.

Prestatie-analyse

Als u werkt met Microsoft Access 7.0, Access 97, Access 2000, Access 2002 of Access 2003, kunt u de Prestatie-analyse gebruiken om query's in de database te analyseren. Omdat de queryprestatie-analyse nauw is verbonden met de Jet-database-engine, stelt de Prestatie-analyse voor alleen indexen toe te voegen wanneer de indexen daadwerkelijk worden gebruikt door de Jet-database-engine om de query te optimaliseren. Dit betekent dat de Prestatie-analyse prestatietips kan bieden die beter zijn afgestemd op uw database dan de algemene aanbevelingen in de sectie 'Tips voor het verbeteren van de prestaties van query's' hieronder.

Als u de Prestatie-analyse wilt uitvoeren in Access 7.0, in Access 97, Access 2000, Access 2002 of Access 2003, voert u deze stappen uit:

Open het menu Extra en klik op Analyseren en op Prestaties.

Tips voor het verbeteren van de prestaties van query's

Gebruik de volgende tips om de queryprestaties te verbeteren:

• De database comprimeren
  
  Wanneer u de database comprimeert, kunt u query's sneller uitvoeren. Door de database te comprimeren worden de records van de tabel opnieuw ingedeeld zodat de records zich bevinden in aangrenzende databasepagina's die worden gesorteerd door de primaire sleutel van de tabel. Op die manier verhoogt u de prestaties van de sequentiële scans van records in de tabel, omdat nu maar een minimaal aantal databasepagina's hoeft te worden gelezen om de gewenste records op te halen. Nadat u de database hebt gecomprimeerd, voert u elke query uit om de query te compileren zodat elke query beschikt over de bijgewerkte tabelstatistieken.
• Een veld indexeren
  
  Indexeer elk veld dat wordt gebruikt om criteria in te stellen voor de queryvelden en de indexvelden aan beide kanten van een join. Of maak een relatie tussen deze velden. Wanneer u een relatie maakt, maakt de Jet-database-engine een index voor de externe sleutel, als deze tenminste nog niet bestaat. Als dat wel het geval is, gebruikt de Jet-database-engine de bestaande index.
  
  Opmerking De Jet-database-engine optimaliseert automatisch een query die een Access-tabel op de vaste schijf samenvoegt met een ODBC-servertabel als de Access-tabel klein is en als de samengevoegde velden worden geïndexeerd. In dit geval worden de prestaties verbeterd doordat Access alleen de vereiste records opvraagt bij de server. Zorg ervoor dat tabellen die u vanuit verschillende bronnen samenvoegt, worden geïndexeerd voor de joinvelden.
• Het kleinst mogelijke gegevenstype selecteren
  
  Wanneer u een veld in een tabel definieert, selecteert u het kleinst mogelijke gegevenstype voor de gegevens in het veld. Zorg er ook voor dat velden die u wilt gebruiken in joins, dezelfde of compatibele gegevenstypen hebben, zoals AutoNummering en Nummer (als de eigenschap Veldlengte is ingesteld op Lange integer).
• Alleen de noodzakelijke velden toevoegen
  
  Wanneer u een query maakt, voegt u alleen de noodzakelijke velden toe. In velden die worden gebruikt om criteria in te stellen, schakelt u het selectievakje Weergeven uit als u die velden niet wilt weergeven.
• De SQL-instructie opslaan als een query
  
  Als de eigenschap Recordbron voor een formulier of rapport is ingesteld op een SQL-instructie, slaat u de SQL-instructie op als een query en stelt u de eigenschap Recordbron in op de naam van de query.
• Berekende velden vermijden
  
  Vermijd het gebruik van berekende velden in subquery's. Als u een query toevoegt die een berekend veld voor een andere query bevat, kan de expressie in het berekende veld zorgen voor lagere prestaties in de query van het hoogste niveau. In het volgende voorbeeld wordt query Q1 gebruikt als invoer voor query Q2:

  Q1: SELECT IIF([MijnKolom]="Ja","Order bevestigd","Order niet bevestigd") AS X FROM MyTable;
  
  Q2: SELECT * FROM Q1 WHERE X="Order bevestigd";

  Aangezien de expressie IIF in Q1 niet kan worden geoptimaliseerd, kan ook Q2 niet worden geoptimaliseerd. Als een expressie die niet kan worden geoptimaliseerd, is genest in een subquery, kan de hele query niet worden geoptimaliseerd.
  
  U kunt de query ook als volgt opbouwen:

  Q1: SELECT * FROM MijnTabel WHERE MijnKolom = "Ja";

  Als er expressies vereist zijn in de uitvoer, probeert u de expressies te plaatsen in een besturingselement van een formulier of rapport. U kunt bijvoorbeeld de vorige query wijzigen in een parameterquery die de waarden van MijnKolom opvraagt, en vervolgens een formulier of rapport baseren op de query. Aan het formulier of rapport kunt u vervolgens een berekend besturingselement toevoegen met 'Hallo' of 'Tot ziens', afhankelijk van de waarde in MijnKolom.
  
  Bouw de query als volgt op:

  PARAMETERS [Geef Ja op als u bevestigde orders wilt zien. Geef Nee op als u onbevestigde orders wilt zien.] Text;
  
  SELECT *
  
  FROM MijnTabel
  
  WHERE MijnKolom = [Geef Ja op als u bevestigde orders wilt zien. Geef Nee op als u onbevestigde orders wilt zien.];

  In het berekende besturingselement op het formulier of rapport typt u:

  =IIF([MijnKolom]="Ja","Order bevestigd","Order niet bevestigd")

• Group By opgeven
  
  Wanneer u records groepeert op de waarden in een gekoppeld veld, geeft u Group By op voor het veld dat zich in dezelfde tabel bevindt als het veld waarvoor u het totaal berekent. Als u bijvoorbeeld in de voorbeelddatabase Noordenwind.mdb een query maakt die het totaal berekent voor het veld Hoeveelheid in de tabel Detailgegevens orders en vervolgens groepeert op OrderID, kunt u Group By opgeven voor het veld OrderID in de tabel Detailgegevens orders. Als u Group By opgeeft voor het veld OrderID in de tabel Orders, moeten eerst alle records worden samengevoegd en moet vervolgens het totaal worden berekend in plaats van dat het totaal wordt berekend en vervolgens alleen de vereiste velden worden samengevoegd.
  
  Gebruik Group By voor meer snelheid en zo min mogelijk velden. Of gebruik zo mogelijk de functie Eerste.
  
  Als een totaalquery een join bevat, kunt u de records beter groeperen in één query en deze query vervolgens toevoegen aan een afzonderlijke query die de join uitvoert. Wanneer u dit doet, zijn de prestaties van sommige query's mogelijk beter.
• Beperkende querycriteria vermijden
  
  Vermijd zo mogelijk het gebruik van beperkende querycriteria voor berekende en niet-geïndexeerde velden. Gebruik criteria-expressies die u kunt optimaliseren.
• Queryprestaties testen in een veld dat wordt gebruikt in een join tussen tabellen
  
  Als u criteria gebruikt om de waarden te beperken in een veld dat wordt gebruikt in een join tussen tabellen met een een-op-veelrelatie, test u of de query sneller wordt uitgevoerd als u deze aan de een-kant of aan de veel-kant van de join plaatst. In sommige query's kunt u de snelheid verhogen door de criteria toe te voegen aan het veld aan de een-kant van de join in plaats van aan de veel-kant.
• Sorteervelden indexeren
  
  Indexeer de velden die u gebruikt om te sorteren.
• Tabelmaakquery's gebruiken om tabellen te maken
  
  Als uw gegevens zelden worden gewijzigd, gebruikt u tabelmaakquery's om tabellen te maken op basis van uw queryresultaten. Gebruik de resulterende tabellen in plaats van query's als de basis voor uw formulieren, rapporten of andere query's. Zorg ervoor dat u indexen toevoegt volgens de richtlijnen in dit artikel.
• Het gebruik van statistische domeinfuncties vermijden
  
  Vermijd het gebruik van statistische domeinfuncties, zoals de functie DLookup om toegang te krijgen tot gegevens uit een tabel die niet is opgenomen in de query. Statistische domeinfuncties zijn specifiek voor Acces. Dit betekent dat de Jet-database-engine query's die statistische domeinfuncties gebruiken, niet kan optimaliseren. Voeg de query in plaats hiervan toe aan de tabel waartoe de functie heeft geprobeerd toegang te krijgen, of maak een subquery.
• Vaste kolomkoppen gebruiken
  
  Als u een kruistabelquery maakt, gebruik dan zoveel mogelijk vaste kolomkoppen.
• Operatoren gebruiken
  
  Gebruik de operatoren Tussen...En, In en = voor geïndexeerde velden.
• Prestaties op de server optimaliseren
  
  Voor query's met een groot aantal updates voor ODBC-gegevensbronnen optimaliseert u de prestaties op de server door de eigenschap FailOnError in te stellen op Ja.