When you create applications that connect to a data source, especially in a multi-user environment, there are a number of performance issues that you should consider. These considerations include the number of open connections to a server, the way in which locking is managed, and the proper use of transactions.
Each connection you create to a data source, such as SQL Server, uses resources on both the client and the server. In addition, the number of connections available on a server is limited by the number of licenses purchased. Since each connection from a Visual Basic application appears as a separate connection, the number of available connections can be reduced quickly. SQL Server does not consider connections on a per-application basis. Therefore, consider using multiple recordsets from the same Connection object if you require more than one cursor.
Unless your application requires use of a recordset, consider using the Execute method of a Connection object or Command object to interact with the data source. SQL statements are more efficient and use less overhead than recordsets.
Using an existing ADO recordset, your application can sort the returned records or search for a specific record without re-querying the data source. This helps limit network access and reduce server load. However, depending upon the size of the recordset, or the search requirements, it may be more efficient to re-query the data source with a more specific SQL statement.
When you use ADO, there are two techniques for updating data in an external data source. You can either execute SQL commands to update records, or you can use a Recordset object and issue various methods of the object to update records.
Unless you need to build a recordset, it is usually more efficient to execute the required modification through a SQL command. You can do this from either the Connection object or the Command object. However, a good understanding of SQL is required.
If your application has already created a recordset that can be updated, it may be easier to use the recordset methods AddNew or Update to modify the database. This technique is only available for single record updates, since only one record is available at a time when using a Recordset object. For multiple record updates, such as a batch updates, consider using the Execute method of a Command object.
Although transactions ensure data integrity, locks are created by the data source while a transaction is open. Commit or roll back the transaction as soon as possible to allow other applications access to the data.