Findings of the Pervasive/Nexus insertion benchmark from "first test" thread

Please review my findings, comments are welcome. The modified code is
posted in binaries.

The benchmark code tests insertion speed between Pervasive.SQL V8 and
NexusDB V1.0. Upon inspection I found the following:

1) There were two indexes being created on the NexusDB table and only one on
the Pervasive table.
2) The insertion operations for NexusDB were not optimized by using
batchappends.
3) Redundant Transport and Remote Server components were being created for
each Thread.
4) NexusDB was created via FieldDefs and IndexDefs.
5) Although exceptions raised within the execution threads were being
caught, they were not being logged.
6) There was an assumption that all records were added to the table once the
test completed.
7) The simulated latency (i.e. sleep calls within the test threads) do not
seem to favor one database over the other; nor do they affect the benchmarks
in any meaningful way.

I have modified the code in the following ways:

1) Added the second index to the pervasive table.
2) Optimized NexusDB inserts using batchappends.
3) centralized usage of the Transport and Remote Server components.
4) Created the NexusDB table through data dictionary manipulation.
5) Used StartTransactionWith method in NexusDB.
6) added the ability to run multiple tests in a row.
7) reworked the threading synchronization of the UI and threads.
8) Added some additional benchmarking tests.


I will examine the performance differences between Pervasive.SQL and NexusDB
in three phases: single user, multi-user and overall performance.

Single User

In the single user, Pervasive.SQL seems to perform consistently inserting a
few records in many transactions or a lot of records in few transactions.
NexusDB on the other hand, performs much better with a lot of records in a
few transactions. Pervasive.SQL outperforms NexusDB in the following test:

5 Latency 1 Users 100 Records 20000 Transactions

In this test there is a single user that is inserting a few records in many

transactions. Pervasive.SQL comes in at a time of 34:16, whereas NexusDB
times in at 01:17:36.

NexusDB outperforms Pervasive.SQL in all other single user cases. The most
sizeable difference is in a very similar test:

0 Latency 1 Users 2000000 Records 1 Transactions

In this test there is a single user that is inserting a lot of records in a
single transaction. NexusDB clocks in at 02:18 whereas Pervasive.SQL
consistently comes it at 36:04 (consistent performance given the total
number of records inserted in previous test).

Multi-User

In multi-user the relative performance is difficult to measure due to the
number of deadlock exceptions that Pervasive.SQL raised (Btrieve Error 78).
There was not a single test involving multiple threads that Pervasive.SQL
did not raise an exception.

Even more unsettling was the fact that the resulting record counts were not
multiples of the record count and successful transactions. For example on
this test:

Pervasive.SQL
5 Latency 5 Users 100 Records 100 Transactions
Elapsed time = 00:00:05
(Btrieve Error 78) raised count: 489
Total records = 50000
Actual Record Count: 1102

The actual record count in the table is 1102 records. The extra 2 records
do not correspond to a multiple of 100 records within a transaction.
Somehow 2 rogue records have been added although only 11 transactions were
committed. I can only conclude that Pervasive.SQL does not rollback
transactions correctly, the Delphi components do not communicate transaction
boundaries to the engine, or there is a bug in the benchmark code.

Despite the fact that Pervasive.SQL did not complete one multi-user test
correctly, it did not completely outperform NexusDB which accomplished every
multiuser without exception. Certainly the deadlock exceptions take time to
occur, however in some cases Pervasive.SQL takes about as much time to fail
as NexusDB does to do the actual work required. NexusDB performs
consistently in all the multi-user tests.

Overall

The overall performance measurements clearly demonstrates that NexusDB
simply outperforms Pervasive.SQL. In the very specific single user case
where Pervasive.SQL does outperform NexusDB, I would argue that this is a
very atypical situation. In the situations where bulk insertion from a
single user is typically required, it is not unreasonable to use a single
transaction which yields much better performance with NexusDB.

