Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 7 of 7 total |
DBISAM Memory Table performance |
Tue, Dec 10 2013 10:20 PM | Permanent Link |
Arthur Williams Zarksoft | 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. |
Wed, Dec 11 2013 4:40 AM | Permanent Link |
Matthew Jones | 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 |
Wed, Dec 11 2013 5:32 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Wed, Dec 11 2013 1:21 PM | Permanent Link |
Arthur Williams Zarksoft | 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. |
Wed, Dec 11 2013 1:43 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Wed, Dec 11 2013 3:24 PM | Permanent Link |
Raul 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. > |
Mon, Dec 16 2013 8:54 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |