Stan


What benchmark tests?

Roy Lambert

From what I remember, converting my apps from v3 to v4 was trivial.


On 6/13/09 3:56 AM, in article
5A045552-F669-4129-A22A-3F6FDA4CE478@news.elevatesoft.com, "Bobby Gallagher"
<bobgal@computech.ie> wrote:

> Question - Should we convert the DBisAM 3 programs to V4 for now - if so
> what would be the advantage.

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:A4519B20-E7ED-418D-9183-55030926B194@news.elevatesoft.com...
>
> What benchmark tests?
>

I wish I could figure out that bit also, the rest I put
down to whaccy baccy or schnapps
Rita

Hi David

Yes I think that converting to 4 is not a big job at all - just wondering if
there is any benifit to be gained from doing this bearing in mind that all
user tables/databaes would need to be upgraded as well

Regards

Bobby


"David Puett" <dpuett@gmail.com> wrote in message
news:C65E928B.36EC6%dpuett@gmail.com...
> From what I remember, converting my apps from v3 to v4 was trivial.
>
>
> On 6/13/09 3:56 AM, in article
> 5A045552-F669-4129-A22A-3F6FDA4CE478@news.elevatesoft.com, "Bobby
> Gallagher"
> <bobgal@computech.ie> wrote:
>
>> Question - Should we convert the DBisAM 3 programs to V4 for now - if so
>> what would be the advantage.
>

Bobby Gallagher wrote:
> Yes I think that converting to 4 is not a big job at all - just
> wondering if there is any benifit to be gained from doing this
> bearing in mind that all user tables/databaes would need to be
> upgraded as well

I got 4 quite a while ago but haven't even installed it yet. Everything
is working quite well with V3 on the customers' side, and there is this
saying "don't fix it if it isn't broken," so I'm rather chicken to take
the step.

Roy Lambert wrote:

> What benchmark tests?

That is not my own invention, I am just refrerring to this benchmark from couple of years
back.
Date: 09 Oct 2007
From: Dave Harrison
Newsgroups: elevatesoft.public.binaries

Youself have written and commented to that thread.

The Benchmark is quite simple. It first creates an empty database, then adds two Indexes
on it and then fills the Table with random data.

Here are the Benchmark results for instance with 50.000 record table

BDE/Paradox 5.0   Elapsed: 0,6 sec,  50.000 records 86 505 Rcds/Sec
NexusDb   Elapsed: 1,4 sec,  50.000 records 35 162 Rcds/Sec
DBISAM 3.30   Elapsed: 3,9 sec,  50.000 records 12 903 Rcds/Sec
ElevateDb 2.01 Elapsed: 5,9 sec,  50.000 records  8 488 Rcds/Sec

These numbers really make wonder. Why does both Dbisam and ElevateDB get numbers like
this? The "Flagship" ElevateDB takes 10 times as much as Paradox, or 4 times as much as
the Free version of NexusDB.

Somewhere these tests are commented, that if you turn LocalShare=True in BDE/Paradox, then
the numbers will fall to the same level as with Elevate versions. But this is not true. I
turned LocalShare=True bot with BDE Config and also in my test code, and it sure is True.
Yet Paradox does get these good numbers and even the competitor, "arch rival" Nexus.

If there were something in that test that is negative to Dbisam/ElevateDb, then I could
try to enhance the code for "our" database versions. But the code is quite simple, and I
have not found anything to enhance.

Here are shortly the 3 steps that this benchmark does.

1. Create DBISAM Table
-----------------------
TableToCreate:=TDBISAMTable.Create(Application);
try
with TableToCreate do
  begin
    DatabaseName := DBISAMDatabase.DatabaseName;
    TableName    := cBenchTableName;
    Exclusive    := True;
    if Exists then
      DeleteTable;
with FieldDefs do
begin
 Clear;
 Add('Name1',ftFixedChar,17);
 Add('Code1',ftFixedChar,1);
 Add('Date1',ftDateTime);
 Add('Num1',ftFloat);
 Add('Num2',ftFloat);
 Add('Num3',ftFloat);
 Add('Num4',ftFloat);
 Add('Num5',ftFloat);
 Add('Num6',ftFloat);
 Add('Num7',ftFloat);
 Add('Num8',ftFloat);
 Add('Num9',ftFloat);
 Add('Num10',ftFloat);
end;
CreateTable;

2.Create DBISAM Indexes
-----------------------
Query := TDBISAMQuery.Create(Self);
with Query do
begin
 DatabaseName := DBISAMDatabase.DatabaseName;
 Sql.Clear;
 Sql.Add('create index ix_Name on '+cBenchTableName+' (Name1,Code1,Date1)');
 ExecSql;
 Sql.Clear;
 Sql.Add('create index ix_Date on '+cBenchTableName+' (Date1,Name1,Code1)');
 ExecSql;
