Re: Access unter Citrix - Probleme beim Komprimieren

Hallo Sascha

Henry Habermacher wrote:
in MSDN. Ich glaube fast, wir hatten das hier vor einigen Wochen
wiedermal und dort wurde dann auch ein entsprechender Link zu einem
Whitepaper von MS gepostet, find's aber partout jetzt nicht mehr.

Hat mir doch keine Ruhe gelassen, war ja auch nicht in MSDN oder KB, sondern in der TechNet drin: Microsoft Jet Database Engine Programmer's Guide, Chapter 4.

http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/msjet/jetch04.mspx?mfr=true

Zitat (c) Microsoft, obiger Link:
----
Optimizer

The optimizer is one of the most complex components of the query engine. It uses statistics to determine the most efficient way to execute a query. The optimizer in the Jet database engine is a cost-based optimizer, which means the optimizer assigns a cost to each task and then chooses the least expensive list of tasks to perform that will generate the desired result set. The longer a task takes to perform, the more costly or expensive it is.

The algorithms that the optimizer uses depend on the accuracy of the statistics provided by the underlying engine. For example, the statistics that some ODBC drivers return may not be accurate. This can cause the optimizer to choose a less-than-optimal execution plan. However, if the whole query is sent to the ODBC server for processing (the usual case), the optimizer's execution plan is irrelevant.

In the native Jet database engine, statistics can become out of date over time. Statistics become out of date if transactions are performed and then rolled back, or if your machine is turned off before the database is closed. The problem in the latter situation occurs because the statistics are cached in memory, and shutting off the machine without closing the database doesn't allow the statistics to be written out to disk.

To update the statistics in a Microsoft Jet database, you must compact the database. Compacting the database may also speed up queries because the process writes all the data in tables in contiguous pages, which makes scanning sequential pages much faster than when the database is fragmented.
----

Es ist also so, dass die Statistiken nicht mal während des Betriebs verändert werden, sondern erst beim Komprimieren. Und soweit ich mich erinnere wird nach dem Compact eine QueryDef ebenfalls nicht in den decompiled Zustand versetzt, sondern nur, wenn du die QueryDef neu anlegst oder das SQL Statement veränderst. Dann wird beim nächsten Mal die Query neu durch den Parser/Optimizer geschickt und der Zugriffsplan neu angelegt:


Noch ein Zitat aus der gleichen Quelle:
----
Each time you prepare a request for information, either by sending an SQL statement as an argument to the OpenRecordset method of a Database object or by saving a QueryDef object in your database, Microsoft Jet runs through a complex series of analysis and optimization steps. When you create a QueryDef object, Microsoft Jet performs a parsing phase (reading and interpreting your SQL statements) and an optimization phase (turning that SQL statement into a plan for the most efficient way to retrieve your answer). When you create a permanent QueryDef object, these steps are performed once. You can then execute the saved QueryDef object either to retrieve the answer you need, or to perform the action you requested. For a full discussion of how Microsoft Jet interprets and executes your queries, see "Query Optimization" later in this chapter.
----

Gruss

Henry


--
Microsoft MVP Office Access
Keine E-Mails auf Postings in NGs. Danke.
Access FAQ www.donkarl.com

.

Relevant Pages

  • Re: Access unter Citrix - Probleme beim Komprimieren
    ... Microsoft Jet Database Engine Programmer's Guide, ... The optimizer is one of the most complex components of the query engine. ... uses statistics to determine the most efficient way to execute a query. ... Each time you prepare a request for information, ...
    (microsoft.public.de.access)
  • Need to aggregate t-test stats
    ... My main goal is to "bless" a new database such ... "name" lengths for each country. ... them would have high p-values (seems like a great way to ... statistics obtained from all the different tests and compare ...
    (sci.stat.math)
  • Re: Need to aggregate t-test stats
    ... that "blessing" means that the new database isn't ... "name" lengths for each country. ... them would have high p-values (seems like ... statistics obtained from all the different ...
    (sci.stat.math)
  • Re: SQL Tuning Regarding System CPU Stats
    ... Since the system statistics are used per database ... different system performance stats. ... statistics on the production machines and import those system ...
    (comp.databases.oracle.server)
  • Re: question about query optimizer
    ... >>>go to unknowndatabase.com and click on the white paper link. ... >> Thanks Jim, but that link is broken somehow ... ... We know it isn't true of the Mauve database because it uses more RAM. ... query then the optimizer has to figure out what order to go after the data. ...
    (comp.databases)