Michael Mee
Jet Program Manager, Microsoft Corporation
Created: January 1995
This article describes techniques for creating client-server applications and offers tips for optimizing performance. It was written to accompany the VBITS '94 talk on "Visual Basic's Database Programming Interface." This article contains a greater level of detail than there will be time to cover in the talk; however, the accompanying talk also covers general database programming tips that are not addressed in this article.
Much of the material found in this paper is derived from a manual in the Microsoft® Access 2.0 Developer's Toolkit. Other sections of those manuals provide similarly useful information about the database access objects (DAOs) found in Microsoft Visual Basic® and Access.
Note All the material in this paper assumes that you're using Jet 2.0—that is, the Visual Basic Compatibility Layer has been installed.
Prerequisites for understanding this article include advanced database coding skills and intermediate client-server knowledge.
There are three ways to connect to a server using Microsoft® Visual Basic®. You can:
This section describes each of these methods and their time and memory requirements on both the client and the server.
The most common and most efficient way to access data on a server is to attach tables from the server. With few exceptions, you can use attached tables in your application just as you would Microsoft Jet tables. Microsoft Jet automatically connects to the server when you use an attached table.
Microsoft Jet stores field and index information for the tables you attach, which improves performance when you open the tables. Note, however, that you must reattach remote tables if you make changes to fields or indexes on the server.
If your server supports the creation of structured query language (SQL) views, you can attach them to your local database. Microsoft Jet treats an attached view exactly like an attached table with no indexes. The processing defined in a view is always performed by the server, no matter what is executed locally.
If your server allows you to update data using views, you need to tell Microsoft Jet which field or fields uniquely specify a record returned by the view by creating an index specification on the attachment. Although not actually an index, this specification enables Microsoft Jet to create an updatable dynaset on the view, which can be used by forms and queries.
For example, suppose you attach an SQL view named SeptOrdersView that returns a subset of records in the remote Orders table, and you name the attachment SeptemberOrders. The OrderID field is still unique within the view, so you would run a data-definition query (not a pass-through query) by executing the following SQL statement:
CREATE UNIQUE INDEX index1 ON SeptemberOrders (OrderID)
This doesn't create an index on the server, nor does it take any extra space in your local database. It simply tells Microsoft Jet that the OrderID field is the primary key of the attached view.
There are three ways to connect to a server using Microsoft® Visual Basic®. You can:
This section describes each of these methods and their time and memory requirements on both the client and the server.
The most common and most efficient way to access data on a server is to attach tables from the server. With few exceptions, you can use attached tables in your application just as you would Microsoft Jet tables. Microsoft Jet automatically connects to the server when you use an attached table.
Microsoft Jet stores field and index information for the tables you attach, which improves performance when you open the tables. Note, however, that you must reattach remote tables if you make changes to fields or indexes on the server.
If your server supports the creation of structured query language (SQL) views, you can attach them to your local database. Microsoft Jet treats an attached view exactly like an attached table with no indexes. The processing defined in a view is always performed by the server, no matter what is executed locally.
If your server allows you to update data using views, you need to tell Microsoft Jet which field or fields uniquely specify a record returned by the view by creating an index specification on the attachment. Although not actually an index, this specification enables Microsoft Jet to create an updatable dynaset on the view, which can be used by forms and queries.
For example, suppose you attach an SQL view named SeptOrdersView that returns a subset of records in the remote Orders table, and you name the attachment SeptemberOrders. The OrderID field is still unique within the view, so you would run a data-definition query (not a pass-through query) by executing the following SQL statement:
CREATE UNIQUE INDEX index1 ON SeptemberOrders (OrderID)
This doesn't create an index on the server, nor does it take any extra space in your local database. It simply tells Microsoft Jet that the OrderID field is the primary key of the attached view.
In many applications, you'll use both ordinary Microsoft Jet queries (that is, queries optimized by the Microsoft Jet database engine) based on attached remote tables and pass-through queries. With an ordinary query, Microsoft Jet compiles and runs the query, combining the power of the server with the capabilities of the Jet database engine. With a pass-through query, you enter a SQL statement that Microsoft Jet sends directly to the server without stopping to compile the query.
Pass-through queries offer the following advantages over other queries:
On the other hand, pass-through queries do have several disadvantages:
In some environments, your access to server data is limited to a set of server-based stored procedures. All data requests and updates are carried out through these stored procedures; you have no direct access to the remote tables. In such an environment, you must use pass-through queries exclusively. If you don't need to update data and only want to retrieve data, you can create a pass-through query for each stored procedure you want to call. You can then base other queries and forms on these queries as if they were attached tables.
However, if you need to update data in this type of environment, you must collect the user's input locally and then execute a pass-through query to send the data to the server, calling the appropriate stored procedure. Here are two approaches you might use:
In either case, you need to write code that constructs a pass-through query using the name of the stored procedure and the values to be supplied.
The third way to connect to a server is to access it directly in code by using OpenDatabase with an ODBC connection string. This approach has several disadvantages, however. Although it's possible to directly open snapshots and dynasets on a remote database, this is always much less efficient than using attached tables. When you attach remote tables, the Jet database engine caches a great deal of information about these tables locally, including field information, index information, and server capabilities. In contrast, when you access the remote tables directly, Microsoft Jet has to ask the server for this information every time you run a query.
For best performance, don't access the server directly (except when preconnecting, as described in the "Preconnecting" section below). Using attached tables and pass-through queries is faster and simpler and provides more functionality. Pass-through queries don't have any of the disadvantages of this type of direct server access; because Microsoft Jet doesn't interpret pass-through queries, it doesn't need any field or index information.
Establishing a connection takes time and memory on both the client and the server. This section describes several ways you can limit connections and thus reduce the resources your application uses.
Using the connection-management capabilities of the Jet database engine, you can log a user onto a server when your application starts without waiting until the first form opens. Microsoft Jet caches the connection and authentication information, and reuses them as needed.
One way to preconnect is to create a form to gather user and password information, construct a connection string containing the values, and use OpenDatabase. For example, the following procedure connects to a server called MyServer and opens a database:
Sub PreConnect (UserName As String, Password As String)
Dim MyDB As Database, ConnStr As String
ConnStr = "ODBC;DSN=MyServer;DATABASE=MyDatabase;"
ConnStr = ConnStr & "UID=" & UserName & ";"
ConnStr = ConnStr & "PWD=" & Password & ";"
Set MyDB = OpenDatabase("", False, False, ConnStr)
MyDB.Close ' Close database but keep connection.
End Sub
If you're not sure which ODBC data source to connect to, you can display a dialog box listing all registered data sources by using "ODBC;" for the connection string argument. Then you can access the actual connection string using the Connect property, which connects you to the server, as in the following example. You can then use the ConnStr value when you create pass-through queries or attach tables.
Sub PreConnect ()
Dim MyDB As Database, ConnStr As String
Set MyDB = OpenDatabase("", False, False, "ODBC;")
ConnStr = MyDB.Connect
MyDB.Close ' Close database but keep connection.
End Sub
In both examples, the Close method doesn't disconnect from the server—instead, Microsoft Jet caches the connection internally.
Some servers support powerful connections that can handle multiple partially completed queries at the same time (ORACLE Server is one example). Other servers provide connections requiring fewer resources, but each connection can process only one query at a time (Microsoft SQL Server and Sybase® SQL Server™ are examples). However, regardless of the server's capabilities, the Jet database engine allows multiple queries and manages your connections automatically.
If your server supports multiple queries on a single connection, you generally won't have to worry about connection usage because Microsoft Jet uses just one connection to the server for the entire application. However, if your server doesn't support such queries, you should try to reduce the number of connections your application requires. This section describes ways to limit your use of connections.
If your server uses multiple connections to process more than one query at the same time, you can reduce connection use by limiting dynasets to 100 records. A dynaset containing 100 or fewer records requires only a single connection. In contrast, a dynaset containing more than 100 records requires two connections: Microsoft Jet uses one connection to fetch the key values from the server and another to fetch the data associated with those keys for the records visible on screen. Other dynasets can share the second connection but can't share the first, because not all key values may have been retrieved from the server.
As an example, three dynasets containing more than 100 records each require a total of four connections. When all of the key values for any of these dynasets have been fetched, or when the dynaset is closed, the corresponding connection is released. In contrast, three dynasets containing 100 or fewer records each require only one connection.
Another way to reduce the number of connections is to close connections your application is no longer using. You can cause a recordset to finish execution and thereby close connections by doing one of the following:
Dim MyRS As Dynaset
Data1.RecordSource = "SELECT ... FROM ..."
Data1.Refresh
Set MyRS = Data1.Recordset.Clone
MyRS.MoveLast
MyRS.Close
Although they reduce connection usage, these techniques cause forms to open more slowly, and you may want to temporarily change the cursor to an hourglass. This isn't recommended if your recordsets are very large.
Regardless of the type of server you're using, if someone is running your application but hasn't taken any action for a long time, you may want to close connections to the server. By default, connections are closed after 600 seconds (10 minutes) of idle time. You can change this interval by setting the ConnectionTimeout entry in the [ODBC] section of the initialization file (VB.INI or APPLICATION.INI) to the number of seconds you want to wait before closing idle connections. Microsoft Jet closes connections even if data controls and forms displaying remote data are still open, and then automatically reconnects when the connection is needed again.
Even if the idle time has expired, Microsoft Jet cannot close a connection if:
In addition to functional differences between local and remote tables, there are often considerable performance differences. Elements of your application that performed acceptably with local data may be significantly slower when the data is on a server, causes too much network traffic, or uses excessive server resources. This section discusses techniques you can use to improve the performance of client/server applications. Additional information on optimizing is included in Chapter 6, "Writing SQL Queries."
The best way to improve the performance of queries on remote data is to have the server execute as much of the query as possible. Microsoft Jet attempts to send the entire query to the server but evaluates locally any query clauses and expressions that aren't supported by servers in general or by your server in particular. Some information about server capability is available when you attach a remote table.
If a query contains expressions that can't be evaluated by your server, more or less of the query is evaluated locally, depending on where the expressions occur. Queries with such expressions in the SELECT clause are still evaluated on the server, unless they occur in a totals query, a DISTINCT query, or a UNION query, in which case they are evaluated locally. Such expressions in other clauses (for example, WHERE, ORDER BY, and GROUP BY clauses) cause at least part of the query to be evaluated locally.
The following section describes how to design queries that maximize the use of the server and thereby improve query performance.
If possible, don't design queries that use functionality not generally supported by servers. The following sections identify some areas of functionality that aren't generally supported and others that usually are.
Functionality not supported by most servers
Most servers don't support the following:
Functionality supported by some servers
Servers differ in certain areas of functionality. Some servers support the following:
If the operation is supported, it's sent to the server for processing; otherwise, it's performed locally.
If a query's SQL statement includes a WHERE clause that the server can't evaluate completely, Microsoft Jet splits the clause into its conjuncts (linked by AND) and sends as many conjuncts to the server as possible. For example, suppose you run the following query:
SELECT field1, field2, field3
FROM table1
WHERE (field1 = 100 OR field1 = 200)
AND IIF(field2 = 0, field4, field5) > 100
AND field3 LIKE "A*"
Microsoft Jet sends the following query to the server and evaluates
IIF(field2 = 0, field4, field5) > 100
locally on the records returned:
SELECT field1, field2, field3
FROM table1
WHERE (field1 = 100 OR field1 = 200)
AND field3 LIKE "A*"
If you need to use a WHERE clause restriction that your server can't process, you can limit the amount of data that Microsoft Jet requests by providing additional restrictions that your server can process. For maximum efficiency, these restrictions should involve fields indexed on the server.
If expressions in your queries contain domain aggregate functions and other elements specific to Visual Basic (such as IIf and Choose), they can't be completely executed by a server. If an expression using one of these elements involves remote data fields, Microsoft Jet executes the expression locally and retrieves all necessary data from the server. If such an expression involves only constants and query parameters, however, Microsoft Jet evaluates it just once and sends the result to the server as a query parameter. Whenever possible, you should structure your queries to take advantage of this optimization by avoiding references to remote fields in expressions that can't be evaluated by the server.
As an example, suppose you have a remote Tasks table with a Status field containing code numbers (1 means unfinished and 2 means completed). The following query requires a user to supply a value of 1 or 2, which isn't very intuitive:
SELECT * FROM Tasks WHERE Status = [What Status?]
In contrast, the next query allows the user to supply Unfinished or Complete when prompted:
SELECT * FROM Tasks
WHERE [What Status?] = IIf(Status=1,'Unfinished','Complete')
However, because the IIf function uses the remote Status field, Microsoft Jet sends the first part of the statement (SELECT * FROM Tasks) to the server and evaluates the restriction on each record.
The following query also allows the user to supply words instead of numbers:
SELECT * FROM Tasks
WHERE Status = IIf([What Status?]='Unfinished',1,2)
However, because the return value of the IIf function is effectively constant, Microsoft Jet evaluates it once locally and sends the following statement to the server, supplying this part of the expression as a parameter value:
SELECT * FROM Tasks WHERE Status = ?
This method is more efficient and reduces network traffic.
To perform a join between local and remote tables, or a heterogeneous join, Microsoft Jet either:
Microsoft Jet uses a remote index join only if the remote field being joined is indexed and if the local table is considerably smaller than the remote table. For example, to join a local table called Employees containing 10 records and a remote table called Tasks containing 50 records on the EmployeeID field, Microsoft Jet retrieves the 50 records from the Tasks table on the server and processes the join locally. However, if the remote table contained 1000 records, Microsoft Jet would send 10 of the following queries to the server, supplying each EmployeeID value from the local Employees table as a parameter value:
SELECT EmployeeID, TaskID
FROM Tasks
WHERE EmployeeID = ?
This is generally much faster than retrieving all 1000 records.
You should always index the remote join field to enable Microsoft Jet to perform a remote index join when appropriate. Regardless of whether or not Microsoft Jet can perform a remote index join, you can improve the performance of a query that uses a heterogeneous join by supplying additional restrictions on remote fields. Microsoft Jet sends these restrictions to the server, limiting the amount of data requested for the join.
Probably the simplest way to improve the performance of updates and deletions—especially if your remote table has many fields—is to add a version field (sometimes called a timestamp) to the remote table. A version field is maintained by the server, and its value automatically changes each time the record is updated. Typically, you can't read the contents of a version field, but its behavior enables Microsoft Jet to detect changes to records.
When updating or deleting a record, Microsoft Jet checks to see if the version field has changed; if it has, the update or deletion would overwrite another user's changes, so it's canceled. If the table doesn't have a version field, Microsoft Jet compares all the old field values with their current values to determine if data has changed. This is less efficient and generates more network traffic. Moreover, when Microsoft Jet checks values, in certain cases data that hasn't changed will appear as if it had, either because of inaccuracies inherent in comparing floating-point numbers, or because Memo and OLE Object fields aren't compared at all.
To add a version field to a remote table, you can use the ALTER TABLE statement. For example, the following command adds a version field to a SQL Server table:
ALTER TABLE RemoteTable ADD VersionCol TIMESTAMP
You can run this statement as a pass-through query in Visual Basic. However, you must reattach the remote table to inform Microsoft Jet of the new field's existence. For more information on the ALTER TABLE statement, search Help for ALTER TABLE (SQL).
Note Not all servers support version fields. To see if yours does, check the server's documentation.
When you design a client/server application, you can change its performance by adjusting the following settings in the [ODBC] section of the initialization file (VB.INI or APPLICATION.INI):
If queries on remote data are taking too long to complete, set this option to 1 to inspect the SQL statements being sent to the server. If some of the statement's clauses or restrictions aren't being sent, you could be using functionality provided by the Jet database engine but not supported by the server.
If your application doesn't use Microsoft Jet security, you should set this entry to 0 to prevent Microsoft Jet from attempting to log on to the server using the default user name Admin. On some servers, a failed logon can take time and slow the server down.
These options determine whether Microsoft Jet inserts or updates data in all fields (including inserting Null values and updating unchanged fields). If you don't have triggers or defaults on your tables, setting these options to 1 can speed up insertions and updates marginally.
If you use parameterized queries and aren't concerned about the number of connections you use, you can set this option to 1. This may use more connections, but it marginally improves the speed of requerying operations.
The Microsoft Developers Network CD-ROM has many articles of interest to database programmers. Some of these are also available in other forms directly from Microsoft (often from one of their download services, including the Internet server at FTP.MICROSOFT.COM). These include the following:
Black, Neil, and Stephen Hecht. "Jet Database Engine ODBC Connectivity." (MSDN Library Archive, Backgrounders) This describes in great detail how Microsoft Jet uses ODBC to retrieve server data. Required reading for anyone using DAO to write significant server applications.
Lambert, Colleen. "ODBC: Architecture, Performance, and Tuning". This paper provides a good overview of how ODBC works and addresses performance issues in a realistic and useful fashion.
Nelson, Greg, and Roger Jennings. "A Client-Server Application: From Concept to Reality." (MSDN Library Archive, Conference and Seminar Papers)
Some books are also starting to appear covering relevant topics:
Watterson, Karen. Visual Basic Database Programming. Published by Addison-Wesley. This is a good overview of all aspects of database programming from Visual Basic.
Free support for all the products detailed in this paper is available on CompuServe®.
DAO/Jet/Visual Basic support is available from the MSBASIC forum of CompuServe, Section 3, "Data Access Objects," and Section 4, "The Data Control." If you're willing to move your frame of reference to Microsoft Access, you will find excellent support for specific database questions in the MSACCESS forum. Unlike the Visual Basic support engineers, the Access engineers live and breathe databases and hence sometimes are able to answer questions that stump the Visual Basic support folks. Don't expect Access support to know anything about Visual Basic's data control, however.
Paid support is also available direct from Microsoft. Call (800) 936-3500 for details. You may also be able to find a local Microsoft Solution Provider (SP) that can help you. Call (800) SOL-PROV for details or referrals to a local SP.
The Visual Basic Compatibility Layer (VBCL) is included both in the Microsoft Access Distribution Kit and the Office Developers Kit. It can also be downloaded from the usual Microsoft download points.
Note that with the VBCL you will also need a copy of the Jet 2.0 engine, which is currently available either with Microsoft Access 2.0 or the Microsoft Access Distribution Kit. Given that you'll need Access 2.0 to set up some of the new Jet 2.0 features (such as cascading relationships or pass-through queries), buying Access 2.0 is probably the best way to obtain Jet 2.0.