Some users find that the intelligence added to their database application by the Jet Engine is more of a hindrance than a help. This criticism is justified since the Jet Engine's optimizations take a certain amount of time for each statement. However, the user benefits in a variety of concrete ways:
1. The Jet Engine optimizes SQL statements that were not well formulated by the application programmer and usually returns its results more rapidly than if there were no optimization.
2. The Jet Engine reformulates SQL statements that are not adapted to the capabilities of the specific server database so that they can be executed by this server database and takes over jobs that the server database cannot perform.
3. In the case of SQL statements that use tables from several data sources within one join, the Jet Engine resolves these statements, optimizes them and transfers the parts to the relevant server databases for execution or executes the necessary jobs itself on a local Microsoft Access database (MDB). Following execution, the Jet Engine puts the pieces of the puzzle back together and returns the result to the application.
Consequently, users will often want to work with the Jet Engine because to do without it would require making major changes to an existing application or would increase programming effort to a degree that would be disproportionate to the value of the solution. Sometimes, however, small changes can already result in a significant improvement in performance.
Before reading a few tips, you should be aware of certain limits: Initially, the response time increases linearly and later on increases exponentially with the size of the result set. Bound controls respond differently to large data sets, with responses ranging all the way to aborting the program and issuing an error message. The mathematical limit for using bound controls is formed by the range of values for the list index (up to 32767); depending on the concrete volume of data, however, memory management already sets a lower limit.
What Is a Large Result Set?
When you issue an SQL query, a larger or smaller set of data is returned in the result for each individual data row (see below under "snapshot or dynaset?") before the query has been fully processed and the time is up.
Therefore, it would be a good idea to calculate the size of the result sets beforehand. A result set of 1500 data rows is already considered to be "large". Try to obtain small data sets. As far as possible, avoid accessing whole tables by using a SELECT statement without a WHERE clause, a "database.opentable" (in VB 3.0) or a "database.createrecordset" with the "dbopentable" parameter (in VB 4.0 or, similarly, in VBA in Microsoft Access). In an SQL statement, address only those columns that you will later need.
From your experience with other applications, you already know that response times basically depend on the RAM (or main memory). The more free RAM you have available, the more fluently the system can toggle between programs and the better the applications can respond. In the same way, a database application can perform better when it attempts to maintain simultaneous access to as few data rows as possible. In each individual data row, only the fields that are required should appear in a SELECT statement. Although "select *" is quickly written, the subsequent results for the application are much slower and can have a significant impact on performance. Consider making MEMO (or LONG) fields visible only on request.
When Is It Advisable to Fill in Lists and Combo Boxes?
On a form, avoid using several combo boxes with a large number of entries that are filled from a data control or from the program with an SQL query. If you are using Visual Basic or VBA for Microsoft Access, if at all possible do not insert lists or combo boxes until they are to be used.
If this is not possible, try not to insert these activities in the Form_Load subroutine but wait at least until the Form_Activate subroutine. If you decide in favor of the latter option, use a flag variable to prevent initialization from being performed more than once (Form_Activate is called almost every time if the focus is set to the form). Afterwards, call Do_Events one or more times to ensure that before the initializations take place, the form is drawn, initially with empty controls.
Specific ways of programming are a question of taste. The example below is only a suggestion for structuring Form_Activate:
Private Sub Form_Activate()
Static active As Integer
If active Then Exit Sub
active = True
DoEvents: DoEvents: DoEvents:
' ...
' Initializations ...
' ...
' In the end set focus to first entry
MyControl.SetFocus
End Sub
Snapshot or Dynaset?
You were already warned above about accessing entire tables. It is more difficult to know when to use dynasets and when it is more convenient to use snapshots. Although snapshots are generally faster (and therefore have the restriction that they cannot be updated), which is also the case for moderately sized result sets (up to one- or two-thousand data rows), dynasets have a more intelligent strategy for highly extensive result sets because they contain only a keyset (i.e., the unique index for each data row that is used to retrieve the entire data row only as necessary) of the result and not the complete data rows.
Nevertheless, do not expect miracles with regard to performance when using dynasets. Time measurements have shown that the strategic advantage of dynasets is not apparent unless they are used for volumes of data for which users have already found the response time to be an inconvenience (more than thirty seconds).
Therefore, base your choice of using a dynaset or a snapshot only on whether you wish to allow updates. If you must work with volumes of data for which the response time using a snapshot is unacceptable, read the sections below.