Home About us Development Training Support |
Making SQL queries run fasterQueries 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 DesignA complicated topic but the basic guideline is to process as little data as possible:
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. CompactAccess 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. IndexesIndexes allow the SQL to find records more quickly. You should create an index for every fields which you use in any of these situations:
Use the Design View of a table to create an index.
Wild cardsBe 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. OptimiserAccess 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.
More...
|