[ City Zoo | Announcements | Articles | Tips & Tricks | Bug List | FAQ | Sites ]
Okay, I will go over my statistical results -- although fair warning to anyone -- statistics are always a very dry subject -- and database speed tests are NO exception.
I started my experiment with 100,000 records. The reason for this is that I read that Paradox loads in a lot of DLLs before it initally makes a query, therefore, small tables would make Paradox seem much slower than it really was (incidently my statistics agree with this statement). I also wanted my table to have fields that would represent a fair range of field types. I therefore used the following three fields:
The tests were performed on a Pentium 66 MHz machine with 16MB of RAM and more than ample disk space (after all the tables were made there was still 300 MB of disk space left).
I divided up my tests as follows:
Tests were made on Databases that had no indices, and Databases that were indexed by a unique primary field of Recnum. All searchs where made on this field (in both the indexed and non-indexed case).
Tests were made in:
There were exactly 5 different types of Databases tested:
This test created 100,000 records in a non indexed table. This is generally the fastest way to create records since there is no index to maintain, the database posting record speed was not hindered.
Creation of 100,000 records in a non-indexed table: Database Time Average Records/Second ------------- ---------- ------------------------ Paradox 5.0 62 min 26.88 rec/s Access - ODBC 134 min 12.44 rec/s Access - VB -- -- Interbase 34 min 49.00 rec/s (*) Dbase 10 min 167.0 rec/s * Interbase had the advantage of using starttrans and commit to post records in 100 record blocks.
At first glance it might appear that the Access-ODBC is the sure fire looser, but in fact, as you will see below, the extra posting time is more than justified in Access ability to search non-indexed fields.
This test randomly jumped to 10 records via SQL statements. It should be noted that in a non-indexed DB earlier records are quicker to be found. This means that if the 10 random jumps were 5, 9, 100, 30, 90, 60, 80, 10, 500, and 2, the speed of such a search would be significantly faster than another more average search around 50,000. Therefore, I also considered the average record number found in the 10 searchs and only accepted results that averaged from 48,000 to 53,000.
Non-Indexed random search results (10 Jumps): Database Time Average Seconds/Record ------------- ---------- ------------------------ Paradox 5.0 3.0 min 18.0 s/rec Access - ODBC 4.0 min 24.0 s/rec Access - VB 1.75 min 10.5 s/rec Interbase 4.0 min 24.0 s/rec DBase 1.35 min 8.1 s/rec
When it comes to searching non-indexed fields, nothing beats DBase. You should note than DBase creation time was the best as well. However, DBase does have (what I think is) a tragic flaw. I will point this out later in the document.
Basically, I searched for record 1, 50000, and 100000 and recorded the time it took to reach each record. Here are the results (rec 1 = record 1, rec 2 = record 50000, and rec 3 = 100,000):
Record Seeks in a Non-Indexed Table: Time to reach Record -------------------------- Database Rec. 1 Rec. 2 Rec. 3 ------------- ------ ------ ------ Paradox 5.0 28 s 17 s 18 s Access - ODBC 0 s 21 s 40 s Access - VB -- -- -- Interbase 1 s 28 s 57 s DBase 1 s 8 s 14 s
Again DBase is the fastest when the table is non-indexed.
The final test on the non-indexed databases was the ability to move sequentially through the first 50,000 records and then move back through them to record 1. The following are the recorded times:
Movement through a Non-Indexed Table: Database Forward Backward ------------- --------- ---------- Paradox 5.0 9 s 8 s Access - ODBC 220 s 12 s Access - VB -- -- (*) Interbase -- -- DBase 0 s 0 s * unknown but a lot faster than ODBC version
The conclusion is that DBase works vastly superior to any of these other Databases when dealling with a non-indexed table.
However, as you might recall, I mentioned that DBase had a major flaw. DBase does not use the indices that you setup for it when you use an SQL command on the table. This means that when using the SQL commands to search for an indexed property you will get the same performance results that you would get from a non-indexed property. Therefore, in the second run of all these tests, the DBase results are the same as in the first run (and compared to the speed of indexed searches they are not very good). The only exception to this is in the Database creation area where the length of time did change to create the index.
Creating an indexed database (unique index to be exact). The time results were as follows (for 100,000 records):
Creation of 100,000 records in an indexed table: Database Time Average Records/Second ------------- ---------- ------------------------ Paradox 5.0 240 min 6.9 rec/s Access - ODBC 90 min 18.5 rec/s Access - VB -- -- Interbase 41 min 40.7 rec/s DBase 14 min 119. rec/s
Since jumping to a given record for an indexed table is very quick the number of jumps used was 1000 (as opposed to 10 for a NON-indexed table). Note: Remeber, jumps were performed by SQL statement searches, not by moveby command.
Indexed random search results (1000 Jumps): Database Time Average Seconds/Record ------------- ---------- ------------------------ Paradox 5.0 44 s 22.7 rec/s Access - ODBC 53 s 18.9 rec/s Access - VB 40 s 25.0 rec/s Interbase 210 s 4.76 rec/s DBase (1 s short of eternity)
In all cases, seek times, and move sequentially through records took no time (at least no-time when rounded to the nearest second).
First, let me run off an obvious conclusion: Interbase is a real dog. Don't even bother. It's only performace boost came in creating records and that was due to using the starttrans, commit type updates (a feature that VB access supports as well -- although it was not used in these test -- had it been used, it is obvious that Access would have had comparable performace to DBase in record creation).
Paradox did well when it came to searching on a indexed field, but when looking for a non-indexed field it was near hopeless. Furthermore, once you indexed the Paradox fields, record update time suffered a 4 times decrease in performace (and that was with only 1 index). When you consider that you would need to index all possible query fields to get Paradox to perform okay you will realize that the update time is astronomical. Therefore, Paradox is pretty much not in the running to be an effecient DB. (Sorry Paradox fans).
The ODBC driver for Access slows it down enough so that, by Delphi, Paradox is a better choice, BUT if you were to use VB your applicaiton would really cruise. This is unfortunate, because many business application are Database intensive and Borland really blew it in that capacity. There is however, once saving grace for Delphi's Database access. If you use the TTable component instead of TQuery and use the setkey (and applyrange) type commands, then you can access DBase's indices. When you do this, DBase is significantly faster than VB Access.
DBase did 1000 random searches in 33 seconds, a rate of 30 rec/s.
The TTable component will allow you to make relations between various tables as well.
There are two negatives to using the TTable component as I see it:
The best performace, therefore, is if you can use DBase, and do table searches for things that you have indices for, and SQL searchs for non-indexed fields -- this gives you the best of both worlds. However, there are obviously cases where a TTable component is not viable. If this is the case and you have a lot of Database activity in your program, you will get a major performace boost from VB.
Incidently, DBase does support full record locking, so that if you had to put it on a server or have some other sort of Multi-user interface you can do it through DBase.
Well, thats the end of my report. I would recommend that someone else also run a similar set of tests (yes, it does take a lot of time) to make sure that my results are correct.