Query Engine Components

The query engine has eight major components:

• Parser/Binder The parser/binder parses the SQL statement that defines a query and binds the names referenced in the query to fields in the underlying tables. The parser/binder first checks the query for proper syntax. If there are any syntactical problems with the query, an error is returned. Next, the parser/binder converts the SQL string into the internal query-object definition format in which queries are stored.
  
  
• Folder The folder moves expressions “up” the query tree. The top of the query tree, or root, is the final result set. By moving expressions to the top of the tree, the expressions don’t have to be computed against records that may be discarded.
  
  
• Splitter The splitter splits conglomerate QueryDef objects into their discrete components. The conglomerate query objects are then removed from the tree. This operation is performed on the query tree starting from the bottom and moving in reverse order up to the root.
  
  
• Flattener The flattener takes the output of the splitter and combines joins, ORDER BY clauses, and WHERE clauses as much as possible.
  
  
• 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.

• Remote Post-Processor The remote post-processor determines how much of a query can be sent to an ODBC back-end for processing by the server. The goal is to send as much of the query as possible to the server for processing. The remote post-processor walks through the query tree from the bottom up, marking subtrees that can be sent to the server. As subtrees are found, they are marked as “remoteable.” Finally, SQL strings are generated for the remote queries.
  
  
• Post-Processor The post processor takes the query in its current compiled form and moves it to a new, cleaner, smaller data-execution structure.
  
  
• Join Processor The join processor executes the compiled query. It uses the join strategy that was chosen by the optimizer to return the desired result set.