Re: Which is faster--Array or ADO

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


Depends of your join and of your need.

Any solution based on 'data lives in memory' is hardly multi-users friendly: if data change, you need to resych it, not necessary trivial.

There is also two opposite 'ways' of doing it: the first one that I will call the DAO way, just to give it a name, would be to create bookmarks for each row in the result of the join, and here that can make up to 3.5G rows if you use a Cartesian join ( 3.5G = 70K times 50K ). Clearly, the whole data (of the join) may not fit in committed memory, AND you may end up with multiple duplicates Disk IO, when you navigate over this monster, that is, reading the same initial row of one of the table, multiple times from DISK (which is slower than reading cached data in memory) . The other way, let us call it, ADONet, again, just to give a name to it, would be to read each table, locally, so taking only 120K of committed memory ( = 70K + 50K), and 'make' the join locally, such as looping over each table now in memory (explicit looping or using some other mechanism, call it LINQ or by other name, it does not really matter for the illustration). That solution will be much faster than the first, since any INITIAL row would only be read ONLY ONCE, but YOU may have to add additionnal functionnality, all by yourself, which may introduce additionnal more or less complex behaviors (lock, update/delete/append/resynch, etc).

In fact, your "array" solution is close to the one I called ADONet, but you have to add the join mechanism, yourself, which can be easy, or very difficult, depending on your EXACT scenario.




Vanderghast, Access MVP



"RLN60" <RLN60@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:2638FC5E-5F94-45BA-AAEC-0628D2EDF020@xxxxxxxxxxxxxxxx
RE: Access 2007

I have 70k rows in one table and about 50k rows in another table. They are
joined together via proper keys and are indexed. I need to load them into
either an Array or and ADO recordset, do a bunch of processing then create an
Excel export and wanted to know which is faster:
1) Loading 70k rows from a table into an ADO recordset and working with it
in ADO
2) Loading 70k rows from a table into an Array and working with it in the
array.

I would like to know a bit about the theory behind why one would be faster
than another. Arrays are in RAM. Is the ADO recordset in RAM also?

Thanks.


.

Relevant Pages

  • Re: Out of Memory Problem in VB 6.0 Application.
    ... that's a fixed size array. ... > block of memory. ... Just think of it as 2gigs of ram that you have at your disposal. ... matter if that's real ram or swap file it's still 2 gigs available to your ...
    (microsoft.public.vb.general.discussion)
  • Re: Handling large amounts of data
    ... > stereo sound sample stored at 4bytes per sample, ... > array syntax. ... Writing the data to disk and then memory mapping the files ... of RAM, and 40MB is nothing. ...
    (comp.lang.c)
  • Re: Fast string operations
    ... Looping: I thought looping over arrays in managed code was "slow" ... array handling and such. ... The problem with TrimHelper is that it always returns a new string instance. ... The customer perceives this as a memory leak. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Out of memory when reading a large file
    ... I would ideally like to read the data in an array and then be able to ... In order to do what you want, you need enough RAM on your computer. ... To get all the data into memory at once, ... chop up the data into smaller chunks. ...
    (comp.soft-sys.matlab)
  • Re: High Memory Consumption of Classes and Arrays
    ... Only the array itself has overhead. ... memory as a reference type. ... > least consume 40 bytes of memory. ...
    (microsoft.public.dotnet.framework.performance)