Article ID: 120172
Article Last Modified on 9/1/2006
APPLIES TO
- Microsoft Visual Basic 3.0 Professional Edition
This article was previously published under Q120172
SUMMARY
This article summarizes the techniques for improving the speed of data access in your database applications. There are four main points that will be covered in this article:
- Transactions
- Attached Tables
- ODBC: DB_SQLPassThrough and ExecuteSQL
- Correct use of the Data Access Object (DAO) Variable
MORE INFORMATION
Transactions
Implementing transactions can be the most significant way to increase the speed of data access in your database application. This technique is ideal for speeding up inserting, updating, appending, or deleting data in a database. In a nutshell, transactions cache a group of operations in memory, then commit them all at once rather than committing each individual operation. They also provide a means for discarding the proposed changes (rollback).
For additional information, please see the following articles in the Microsoft Knowledge Base:
109830 : How to Speed Up Data Access by Using BeginTrans & CommitTrans
For ODBC sources, however, transactions do have their limitations as outlined in the following article:
101518 : Transactions on ODBC Data Sources in Visual Basic Version 3.0
Attached Tables
Using attached tables increases performance most when connecting to ODBC databases such as Microsoft SQL Server, Oracle, and Sybase. With attached tables, information on the table structure is cached in the Microsoft Access database.
This technique increases performance when connecting to the ODBC data source because you eliminate the overhead of Visual Basic querying the ODBC data source for this table structure information.
For additional information, please see the following article in the Microsoft Knowledge Base:
90100 : PRB: Remote ODBC Tables Are Read-Only Without a Unique Index
ODBC: DB_SQLPassThrough and ExecuteSQL
If you do not use attached tables to connect to your ODBC data source, you can use the DB_SQLPassThrough flag when creating recordsets to increase performance. For action queries such as Insert, Delete, and Update that take action rather than returning records, you can use the ExecuteSQL method. By using DB_SQLPassThrough and ExecuteSQL, you increase performance by delegating execution of the SQL query to the client-server database via the ODBC connection. There are some limitations, however, when using DB_SQLPassThrough. Most notably, the recordset returned is Read-Only.
For additional information, please see the following articles in the Microsoft Knowledge Base:
106492 : How to Call SQL Stored Procedures from Visual Basic
115237 : How to Use Temporary Tables in SQL Server from Visual Basic
106111 : PRB: Illegal to Use Find Methods w/ SQL PASSTHROUGH & ODBC DB
103976 : FIX: Invalid Argument Err on Execute Method w/SQL Passthrough
Correct Use of the Data Access Object (DAO) Variable
You can also increase performance by using the correct data access object variable(s) for your needs. To do this, you need to understand the functionality of each of the different DAO variables. Below is a brief outline of the seven main advantages and disadvantages of each of the data access object variables and the data control.
Table Object:
- Provides the fastest way to search for records with SEEK.
- Uses index when performing searches.
- Provides both Read/Write access.
- Cannot use SORT or FILTER, use the Index property instead.
- Opens table for direct access to the base table.
- Changes in records appear immediately as you move through them.
- Cannot be used on ODBC databases or attached tables.
Dynaset Object:
- Provides most flexibility - able to use more than one Table.
- Provides both Read/Write access, and returns set of keys to data.
- With ODBC, Write access requires that you set a unique index.
- Only takes advantage of Indexed fields when needed.
- Can use SORT or FILTER.
- Uses JOIN on two or more tables to create supersets or subsets.
- Reflects changes to the records in a multiuser environment.
Snapshot Object:
- Provides Read-Only access.
- Creates from existing Dynaset or Snapshot
- Cannot execute Edit, Addnew or Update methods.
- Does not support Transaction statements.
- Can use SORT or FILTER.
- Returns all of the selected data - not keys or pointers to the data.
- Looses the currency of the data quickly in multiuser application.
Data Control:
- Easily binds certain controls to it.
- Provides both Read and Write access.
- With ODBC, Write access requires that you set a unique index.
- Does not provide an Index property; use ORDER BY.
- Using the SORT or FILTER property has no effect
- Is subset of Dynaset, not the equivalent.
- Cannot use Parameter Query.
For additional information, please see the following articles in the Microsoft Knowledge Base:
Additional query words: 3.00 Speed fast faster
Keywords: KB120172
