So a while back I posted a question about memory table performance where I had put a few million records into a memory table.Many of the responses were along the lines of "that's too much for DBISAM".

So my question now is, what is the largest number of records, approximately, that should be assigned to a DBISAM table ? Less than 100,000 ?  Few hundred thousand but less than a million ?

Although this is the wrong forum, in the event that Tim responds, I would have the same question for EDB. I know it's faster than DBISAM, and in fact is about the same speed as Interbase, at least at the record counts I was using in my testing. Although it's faster, perhaps EDB has the same general capacity limits, I don't know, hence the questions.

Arthur

Well, the capacity is memory available to handle the data. Yes you can put a million records into a memory table but the question is how long does it take to populate the memory table and the purpose to put all data in memory.

I have used memory tables a lot and realize the best use for this is arount 10 thousands but here I am not speaking about capacity but the reason to put summary of information into a memory table. Sometimes it is faster to put the data into a record structure array but It depends on what you really need to do.

Eduardo

On 2/25/2018 9:01 PM, Arthur Williams wrote:
> So a while back I posted a question about memory table performance where I had put a few million records into a memory table.Many of the responses were along the lines of "that's too much for DBISAM".
>
> So my question now is, what is the largest number of records, approximately, that should be assigned to a DBISAM table ? Less than 100,000 ?  Few hundred thousand but less than a million ?

Official technical capacities are listed here

https://www.elevatesoft.com/manual?action=topics&id=dbisam4&product=rsdelphiwin32&version=10T&section=appendix_system_cap

For in-memory tables there is also the aspect of process virtual memory
size - only really matters for 32 bit apps as it's 2GB total for
everything including in-memory data.


> Although this is the wrong forum, in the event that Tim responds, I would have the same question for EDB. I know it's faster than DBISAM, and in fact is about the same speed as Interbase, at least at the record counts I was using in my testing. Although it's faster, perhaps EDB has the same general capacity limits, I don't know, hence the questions.
>

And EDB :
https://www.elevatesoft.com/manual?action=topics&id=edb2&product=rsdelphiwin32&version=10T&section=appendix_system_cap



Raul

The technical capacity isn't really relevant. I want to know what the approximate size of table can be used in DBISAM and still remain practical enough to use. I'm also not talking about memory tables, just tables in general. I already know that DBISAM is no good for tables with millions of records. The statement:

mytable.filter := Active;

when you have constructed a filter string takes between 10 and twenty seconds to execute if the table in general has several million records. That's not practical for real world use.

By way of example, AidAim says EasyTable should be used for tables of 10,000 records or less. Sure it can theoretically hold 2 billion records, but they say don't do that, use Accuracer instead.

So the question regarding DBISAM isn't what it could do, but what it can do that still makes it a reasonable choice to do so. The same thing for EDB.

Arthur


Whilst I agree with you that the technical capacities are not massively of interest the question you are asking is only answerable in terms of the technical capabilities. Unfortunately a real world answer requires real world information - ie details of what is to be done with the table.

<<mytable.filter := Active;

when you have constructed a filter string takes between 10 and twenty seconds to execute if the table in general has several million records. That's not practical for real world use.>>

I agree, almost. If the filter is to be applied to produce a report at intervals its probably fine, if its responding to a user sat at a PC it may not be, unless its operating over the internet where such delays are more tolerated.

To pursue it a little further - how optimised is the filter?  What type of fields are you filtering on? How many rows is it going to return? All of these will make a difference.

Ultimately, if you have a multi-million rows database from which you require sub-second performance the DBISAM is probably the wrong tool and you need a much bigger budget.

My biggest DBISAM table has been reduced to c74k records (around 2GB in size), its my homebrew mail and news reader. At its peak it was c250k records the only real difference I've noticed in speed is when doing a string search on a message. Other operations seem as fast or as slow - take your pick.

Roy

I have databases with 40Gb or more with tables with 18 million records. But I never apply a filter to that table.

IMO the way the developer should use to get data from table is the most important part of the process. At least for me, most of the time, a simple interval of dates with range can do the things. If you apply range and "walk" throught the range transfering data that really matters to an in memory-table can speed up things a lot.

To improve (a lot) the things, I  do the range/while not eof routine at server side producing a temporary table. The performance is fantastic even with millions of records. Most of the time, a few number of records should be retrieved.

Eduardo

Roy Lambert wrote:

Arthur


Whilst I agree with you that the technical capacities are not massively of interest the question you are asking is only answerable in terms of the technical capabilities. Unfortunately a real world answer requires real world information - ie details of what is to be done with the table.

<<mytable.filter := Active;

when you have constructed a filter string takes between 10 and twenty seconds to execute if the table in general has several million records. That's not practical for real world use.>>

I agree, almost. If the filter is to be applied to produce a report at intervals its probably fine, if its responding to a user sat at a PC it may not be, unless its operating over the internet where such delays are more tolerated.

To pursue it a little further - how optimised is the filter?  What type of fields are you filtering on? How many rows is it going to return? All of these will make a difference.

Roy

------------------------

The table is question was artiificial in the sense that it was built solely for testing the filter. The filtered data was perhaps a few hundred records out of the millions. I was trying to understand why my filtering was taking so long, and it wasn't until I ran the artificial test that I discovered that it was the .filter := Active; that was where the delay was rather than the .Findfirst. I had expected the .Findfirst to the big chokepoint and was quite suprised to find that it wasn't. The filter was a single value check against a unique but not primary index.

I don't foresee encountering that situation in my current projects as the records involved are not that large, but I have one in the I'll say design stage where there will be millions of records and response time could be an issue.

Arthur


Try applying the filter as SQL in DBSys with Generate Plan checked. Have a look at the plan and see if that tells you the query is optimised or not.

Roy Lambert

Jose


That's a big table

Out of interest how fast would a query be in comparison with setting a range?

Roy Lambert

Roy

With a simple filter, or filter that matches an index that already exists range is always faster than query.

Querying tables with more than 10 milion records is a bit slow because DBISAM needs to build a bitmap of pointers and sometimes it needs to read all the records.

Like I said before, I build POS systems and dates are the key of the process. I use a range with initial/end dates and put what I want in temporary tables. It rocks. It is fast for any criteria specially because it is running enterely in the server side (without network issues) reading data directly from the disk.

I have tried (only in my lab) a software called RamDisk (https://www.softperfect.com/products/ramdisk) and this is a fantastic way to solve or decrease the speed of filtering and reading data.

Eduardo