Using ADO.Command’s GetRows with Arrays and Scripting.Dictionary
While working with our team’s DB Command library, I noticed the current implementation seemed slightly inefficient. So, I proposed an alternative approach.
The existing method uses an ADO.Command object to perform a SELECT operation, storing the resulting data in a RecordSet. Then, we loop through the RecordSet, using nested loops to populate a Scripting.Dictionary object with column names and their corresponding data. This Dictionary is then used for subsequent processing.
The new approach I envisioned involves utilizing an API provided by the ADO.Command object, which can immediately flush the data stored in a RecordSet into a 2D array. This method eliminates the need for nested loops, potentially improving performance. However, there was one major challenge: data stored in an array cannot be accessed using column names. This limitation could create inconveniences during development and lead to incorrect data retrieval if the database schema were to change in the future.
To address this, I proposed the following method:
1. Use a Scripting.Dictionary object as a column mapper.
2. Store the actual data in an array.
3. Provide an interface in the DB class to allow developers to access data by column name and row number, maintaining ease of use.
While I can’t share the exact implementation due to company confidentiality, I can share the results of our internal load tests after completing this work—and the results were quite remarkable.
Using HP LoadRunner, we visualized the performance improvements as shown in the graph below:
(Imagine a performance graph here)
The graph came out just as expected—clean and beautiful. These results gave me the confidence that the improvements were worth implementing. I went ahead and developed the new library. However, as of now, it hasn’t been widely adopted within the team due to a lack of proper sharing and promotion. For the time being, I’m the only one using it.
I’ve already applied this library to a few new sites as a prototype, and it seems to be working quite well.