end;

3.Add random data
-----------------
aTable.Append;
FieldName1.AsString   := copy(cAlpha,Random(Length(cAlpha))+1,Random(8)+3);
FieldCode1.AsString   := UpCase(cAlpha[Random(3)+1]);
FieldDate1.AsDateTime := StartDate + Random(10000);

for j:=1 to 10 do
 vFieldNums[j].AsFloat := RoundTo(Random(2)*1000,-3);
aTable.Post;

-----
THIS is why I originally joined to this thread, as there were something about Flagship
products. Currently the Flagship does not seem to be competing in speed even with old
Dbisam 3.30 version.

The differences also are quite big. Why to upgrade from 3.x or 4.x versions to ElevateDB,
if the marketing phrase would be something like "Upgrade now, you get a brand new engine
and your apps wull run only 51% slower."

Sorry, I have *nothing* against Elevate Software, nor their personnel or their products.
My own business is mostly trusting on Elevate's products, and that way they are also "our"
product line. I am just damn concerned that why do our engines seem to perform so badly.
Many of our users for instance with POS tasks easily gather 100.000 new records or more
per year.
Stan

Stan


I remember it, I just didn't think you were referring to something that old Also I don't care how fast the BDE/Paradox is no way am I using it again, now DBISAM - that's a different question <vbg>

Roy Lambert

Stan


Another little thought. Dave's tests were single user/exclusive table open. It would be interesting to see how each performed under more real life situations eg two or more users populating the database.

Arguably uses with multi-million row databases should look at alternative strategies, especially if they're into thousands of transactions per second. But to put it into context the 100,000 rows a year translates to slightly less than 1 per sec (assuming working time of 47 weeks x 5 days x 8 hours).

Writing speed isn't going to be a problem for most of us

Roy Lambert

Roy Lambert wrote:

> Also I don't care how fast the BDE/Paradox is no way am I using it again,
> now DBISAM - that's a different question <vbg>

Yes, looks like old DBISAM is 50% faster than ElevateDB in this test, and the freeware
version Nexus is 400% (four times) faster.

My current thought is that the speed difference becomes from other engines having better
structured Indexes or maybe using them better than the other engines. Here are the Data
and Index file sizes from that 50.000 record test:

DBISAM
------
Test.DAT   6.510.111 bytes
Test.IDX    6.684.672 bytes

ElevateDB
----------
Test.EDBTbl 7.216.384
Test.EDBIdx 6.664.192

BDE/Paradox
-----------
Test.DB    5.310.464
Test.XG0      34.816
Test.YG0       4.096
Test.XG1      34.816
Test.YG1       4.096

NexusDB
--------
Test.nx1           11.952.128
$SQL$PROCEDURES.nx1    20.480
$SQL$VIEWS.nx1         20.480
$SQL$FUNCTIONS.nx1     20.480
$SQL$TRIGGERS.nx1      20.480


You can quickly see that both DBISAM and ElevateDB have huge Index files. The Index file
is almost as big as the actual data. Yet here were no String type fields indexed, only
numerical fields.

My conclusion is that there must be a cost for speed when the index files grow rapidly. It
is strange how Paradox Indexes can be as tight as they seem to bee. Yet I have checked,
and both DBISAM and Paradox tables DO have same fields indexed, so BDE/Paradox is not
cheating here.

My thought was that maybe I will expand this test and run it against maybe one or two more
other popular database engines. But w'll see if that realizes.

Stan

Stan



>You can quickly see that both DBISAM and ElevateDB have huge Index files. The Index file
>is almost as big as the actual data. Yet here were no String type fields indexed, only
>numerical fields.

It looks as though Nexus has as well, but wrapped into the nx1 file.

>My conclusion is that there must be a cost for speed when the index files grow rapidly. It
>is strange how Paradox Indexes can be as tight as they seem to bee.

I'd be interested in the answers to that one, just out of interest.

>Yet I have checked,
>and both DBISAM and Paradox tables DO have same fields indexed, so BDE/Paradox is not
>cheating here.

Maybe it is. Its possible that Paradox isn't actually creating an index on disc, just in memory since its been told its exclusive and all its saving down to disk is the index definition. The omnipotent Tim should know.

>My thought was that maybe I will expand this test and run it against maybe one or two more
>other popular database engines. But w'll see if that realizes.

If you do, make it multi user please.

Roy Lambet