Visual Basic: Tips and Tradeoffs in Data Access Performance
Robert Eineigl
March 10, 1995
When Visual Basic version 3.0 arrived, with its rich set of data access capabilities, the increased power and flexibility also provided, in terms of performance, more rope to hang yourself with (pun intended!). When Visual Basic 3.0 became the predominant platform for building clients for mission-critical, vertical data access systems, many programmers experienced the joys of system analysis and database management for the first time.
As easy access to information becomes more essential to remaining competitive, it also escalates expectations and makes system users increasingly impatient with unresponsive client applications.
This article surveys the many choices available to the programmer writing data-centric applications in Visual Basic, and offers recommendations aimed at maximizing the perceived responsiveness and performance of data access client applications.
The choices
From a design perspective, the programmer of a data access client application faces four major choices affecting performance:
• Which back-end relational database to use.
• Which data access method to employ—whether to use the Visual Basic data access object (DAO) layer, open database connectivity (ODBC), or a proprietary back-end interface or interfaces.
• Which back-end database system design data model to use, with its associated triggers and procedures.
• If using the DAO layer, making the correct choice of data access objects, and where to use transactions, attached tables, and the pass-through flag.
By using speed and efficiency as the scalpel, the endless feature requests for the system can be pared down to a finite, implementable list. Keeping performance implications at the forefront of the design process helps narrow choices and develop an architecture that is both scalable and responsive. Performance considerations should be second in priority only to the critical analysis needed to define clearly the rules of the business that the system must satisfy.
First choice: Which RDBMS?
The choice of a relational database management system (RDBMS) depends on both present and future business needs, as well as resources available. This decision is logically the starting point for the system design, because all the decisions pertaining to performance depend on the capabilities and limitations of the back end.
Several questions need to be considered:
• How many users will the system serve, now and in the next two years?
• Does data need to be distributed to more than one site?
• How often can the system requirements be expected to change? How quickly will those changes need to be made?
• What level of expertise is reasonable to expect from users?
• What resources are available to purchase hardware, software, and development/training time?
A comprehensive discussion of the tradeoffs involved with this first area of choice is beyond the scope of this article. However, let me propose a simple scenario for the purposes of illustration. Let's assume these answers to our checklist of questions:
• 70 users now, increasing to as many as 150 users in two years.
• Yes, there's a need for distributed data.
• The system will need revision every six months.
• All users are novices to database concepts.
• Money is available for a dedicated server box, a site license for SQL Server for Windows NT, and sufficient development time if Visual Basic is used as the front-end development tool.
The answers here favor a true client-server RDBMS, especially because of the large number of users anticipated and the need for administrative tools for data replication. A dedicated server is essential to multiuser systems with more than 50 users. Client-side database engines, such as Microsoft Access, also require that all clients voluntarily close their connections to the .MDB file before the database can be copied, compacted, and so on.
The need for frequent or fast system revisions also favors a dedicated back-end approach, with the rules of the business stored in the back end. Making system changes is easier to accomplish and synchronize from one central location.
Second choice: Which data access method?
With Visual Basic as the front-end development platform, there are several methods of data access:
• Write code that leverages the built-in DAO layer, directly to Microsoft Access, FoxPro, dBASE, Btrieve, and Paradox databases or through the DAO to ODBC databases.
• Code directly to the ODBC API, bypassing the DAO, starting with sample code from the ODBC SDK.
• Code directly to a back-end-specific protocol, such as the VBSQL library for SQL Server or a gateway to a mainframe database.
When to use the DAO layer
The Visual Basic DAO layer was designed to provide a generic set of data-aware objects and functions for use as a built-in library. This design insulates the programmer from the proprietary details of the back end. In the case of ODBC databases, the DAO layer provides the interface to the ODBC API. If the databases are local or client-based, such as Microsoft Access or FoxPro, the DAO layer provides a single interface, via the Microsoft Access engine, to the divergent database formats. The generic DAO layer in Visual Basic provides the leverage of a built-in data-aware library. Its disadvantage is that you can't modify or extend that library's functionality.
(Because using database objects raises some complicated issues, I will discuss how to use the DAO layer efficiently in the May/June 1995 issue of the Developer Network News.)
Go directly to the ODBC layer...
If the system must access heterogeneous data sources and be scalable enough to add new data sources as needed, the advantages of the ODBC approach should be apparent.
If the choice is made to code directly to the ODBC API, the development process can be similar to using the DAO layer, once the investment in creating a generic data access library or engine is made. The leverage begins once this ODBC interface library is written, because the Visual Basic code can be generic and insulated from the proprietary details—in the same way as code written to the DAO. And as with the code written to the DAO, the Visual Basic code written to the homegrown ODBC library is reusable as target back-end databases change.
Because Visual Basic and Microsoft Access were the first high-volume applications to exploit the ODBC standard, the general first impression of the ODBC mechanism was that it was much slower than the proprietary access protocols. In reality most performance decreases could be traced to one particular ODBC interface: the Microsoft Access engine.
This engine was designed for high generality and to provide a generic library of data access functions to make Visual Basic code written to the DAO independent of the data source. To achieve this, some flexibility and optimization for specific functions were sacrificed.
In bench tests against Microsoft SQL Server, comparing direct ODBC calls to VBSQL(DBLIB) calls, the average performance numbers were comparable, with some ODBC functions being faster than some DBLIB calls and vice versa. Getting a connection through the ODBC API, for example, took longer than through VBSQL calls, though not by an order of magnitude.
...or to a proprietary protocol
For application architectures where connections are created, used, and then immediately broken, the bench test results would seem to favor the proprietary protocol. Bear in mind, however, that this architecture arose from the need to "go easy" on older operating systems and networks, and may be less imperative with the latest operating systems/networks.
If the set of data sources is limited for the foreseeable future, having several proprietary libraries coexist in the front end may be the best solution. This leverages existing expertise and investments to get a production system up as soon as possible. In this case, maintaining the performance level of existing legacy systems in any new system is the appropriate choice.
Third choice: Which database design?
The actual design of the database—including normalization of tables, architecture of stored procedures, security, and data integrity—is a major area for performance considerations. Designing database systems for optimal performance is a broad topic; here we consider only questions directly affecting Visual Basic developers.
Assuming that we are discussing systems using client-server databases, the first architectural decision is how much processing logic should reside in the Visual Basic application, in the back-end, or in intermediary layers. For example, should the front end perform all data validation, none, or a mixture of front- and back-end logic based on performance considerations?
Other architectural decisions: Should the client handle security separately from the database's intrinsic security model or leave it entirely to the back-end's security model? Should the user's preferences be stored locally in an .INI file or on the back end in a user preferences table?
The answers to such questions have performance implications, as do the more back-end-centric issues of data modeling, enforcing business rules, or optimizing transaction throughput. These design decisions can only be made in the precise context of the particular project. Generally, programmers can increase the overall client performance by letting the client and server each do what it can do fastest, provided that doing so doesn't violate any architectural specifications for the system.
Next issue
Keeping the performance tradeoffs in mind during design of Visual Basic data access systems can reap large performance gains. In the next Visual Basic column, I'll discuss which DAO object is appropriate for which task, where to use transactions, when to use the pass-through flag, and when to use attached tables.
Robert Eineigl works in Microsoft's Information Technology Group, building database systems coupled to clients written in Visual Basic. He also disturbs wildlife by playing his custom-made, oversized trumpet.
Additional Reading
Note: All of these articles are in the Developer Network Development Library.
"Summary article: INF: Article List for Visual Basic: Data Access w/ MS Access/Jet" ID: Q122723
"LONG: Overview of Data Access in Visual Basic Version 3.0" ID: Q108379
"Using Table Objects Versus Dynaset/Snapshot Objects in Visual Basic" ID: Q109218
"INF: Database Normalization Basics" ID: Q100139
"Differences Between the Object Variables in Visual Basic Version 3.0" ID: Q103442
"Differences Among the Installable ISAMs" ID: Q104918
"Comparison of Seek Versus Find Methods, for Visual Basic Data Access" ID: Q108149
"How to Speed Up Data Access by Using BeginTrans & CommitTrans" ID: Q109830
"How to Optimize Memory Management in Visual Basic 3.0 for Windows" ID: Q112860
"How to Access Multiple Databases in an SQL Query" ID: Q113701
"PRB: ODBC Error ‘Connection is busy with results...'" ID: Q119023
"LONG: Visual Basic 3.0 EXTERNAL.TXT: Using External Database Tables" ID: Q108422
"LONG: PERFORM.TXT—Performance Tuning Tips for Visual Basic and MS Access" ID: Q107751
"INF: Five Parameters to Set in ISAM Section of MSACCESS.INI" ID: Q101323