Summary of Tips to Improve Data Access Speed in VB version 3.0

- Professional Edition of Microsoft Visual Basic for Windows,

  version 3.0

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:

ARTICLE-ID: Q109830

TITLE     : How to Speed Up Data Access by Using BeginTrans & CommitTrans

ARTICLE-ID: Q101518

TITLE     : 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 articles in the Microsoft Knowledge Base:

ARTICLE-ID: Q108423

TITLE     : How to Attach an External Database Table to a VB 3.0 Database

TITLE     : 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:

ARTICLE-ID: Q106492

TITLE     : How to Call SQL Stored Procedures from Visual Basic

TITLE     : How to Use Temporary Tables in SQL Server from Visual Basic

TITLE     : PRB: Illegal to Use Find Methods w/ SQL PASSTHROUGH & ODBC DB

TITLE     : 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.

• 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.

• 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.

• 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.

ARTICLE-ID: Q103442

TITLE     : Differences Between the Object Variables in VB Version 3.0

TITLE     : Using Table Objects Versus Dynaset/Snapshot Objects in VB

TITLE     : Limitations of the Data Control in Visual Basic Version 3.0