While contemplating whether to use DBISAM in-memory tables in an application I wrote a simple test program to see how fast things were.

I create a simple table of perhaps a dozen fields and then generate 1,000,000 records for it.

Time to .Append 100,000 records on disk      : 14 seconds
Time to .Append 100,000 records in memory :  6 seconds

Then lets read from .First to the end with just .Next, no field referrals.

Time to Read 1,000,000 disk records           :  2 seconds
Time to Read 1,000,000 records in memory :  11 seconds  (?)

Bit odd that. IDE disk 5x faster than memory ? I think not. I suspect Windows caching performance exceeds whatever TMemoryFile is doing.

Time to Read 1,000,000 disk records randomly with FindKey          :  63 seconds
Time to Read 1,000,000 records in memory randomly with FindKey :  57 seconds  (?)

That's some slow memory alright.

I also tried reading the table using a Filter.

Time to activate filter (.Filter = True) : 2 to 12(!!) seconds
Time to read 1,000,000 memory records : 6 seconds, ~200K records selected by filter.

Whether the filter setting took 2 seconds or 12 seconds seems rather random, so I'm at a loss as to what might be behind it.

Overall, rather disappointing performance from the in-memory table.

I think that a million records is not using memory tables appropriately. I
would think it would start swapping to disk and suchlike. I think that any
more than ten thousand records would be more than I would want in memory.
Disks, indexes and caches will soon win for millions. Either that or my
expectations are wrong...

--
Matthew Jones

Arthur


I'm with Mathew and you on this. Firstly a million records in memory is beyond the design scope and secondly who knows what Windows is doing with this. Even if you have oodles of RAM Windows can still decide to write things out to the swap file it it sees fit. That will stuff your performance.

If you can post your test code I'd be happy to try in ElevateDB and see if that speeds things up or not.

Another thought just occurred. Was this test carried out in the IDE or outside it, if inside do you have FastMM in debug mode?

I often do that and forget that FastMM in debug mode will "hang on to memory" and make things run much slower.

Roy Lambert [Team Elevate]

Does EDB have in-memory tables too ? I was flipping through the manual but I didn't see anything on the topic.

There was no swapping. This is a 64bit application with 16GB ram available. Total memory consumption for the million records was only about 2GB.

It makes no sense to me that DBISAM can read from disk faster than it can read from memory. I presume that there is a lot of overhead it has to go through before it actually reads the records, thus negating the speed advantage of ram.

I also don't understand why it takes several seconds to process .Filter = True. The first few times when I saw it was taking 12 seconds to process "type = 3" as the filter I could hardly believe it.

Arthur

>Does EDB have in-memory tables too ? I was flipping through the manual but I didn't see anything on the topic.

They're exactly the same as any other table - you just define the database as memory

>There was no swapping. This is a 64bit application with 16GB ram available. Total memory consumption for the million records was only about 2GB.

Are you sure. Windows does strange things.

>It makes no sense to me that DBISAM can read from disk faster than it can read from memory. I presume that there is a lot of overhead it has to go through before it actually reads the records, thus negating the speed advantage of ram.
>
>I also don't understand why it takes several seconds to process .Filter = True. The first few times when I saw it was taking 12 seconds to process "type = 3" as the filter I could hardly believe it.


That's why I'd like your test app to see what happens.

Roy Lambert [Team Elevate]

Arthur,

I'd have to agree with others that without seeing the little test app
there is not much to comment or suggest on.

We did something similar long time ago and got opposite results -
in-memory table was quite a bit faster. in some cases we found
in-memoery table to be twice as fast.

For example insert operations were faster while operations that just did
data selection were about same (or trivially faster for in-mem).

You're seeing something comparable on append - in-mem twice as fast.

Dataset navigation being slower is unexplained and in our case it was
faster for in-mem by about 30%.

Raul


On 12/10/2013 10:20 PM, Arthur Williams wrote:
> While contemplating whether to use DBISAM in-memory tables in an application I wrote a simple test program to see how fast things were.
> I create a simple table of perhaps a dozen fields and then generate 1,000,000 records for it.
> Time to .Append 100,000 records on disk      : 14 seconds
> Time to .Append 100,000 records in memory :  6 seconds
>
> Then lets read from .First to the end with just .Next, no field referrals.
>
> Time to Read 1,000,000 disk records           :  2 seconds
> Time to Read 1,000,000 records in memory :  11 seconds  (?)
>
> Bit odd that. IDE disk 5x faster than memory ? I think not. I suspect Windows caching performance exceeds whatever TMemoryFile is doing.
>
> Time to Read 1,000,000 disk records randomly with FindKey          :  63 seconds
> Time to Read 1,000,000 records in memory randomly with FindKey :  57 seconds  (?)
>
> That's some slow memory alright.
>
> I also tried reading the table using a Filter.
>
> Time to activate filter (.Filter = True) : 2 to 12(!!) seconds
> Time to read 1,000,000 memory records : 6 seconds, ~200K records selected by filter.
>
> Whether the filter setting took 2 seconds or 12 seconds seems rather random, so I'm at a loss as to what might be behind it.
>
> Overall, rather disappointing performance from the in-memory table.
>

Arthur,

<< Overall, rather disappointing performance from the in-memory table. >>

DBISAM *itself* is not particularly fast once you get into the millions of
records, due to several reasons, but mostly due to the way that indexes are
updated/accessed.  Also, in-memory tables aren't special in terms of how the
engine uses them, so the only thing that is different is how the raw data is
stored, not how DBISAM reads it into the cache, processes it, etc.  So
you're going to incur the same amount of CPU processing for in-memory vs.
disk.

As to the specifics, I'd have to profile your test application to see what's
actually taking up the time.  I suspect that the issue is simply the size of
the TList that DBISAM is using to store the in-memory table blocks.  The
constant re-allocs are probably just killing the performance once you get
past a certain file size, and this just points to the fact that the
in-memory tables weren't really designed for such numbers of records.

Tim Young
Elevate Software
www.elevatesoft.com