Some of the strategies for converting a file-server application to client/server are easy to implement. Others may require rethinking or redesigning the way your application works. The concept of limiting the data returned by the server is one of the strategies that may require some extensive work in your conversion process. However, the performance gains are potentially very large, and the more data you have, the bigger the payoff. This optimization strategy applies to both Microsoft Jet and ODBCDirect Workspace objects.
As an example, consider the following code that opens a Recordset on the Orders table:
Set rst = dbs.OpenRecordset("Orders")
In a stand-alone or file-server environment, this command is a perfectly acceptable way of opening a table in a Microsoft Jet database. Because Microsoft Jet reads data at a page level, selecting all fields is no less efficient than selecting a single field.
However, when you open the Orders table on an ODBC server this way, the server starts sending the entire contents of the Orders table to your workstation. This can be a very slow and resource-intensive task.
If you look closely at what your application really needs, you may find that you rarely need all records from a table, or all fields in each record. In a client/server system, you want to return only the data that is absolutely necessary for the part of your application that is currently executing. The less data you request, the less time is spent getting the data. In some cases, you may even set limits on what your users can retrieve. For example, if you give users the ability to do searches and then display what data is found, before retrieving the data, you may want to check the number of records being retrieved. If it’s an extremely large number, you can prompt users to limit searches, or display a message that there may be some delays if they proceed.
The remainder of this section will discuss the three main ways to limit the amount of returned data: