Borland Delphi

[ City Zoo | Announcements | Articles | Tips & Tricks | Bug List | FAQ | Sites ]


Database Time Test Scores

by Ben Licht

Contents:

1. Methods
2. Tests Performed
3. Non-Indexed Tables
3.1 Creating Records in a Non-Indexed Table
3.2 Random Record Jumping in a Non-Indexed Table
3.3 Record Seeks in a Non-Indexed Table
3.4 Sequential Movement through a Non-Indexed Table
3.5 Conclusions
4. Indexed Tables
4.1 Creating Records in an Indexed Table
4.2 Random Record Jumping in an Indexed Table
4.3 Record Seeks & Sequential Movement in an Indexed Table
4.4 Conclusions

1. Methods

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).


2. Tests Performed

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:

  1. Paradox 5.0
  2. Access 1.1 w/ ODBC (via Delphi)
  3. Access 1.1 w/o ODBC (via VB)
  4. Interbase
  5. DBase for Windows/DBase IV

3. Non-Indexed Tables

3.1 Creating Records in a Non-Indexed Table

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.

3.2 Random Record Jumping in a Non-Indexed Table

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.

3.3 Record Seeks in a Non-Indexed Table

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.

3.4 Sequential Movement through a Non-Indexed Table

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

3.5 Conclusions

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.


4. Indexed Tables

4.1 Creating Records in an Indexed Table

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

4.2 Random Record Jumping in an Indexed Table

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)

4.3 Record Seeks & Sequential Movement in an Indexed Table

In all cases, seek times, and move sequentially through records took no time (at least no-time when rounded to the nearest second).

4.4 Conclusions

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:

  1. Changing to a SQL server (upsizing) will require serious recoding of your program if you use TTable.
  2. You cannot do wildcard searchs or "like" type searches with the TTable. You can do wild card searches if you know the first letters, but not if you only know the last letters.

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.

Ben Licht


[ Home Page | What's New | About CITY ZOO | Borland Delphi | About the Authors | INDEX ]
benl@panix.com
Copyright © 1995 Ben Licht. This is a CITY ZOO production.
Last revised July 7, 1995.
Enhanced version