How good is the client/server environment in delivering results to the end user? I guess the answer to that question is "it depends on what you're use to."

Client/server does exactly what minicomputers and mainframes were so good at doing -- delivering results from a centralized processor to a client machine. It's the irony of the 1990's. The only difference is the miniss and mainframes were more stable and have done a better job at delivering results.

The issue here, however, is client/server programming. That is, client side applications interfacing with a server side database. The programming is significantly different and different for each language.

In a recent client/server project using Visual Basic 5, I explored all the techniques currently available for accessing server databases using a DBMS, developing a benchmark program as a preliminary end product. The results were startling.

The target DBMS was SEQUEL 6.5, Microsoft's own DBMS for small to medium size databases. In the client/server world there is one constant. The greater the functionality and ease of connection to a remote database the slower the access of information. This is primarily due to a direct relationship between functionality and system overhead. The greater the functionality, the more layers the request has to pass through before it reaches the database itself. So client/server programming first and foremost is driven by a series of trade-offs.

The ultimate in database access speed is provided by ODBC API (Open Database Connectivity, Application Program Interface). Using ODBC API is an exercise in patience and a challenge to even the best programming minds. Once conquered, it will provide you with greater speed. The sacrifice, however, is significantly reduced functionality. Everything has to be coded.

Fortunately, in Visual Basic 5, the next layer up from ODBC API is called RDO (Remote Data Object). This access approach applies a thin veneer of functionality over ODBC API yielding simplified programming with small reductions in access speed. Of course not all languages have RDO, but most have something similar.

The benchmark programs I wrote to compare the two approaches provided access differences that were less than 5%. Given the added functionality that RDO provided, the 5% difference was a worthwhile trade-off.

Still looking for additional access improvements, however, I examined a second technique widely used in client/server programming -- stored procedures. Stored procedures allow you to store pre-compiled SQL calls (and any additional programming you might want to include) in a procedure on the server side. Pre-compilation saves a lot of time. In addition, you don't need to use dynamic cursors (called record pointers on the AS400) with stored procedures. This really saves on overhead. The pre-compilation and absence of cursor overhead translated to significant increases in access time -- to the tune of 4 times over straight ODBC API.

In addition to pre-compilation, stored procedures just run faster because they live on the server which is generally the biggest processing environment in the network. Plus, you have less network traffic because the SQL calls are taking place on the server, not on the client.

All in all, client/server access using some form of ODBC API process coupled with stored procedures proved to be the best approach for maximizing data access from a server side database.

How does client/server access compare to desktop databases? There is no comparison. Desktop databases are much faster, providing access times more than double that obtained from my ODBC connections.

Within the client/server world, however, the closer you get to low level API functions the faster the speeds, the greater the programming complexity, the greater the management requirements and the smaller the functionality.

RR 2 Box 168, Jericho, VT 05465, 802-899-3115 Info@lavalleeccs.com