Ado as a technology offers a lot of usefulness. It can help you manage concur connections. Lock records; ease inserting, manipulating, and deleting individual records; and overall manage database manipulation. It tries to provide a layer of abstraction between the developer/application and the database/database provider. It accomplishes this goal fairly well. Unfortunately though, it comes at a price: performance. To understand how to improve performance, we need to understand the basics of how ADO works.

ADO does a lot behind the scene. First it must be able to communicate with your database provider. To do this it has to translate all of your ADO commands into its SQL specific counter parts. There is no getting around this. But it is additional layer that needs to be accounted for. This isn’t the biggest hit in performance though. What is the foundation of how ADO works; Record sets.

Record sets are the result of your SQL command, the records that get returned. ADO returns all of the records you asked for in one big lump and loads it into memory. Memory is cheap and memory operations are fast, but the overhead of managing large amounts of data this way is costly. Operations such as sort, find, and filter can be huge if the data is as well. These commands and others are done on the client side and on the entire record set.

Transferring that large amount of data can be part of the overhead as well. Traditional client/server databases can handle this, but if you are using access which uses a file connection this can pull an application to its knees.

By default when you open up a generic ADO connection, it opens and returns the whole table. Depending upon the table this can be a long and resource intensive operation. The best way to handle these performance issues are to follow these simple steps to improve your performance.