Recordset vs Variant Array

[daveshack]

Hi,

I have got into the habit of using module level variant arrays to hold data from ADO recordsets (using the .GetRows method)

I have found that this works quite well.

I am just wondering on the pros / cons of this basically in terms of memory usage / performance?

Will a recordset that is open for the duration of the form being open consume more resources than a Variant Array?

Is it quicker to loop through a Variant Array than a recordset?

Thanks for any tips / suggestions in advance

Dave

[Dave Sell]

Hey Dave those are good questions. The only way to _know_ is to see the source code Microsoft uses on those classes. Which we dont have.

I would suspect the recordset is faster than the Array, but the only way to find out is empircally.

Set up an array and a Recordset of varying sizes (1000, 10,000, 100,000) and try different operations on them (sorting, traversing, inserting, deleting).

If you do all that, please post your results!

[numtel]

I did a test the other day to see if it was true that I should use integers as opposed to longs or variants in for loops, it turns out it doesn't matter, look at this.

[daveshack]

After a little testing it turns out that it is quicker to loop through a Variant Array than a Recordset

I should point out that I am using a MySQL backend db and I am not using ADO. I am using a VB API wrapper written for MySQL databases called VBMySQLDirect (www.vbmysql.com). This eliminates the need for ODBC / OLEBD layer as well as the ADO layer. I found that performance with ADO + ODBC or ADO + OLEDB with a MySQL db was extremely slow.

Anyway after creating a module level Recordset and Variant Array i looped through about 100000 records. Just looped through using the .MoveNext method. The Variant array was quicker by about a factor of 10.

Then I added an If statement which checked the value of a .Field in the recordset and a value in the Variant Array. With the if statement the Variant Array ran quicker by about a factor of 15

Finally I added another If statement that checked the sum of 2 fields. This checked if Fields(1) + Fields(2) = Fields(3) then (or checked the value of elements in the Variant array if Array(i,1) + Array(i,2) = Array(i,3) then)

With this the Recordset object again was slower by a factor of about 15

I have included my code for testing. However it does use VBMySQLDirect which will not work in all situations.

So in my case the Array seems quicker. Everywhere I read suggests against using too many Variants because of the added resource usage. I always Set varArray = Empty to release memory. Has anyone had any issues with this.

I did not check sorting, inserting, deleting via the array / recordset. As sorting I will do in the SQL statement to prepare the recordset. Inserting and deleting I always do via the .Execute method on the Connection object.

Hope this makes sense.

Cheers for the input.

Dave