January 8, 2007
Understanding and improving ADO performance
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.
Steps to improve ADO and query performance:
- Use the .command method. This will give you greater control over what you bring over. By limiting the amount of data to what you just need, you save network bandwidth, client memory, and client CPU time. Let the database engine do the work for you.
- Use the where clause in your SQL command. Again this goes along with #1.
- Don’t use Select * in your SQL command. Using column names gives lets you specify the order that columns get returned in. You can change the names of the columns. You don’t have to fumble with ADOx to figure out the column names (giving your code that much more readability). Finally, if the database is changed and a columns get added you aren’t suddenly faced with a degrade in performance because the added extra data is being brought over.
- Do all of your sorting, grouping, and aggregates (Min, max, sum, count, distinct, …) in your SQL command. The database is designed and optimized to do these functions. Let it do it for you.
- Be smart with your queries. Know your data and look at your most used queries and optimize them against your existing indexes and add indexes where you need them.
I followed these steps with a customer’s existing Access database and saved them from rewriting the entire application. It sped up the application from opening up this one screen in 3 min to 20 seconds. That was before optimizing the queries too.
What other tips and tricks do you have for speeding up an ADO connection/query?
Technorati Tags: MS Access, SQL, ADO, performance, databases, queries, development
Albert Einstein once said, “Technological progress is like an axe in the hands of a pathological criminal.” I look at the progress we have made in the past 20 years and especially in the last 5. We have gone from technology being in the hands of geeky privileged few, to it being common place everywhere. It is no more apparent than watching how people view and use the web. We live in a world where people 