I will say that I am not a skilled Pervasive.SQL developer and there may be
some additional code or tuning tweaks possible. But I believe there are
more fundamental issues being revealed. I invite Pervasive.SQL advocates to
take a look, modify the code and tuning parameters to provide maximum
performance.

The most disturbing part of this benchmark is that Pervasive.SQL does not
handle concurrent users at all and there are rollback inconsistencies. This
does not make me feel comfortable storing data in Pervasive.SQL and
certainly qualifies as data corruption in my book. Given Pervasive.SQL's
pricing, unreliability, inability to scale to even 5 concurrent users and
the fact that there are quite capable alternatives, I cannot recommend
Pervasive.SQL for any purpose.


--
Brian Moelk [NDX]
bmoelk@NObrainendeavorSPAM.FORcomME
http://www.brainendeavor.com

IIRC <g>, tests run on: 800Mhz AMD Duron 768M RAM IDE

Latency Users Records Transactions Pervasive.SQL NexusDB
5 1 100 100 00:00:08
00:00:02
5 1 100 500 00:00:46
00:00:15
5 1 100 2000 00:03:02
00:01:39
5 1 100 20000 00:34:16
01:17:36
5 5 100 20 00:00:02 *
00:00:02
5 5 100 100 00:00:05 *
00:00:11
5 5 100 400 00:00:19 *
00:01:23
5 20 100 5 00:00:01 *
00:00:02
5 20 100 25 00:00:06 *
00:00:11
5 50 100 2 00:00:02 *
00:00:02
5 50 100 10 00:00:10 *
00:00:11
25 1 100 100 00:00:12
00:00:05
25 5 100 20 00:00:01 *
00:00:05
25 20 100 5 00:00:01 *
00:00:05
25 50 100 2 00:00:02 *
00:00:05
100 1 100 100 00:00:27
00:00:20
100 5 100 20 00:00:07 *
00:00:20
100 20 100 5 00:00:02 *
00:00:20
100 50 100 2 00:00:02 *
00:00:20
0 1 2000000 1 00:36:04
00:02:18
0 10 50000 1 00:00:42 *
00:00:34
0 10 50000 10 00:02:03 *
00:13:18
0 50 10000 1 00:00:09 *
00:00:48
0 50 10000 10 00:00:31 *
00:48:30

* did not complete test successfully

Raw Output Logs:

Pervasive.SQL
5 Latency 1 Users 100 Records 100 Transactions
Elapsed time = 00:00:08
Total records = 10000
Actual Record Count: 10000
=================================================
Pervasive.SQL
5 Latency 1 Users 100 Records 500 Transactions
Elapsed time = 00:00:46
Total records = 50000
Actual Record Count: 50000
=================================================
Pervasive.SQL
5 Latency 1 Users 100 Records 2000 Transactions
Elapsed time = 00:03:02
Total records = 200000
Actual Record Count: 200000
=================================================
Pervasive.SQL
5 Latency 1 Users 100 Records 20000 Transactions
Elapsed time = 00:34:16
Total records = 2000000
Actual Record Count: 2000000
=================================================
Pervasive.SQL
5 Latency 5 Users 100 Records 20 Transactions
Elapsed time = 00:00:02
(Btrieve Error 78) raised count: 96
Total records = 10000
Actual Record Count: 403
=================================================
Pervasive.SQL
5 Latency 5 Users 100 Records 100 Transactions
Elapsed time = 00:00:05
(Btrieve Error 78) raised count: 489
Total records = 50000
Actual Record Count: 1102
=================================================
Pervasive.SQL
5 Latency 5 Users 100 Records 400 Transactions
Elapsed time = 00:00:19
(Btrieve Error 78) raised count: 1992
Total records = 200000
Actual Record Count: 804
=================================================
Pervasive.SQL
5 Latency 20 Users 100 Records 5 Transactions
Elapsed time = 00:00:01
(Btrieve Error 78) raised count: 98
Total records = 10000
Actual Record Count: 201
=================================================
Pervasive.SQL
5 Latency 20 Users 100 Records 25 Transactions
Elapsed time = 00:00:06
(Btrieve Error 78) raised count: 497
Total records = 50000
Actual Record Count: 302
=================================================
Pervasive.SQL
5 Latency 50 Users 100 Records 2 Transactions
Elapsed time = 00:00:02
(Btrieve Error 78) raised count: 99
Total records = 10000
Actual Record Count: 101
=================================================
Pervasive.SQL
5 Latency 50 Users 100 Records 10 Transactions
Elapsed time = 00:00:10
(Btrieve Error 78) raised count: 499
Total records = 50000
Actual Record Count: 101
=================================================
Pervasive.SQL
25 Latency 1 Users 100 Records 100 Transactions
Elapsed time = 00:00:12
Total records = 10000
Actual Record Count: 10000
=================================================
Pervasive.SQL
25 Latency 5 Users 100 Records 20 Transactions
Elapsed time = 00:00:01
(Btrieve Error 78) raised count: 97
Total records = 10000
Actual Record Count: 303
=================================================
Pervasive.SQL
25 Latency 20 Users 100 Records 5 Transactions
Elapsed time = 00:00:01
(Btrieve Error 78) raised count: 97
Total records = 10000
Actual Record Count: 302
=================================================
Pervasive.SQL
25 Latency 50 Users 100 Records 2 Transactions
Elapsed time = 00:00:02
(Btrieve Error 78) raised count: 99
Total records = 10000
Actual Record Count: 101
=================================================
Pervasive.SQL
100 Latency 1 Users 100 Records 100 Transactions
Elapsed time = 00:00:27
Total records = 10000
Actual Record Count: 10000
=================================================
Pervasive.SQL
100 Latency 5 Users 100 Records 20 Transactions
Elapsed time = 00:00:07
(Btrieve Error 78) raised count: 76
Total records = 10000
Actual Record Count: 2416
=================================================
Pervasive.SQL
100 Latency 20 Users 100 Records 5 Transactions
Elapsed time = 00:00:02
(Btrieve Error 78) raised count: 97
Total records = 10000
Actual Record Count: 302
=================================================
Pervasive.SQL
100 Latency 50 Users 100 Records 2 Transactions
Elapsed time = 00:00:02
(Btrieve Error 78) raised count: 99
Total records = 10000
Actual Record Count: 101
=================================================
Pervasive.SQL
0 Latency 1 Users 2000000 Records 1 Transactions
Elapsed time = 00:36:04
Total records = 2000000
Actual Record Count: 2000000
=================================================
Pervasive.SQL
0 Latency 10 Users 50000 Records 1 Transactions
Elapsed time = 00:00:42
(Btrieve Error 78) raised count: 9
Total records = 500000
Actual Record Count: 50000
=================================================
Pervasive.SQL
0 Latency 10 Users 50000 Records 10 Transactions
Elapsed time = 00:02:03
(Btrieve Error 78) raised count: 97
Total records = 5000000
Actual Record Count: 150001
=================================================
Pervasive.SQL
0 Latency 50 Users 10000 Records 1 Transactions
Elapsed time = 00:00:09
(Btrieve Error 78) raised count: 49
Total records = 500000
Actual Record Count: 10000
=================================================
Pervasive.SQL
0 Latency 50 Users 10000 Records 10 Transactions
Elapsed time = 00:00:31
(Btrieve Error 78) raised count: 497
Total records = 5000000
Actual Record Count: 30001
=================================================


NexusDB
5 Latency 1 Users 100 Records 100 Transactions
Elapsed time = 00:00:02
Total records = 10000
Actual Record Count: 10000
=================================================
NexusDB
5 Latency 1 Users 100 Records 500 Transactions
Elapsed time = 00:00:15
Total records = 50000
Actual Record Count: 50000
=================================================
NexusDB
5 Latency 1 Users 100 Records 2000 Transactions
Elapsed time = 00:01:39
Total records = 200000
Actual Record Count: 200000
=================================================
NexusDB
5 Latency 1 Users 100 Records 20000 Transactions
Elapsed time = 01:17:36
Total records = 2000000
Actual Record Count: 2000000
=================================================
NexusDB
5 Latency 5 Users 100 Records 20 Transactions
Elapsed time = 00:00:02
Total records = 10000
Actual Record Count: 10000
=================================================
NexusDB
5 Latency 5 Users 100 Records 100 Transactions
Elapsed time = 00:00:11
Total records = 50000
Actual Record Count: 50000
=================================================
NexusDB
5 Latency 5 Users 100 Records 400 Transactions
Elapsed time = 00:01:23
Total records = 200000
Actual Record Count: 200000
=================================================
NexusDB
5 Latency 20 Users 100 Records 5 Transactions
Elapsed time = 00:00:02
Total records = 10000
Actual Record Count: 10000
=================================================
NexusDB
5 Latency 20 Users 100 Records 25 Transactions
Elapsed time = 00:00:11
Total records = 50000
Actual Record Count: 50000
=================================================
NexusDB
5 Latency 50 Users 100 Records 2 Transactions
Elapsed time = 00:00:02
Total records = 10000
Actual Record Count: 10000
=================================================
NexusDB
5 Latency 50 Users 100 Records 10 Transactions
Elapsed time = 00:00:11
Total records = 50000
Actual Record Count: 50000
=================================================
NexusDB
25 Latency 1 Users 100 Records 100 Transactions
Elapsed time = 00:00:05
Total records = 10000
Actual Record Count: 10000
=================================================
NexusDB
25 Latency 5 Users 100 Records 20 Transactions
Elapsed time = 00:00:05
Total records = 10000
Actual Record Count: 10000
=================================================
NexusDB
25 Latency 20 Users 100 Records 5 Transactions
Elapsed time = 00:00:05
Total records = 10000
Actual Record Count: 10000
=================================================
NexusDB
25 Latency 50 Users 100 Records 2 Transactions
Elapsed time = 00:00:05
Total records = 10000
Actual Record Count: 10000
=================================================
NexusDB
100 Latency 1 Users 100 Records 100 Transactions
Elapsed time = 00:00:20
Total records = 10000
Actual Record Count: 10000
=================================================
NexusDB
100 Latency 5 Users 100 Records 20 Transactions
Elapsed time = 00:00:20
Total records = 10000
Actual Record Count: 10000
=================================================
NexusDB
100 Latency 20 Users 100 Records 5 Transactions
Elapsed time = 00:00:20
Total records = 10000
Actual Record Count: 10000
=================================================
NexusDB
100 Latency 50 Users 100 Records 2 Transactions
Elapsed time = 00:00:20
Total records = 10000
Actual Record Count: 10000
=================================================
NexusDB
0 Latency 1 Users 2000000 Records 1 Transactions
Elapsed time = 00:02:18
Total records = 2000000
Actual Record Count: 2000000
=================================================
NexusDB
0 Latency 10 Users 50000 Records 1 Transactions
Elapsed time = 00:00:34
Total records = 500000
Actual Record Count: 500000
=================================================
NexusDB
0 Latency 10 Users 50000 Records 10 Transactions
Elapsed time = 00:13:18
Total records = 5000000
Actual Record Count: 5000000
=================================================
NexusDB
0 Latency 50 Users 10000 Records 1 Transactions
Elapsed time = 00:00:48
Total records = 500000
Actual Record Count: 500000
=================================================
NexusDB
0 Latency 50 Users 10000 Records 10 Transactions
Elapsed time = 00:48:30
Total records = 5000000
Actual Record Count: 5000000
=================================================