Re: Which is faster--Array or ADO


In most cases, it's probably better to use your record set.

I mean if you think about this process, you are ALWAYS loading something
into RAM be it an array, or that of a reocrd set.

So, one is not necessarily going to your processing speed because it is ram.
In fact ALL processing in the computer is done in ram.


I mean:

#1 using an array:

loop
read one record from disk into array
modify this record
now write record back to disk
until all records done
write data results out to spread***

#2 using an reocrdset
loop
read one record from disk into reocrdset
modify this record - write back to disk
untill all reocrds done
write data results out to sprad***

I mean, in both cases, the BULK of your time going to be reading from the
hard disk, and not the processing part anway.

The other advantage of using a record set tends to be that you can use SQL
sum functions and things like joins.

For example you might have a customer's table, and a customer invoice table.
If you load all the customers into an array, and then load all the customer
invoices into another arrary then you'll have to use a significant amount of
additional processing to join up the two arrays to total up each customer
invoices. In you use a reocrdset, then you can use a SQL join to join up to
two tables process the whole thing is one pass. In fact, you might even have
sql generate the totals for you and not even have to process this reocrd by
reocrd.

sql = join of two tables
loop
read one reocrd (that is a join of two tables)
modify or process
untill all reords are done

Furthermore in many cases you can use SQL to do an update in one shot and
this again tends to be faster than writing your own custom code to modify
the data one reocrd by one record at a time.

At the end of the day without knowing what kind of processing you doing here
it hard to guess.

In most cases there is not going to be a lot of difference. To load data
into a array you have to FIRST pull it into a reocrdset anyway!

So, using SQL commands and record sets tends to be far less work, and
requires far less code to accomplish the same goal. Furthermore you have
this query engine that optimizes the task at hand (it can make decisions as
whether it's better to read some data sequentially, use an index to pull the
data out, it makes intelligent decisions about how it will grab the data).

In both cases you going to read a reord from disk....modify it in memory,
and save it back. So, pulling the data into an arrray (extra work and time
and code and proecessing to setup and build that array), and THEN modifing
the data, and THEN writing a bunch of code to write out the array back to
disk is not going to save any disk i/o here at all.

Of course there can be some advantages of an array and that depends on how
the data is going to be manipulated. For the most part if you think about
what's going on here, you have to read the data from disk in both cases into
RAM to be able to do anything to that data.

It also depends on "how" you plan to decide what data needs to be read into
the arrary for procesing. If you not needing all reocrds, then it can often
be a waste of computer resources to read the whole table into that array
especially for turns out that you don't need to process all records in that
array.

So there's no simple answer, and there's no way of knowing without seeing
the kind of processing and the approach to your problem you trying to solve
here. So a clear-cut answer based on the informaton you given is not really
possbile.

However the above does point out that simply choosing an array because it
uses RAM is not necessarily going to be faster, since in both cases you have
to read the information from the hard disk into RAM before you can actually
do anything with the data anyway.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx


.

Loading