Making SQL queries run faster


Queries are very useful for finding information and for making changes to large amounts of data. They can be slow on a large database. Try these techniques to make them run more quickly.

Query Design

A complicated topic but the basic guideline is to process as little data as possible:

  • Only include the fields that you need.
  • Only include the records that you need.

Double-clicking on the asterisk in the Query Designer to select all fields is quick and easy. You might do it thinking that the extra fields will be useful at some later stage in the design. Resist the temptation and take care to pick just the fields that you want.

Compact

Access databases contain a lot of wasted space which grows every time that you add, edit, or delete a record. Select Compact Database from Database Utilities on the Tools menu to reclaim the wasted space in all the components of the database.

The process moves data around on disk and may take several minutes on a large database. Nobody can view the data whilst the compaction is running so you should make sure that you are the only user before starting. If another user has the database open then you'll see a warning message and compaction will not start.

Indexes

Indexes allow the SQL to find records more quickly. You should create an index for every fields which you use in any of these situations:

  • linking two tables together
  • selecting records
  • sorting records into order

Use the Design View of a table to create an index.

[Table Designer]

Wild cards

Be careful when using a wild card in a query. If the target of the SQL selection criterion ends with an asterisk like this:

Surname LIKE 'F*'

then Access will be able to use an index to find all entries starting with 'F'.

If the wild card is anywhere other than at the end of the expression then Access will not be able to use an index and comparisons like these will be very slow:

Surname LIKE '*F'
Surname LIKE '??F'

No index can help you look up all entries ending with 'F' or whose third letter is 'F' so Access must fall back on a brute force search through the entire table.

Optimiser

Access includes a Query Optimiser which works out the most effective strategy for retrieving the records. The information that the Optimiser uses can get out of date and this might make the Optimiser choose the wrong strategy, especially if many records have been added or changed. Opening the query in Design View and saving it will update the statistics.


Hints & tips

The textbox class in Visual FoxPro 9 has a new Autocomplete property which shows the user the previous values that have been entered in that textbox.
Autocomplete in VFP 9

Your Access database will look more impressive if you add custom toolbars...
Custom toolbars

FoxPro has always had functions to read and write files at a low level...
Foxpro low level file functions

More...
More pages of hints and tips for users of Microsoft FoxPro and Access databases.

Site Map