How to Avoid Common Database Programming Mistakes in VB (115986)

MORE INFORMATION

Avoid Redundant Tables

Frequently, a second copy of an existing table is not joined to anything. This slows down the query process and causes unwanted records to appear in query results.

Avoid Expressions in Query Output

By placing expressions in the output column of a query, you may cause optimization problems. For example, consider a situation where one query is used as input to another query as in this example:

   Query1:  Select IIF([MyColumn]="H", "Hello", Goodbye") AS X
   Query2:  Select * From Query1 Where X="Hello";
				

This causes an optimization problem because the Microsoft Access database engine cannot optimize the IIF() expression, so it cannot optimize Query2. Sometimes expressions get buried so far down in a query tree, that you may not even realize they're there.

Here's a more optimal way to write this query:

   Query1: Select * From mytable Where mycolumn = "H";
				

Place Group By Column in the Same Table as Aggregate

When joining two tables, make sure the Group By column (Column1) and the column in the aggregate (Sum, Count, and so on) both come from the same table.

Group By as Few Columns as Possible

When creating queries that display totals using the Microsoft Access database engine, place as few columns as possible in the Group By clause. The more columns the query has to group by, the longer it will take.

Place the Group By Before the Join

If possible, use the Group By with a single table, and then join that table to another table rather than joining the tables and doing the Group By in the same query. Here are two examples:

Example One:

   ' Enter the following three lines as one, single line of code:
   Select table1.[field1 ID], Count(table1.[field2 ID]) AS [CountofField2]
      From table2 INNER JOIN table1
      ON table2.[field3 ID] = table1.[field3 ID]
      Group By table1.[field1 Name];
				

Example Two:

   ' This example uses two separate queries:

   ' Query1:
   Select table2.[field1 ID] From table2 Group By table2.[field1 ID];

   ' Query2:
   ' Enter the following three lines as one, single line of code:
   Select table1.[field3 ID], Count(table1.[field2 ID]) AS [CountofField2]
      From Q1 INNER JOIN table1 ON Q1.[field3 ID] = table1.[field3 ID]
      Group By table1.[field3 Name];
				

Index the Join Column from Both Tables

When joining tables, index the fields on both sides of a join. This can speed query execution by allowing more sophisticated join strategies such as Index Join and Index-Merge Join.

Index Fields as Much as Possible

When in doubt, index. If the frequency of updating the database is low, place an index on all columns that will be used as join columns or that will be used in a restriction. With the Rushmore query optimization in the Microsoft Access version 2.0 database engine, the query engine is able to take advantage of multiple indexes on a single table.

Use COUNT(*) Instead of COUNT([Column Name])

To determine the number of records, use COUNT(*) rather than COUNT([Column Name]). COUNT(*) is executed much faster.

These are just some tips to help you in the design and programming phase of creating your program. For information on optimizing your database design, please see the following article in the Microsoft Knowledge Base: