|
Log in |
FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 ================================================= |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
2.0.599r3: Query that should work returns Unable to resolve the identifier"#t1"."ContratoId" | =?ISO-8859-1?Q?=22Rodrigo_G=F3mez_=5BNDX=5D=22?= | nexusdb.public.support.sql | 2 | 4th November 2006 11:03 AM |
SQL query to search on a field that is "similar to a set" or "mask"? | J Tabor | nexusdb.public.support.sql | 2 | 10th May 2006 03:20 PM |
Announcment! "The Keep" ver 2.0.0.1 and "Po Boy Application LifeCycle Management Utility" ver 1.0.0.l are now both available for free download! | Robert Meek | nexusdb.public.support.thirdparty | 1 | 11th July 2004 11:23 AM |
is "nxDatabase.Failsafe" = "Nexus Server-ForceFailSafe"? | Hugo Galindo | nexusdb.public.support | 7 | 23rd September 2003 07:41 PM |
Import CSV: Selecting "Overwrite" in "Advanced option" gives "Abstract error" when "Finish" is pressed | Jesper Østergaard | nexusdb.public.support | 1 | 6th July 2003 01:10 PM |