Paradox vs Microsoft Access

Written by Embarcadero USA on Posted in PROGRAMMING

Paradox vs Microsoft Access

Paradox vs Microsoft Access

Overview

This test is to compare various database operations performance on Paradox and Microsoft Access database. I did the testing is I want to know how far can these xbase database go. The test result affects my decision on choosing which database to deploy for my applications.

Author's comments

This is my personal opinion.  It is not an absolute answer or solution for these two databases.

I used Paradox + BDE for about 5 years.  It works fine for a single machine.  Although the database get corrupted and index out of date once a while, my customers still accept the errors and make no complaints.

As network setup cost keep sliding in these few years, some customers start expands their businesses.  They don't satisfy with the standalone model applications.  Instead, networking is the only answer for them.  To minimize the impact on changing my software's design, I configure my customers applications to work under networking environment by just doing a minor adjustment.  It works for a day on two.  As the work load keep increasing, the index out of date error keep happen.  Sometime, it happen so often until my customers lost confident on my applications.  

If deploy the database under Windows NT platform, turn off the opportunistic lock may reduce the frequency of index out of date.  This does help but user has to turn it off by manually.  This is not an easy task for users who do not know how to manage NT. Moreover, installing BDE or distribute BDE increase our burden.  Most Windows machine do not equip with BDE by default.

This has forced me to find a new database to substitute Paradox + BDE.  I found that Microsoft Access is the most common database under Windows platform.  Most of my customers' machine has Microsoft Office installed.  Using Access as the database and MDAC as the database engine soon become my first choice.  The MDAC, as stated by Microsoft, will be a standard in Windows platform.  MDAC is widely available for Windows 2000 platform.  Thus, deploying applications using Access and MDAC should be fairly simple and straight forward.

Before I dump my effort to re-design my application model, I study ADO and MDAC, I do this test to examine the ability of both databases.  I just can't afford for choosing an inappropriate technology for my applications.

By just doing half of the test, I already has a clue.  I am going to use MS-Access for my future applications.  Microsoft Access's transaction capability is out of my expectation and perform excellently well compare with Paradox.  I am able to rollback more than 10,000 modifications on Access database whereas Paradox can only rollback 300+ modifications. Moreover, A single file per Microsoft Access database makes distribution and backup easy.

Test Environment

Linux Machine

Pentium Pro 200MHz with 64M RAM
Linux Kernel Version: 2.2.5-15
Samba version: samba-2.0.6-19991110
Hard disk: IBM-DPTA-372050 19574MB w/1961KB Cache

Windows 95 Machine

Pentium 133Mhz with 32M RAM
Hard disk: 2.1GB Quantum Fireball

Local machine

AMD K6-2, 450Mhz with 64M RAM
OS: Windows NT Wosktation 4 with Service Pack 5 installed
Hard disk: 6.4Gb Quantum Fireball CX1

Network configuration

10Mbps, when doing testing, only client and server machines are working.

Database Engine

Paradox: BDE version 5.10, Paradox Driver Version 4.0 Table Level 7
Microsoft Access: Microsoft Access 2000 database. Engine version: Microsoft DAO 3.6 Object Library DBEngine

Access - Local

Application access Microsoft Access database stored in local machine.

Paradox - Local

Application access local Paradox database stored in local machine.

Access - Network(Samba)

Application access Microsoft Access database stored in Samba server.

Paradox - Network(Samba)

Application access Paradox database stored in Samba server.

Access - Network(Win95)

Application access Microsoft Acess database stored in Windows 95 machine.

Paradox - Network(Win95)

Application access Paradox database stored in Windows 95 machine.

Test Result

Due to time constraint, I did 9 test cases only:

Case 1: Edit Record

Access Local Access Network (Samba) Paradox Local Paradox Network (Samba) Access Network (Win95) Paradox Network (Win95)
500 0:00:00 0:00:00 0:00:00 0:00:01 0:00:02 0:00:02
1000 0:00:01 0:00:01 0:00:00 0:00:02 0:00:03 0:00:05
1500 0:00:01 0:00:01 0:00:00 0:00:02 0:00:04 0:00:06
2000 0:00:01 0:00:01 0:00:00 0:00:03 0:00:06 0:00:10
3000 0:00:02 0:00:09 0:00:00 0:00:05 0:00:10 0:00:13
4000 0:00:04 0:00:10 0:00:01 0:00:06 0:00:14 0:00:15
5000 0:00:05 0:00:11 0:00:01 0:00:08 0:00:18 0:00:49
6000 0:00:07 0:00:18 0:00:01 0:00:10 0:00:22 0:00:24
7000 0:00:09 0:00:22 0:00:01 0:00:18 0:00:27 0:00:32
8000 0:00:11 0:00:26 0:00:21 0:00:13 0:00:32 0:00:33
9000 0:00:14 0:00:31 0:00:21 0:00:14 0:00:36 0:00:41
10000 0:00:16 0:00:31 0:00:36 0:00:17 0:00:41 0:00:44

Case 2: Insert Record (w/ Primary Key)

Access Local Access Network (Samba) Paradox Local Paradox Network (Samba) Access Network (Win95) Paradox Network (Win95)
500 0:00:09 0:00:09 0:00:02 0:00:06 0:01:23 0:00:41
1000 0:00:16 0:00:16 0:00:03 0:00:11 0:02:48 0:01:19
1500 0:00:24 0:00:24 0:00:06 0:00:13 0:04:19 0:01:53
2000 0:00:33 0:00:34 0:00:10 0:00:21 0:05:58 0:02:31
3000 0:00:51 0:00:58 0:00:44 0:00:30 0:09:55 0:03:45
4000 0:01:07 0:01:08 0:00:56 0:00:38 0:14:00 0:05:12
5000 0:01:24 0:01:35 0:01:00 0:00:49 0:16:04 0:06:15
6000 0:01:43 0:01:57 0:01:13 0:00:54 0:19:12 0:07:32
7000 0:02:00 0:02:32 0:01:25 0:01:04 0:23:38 0:08:48
8000 0:02:23 0:03:08 0:01:33 0:01:14 0:25:50 0:10:05
9000 0:02:38 0:03:21 0:01:49 0:01:21 0:29:09 0:11:13
10000 0:02:52 0:03:29 0:02:02 0:01:31 0:32:24 0:12:44

Case 3: Insert Record (w/o Primary Key)

Access Local Access Network (Samba) Paradox Local Paradox Network (Samba) Access Network (Win95) Paradox Network (Win95)
500 0:00:07 0:00:06 0:00:00 0:00:01 0:00:45 0:00:08
1000 0:00:13 0:00:12 0:00:00 0:00:02 0:01:34 0:00:14
1500 0:00:22 0:00:18 0:00:01 0:00:03 0:02:16 0:00:20
2000 0:00:29 0:00:25 0:00:01 0:00:03 0:03:07 0:00:26
3000 0:00:41 0:00:39 0:00:10 0:00:05 0:04:51 0:00:52
4000 0:00:54 0:00:51 0:00:11 0:00:07 0:06:25 0:00:52
5000 0:01:14 0:01:05 0:00:13 0:00:08 0:08:04 0:01:06
6000 0:01:24 0:01:22 0:00:16 0:00:13 0:09:22 0:01:20
7000 0:01:40 0:01:33 0:00:18 0:00:14 0:11:08 0:01:33
8000 0:01:56 0:01:49 0:00:28 0:00:17 0:12:34 0:01:46
9000 0:02:10 0:02:05 0:00:32 0:00:18 0:14:10 0:02:01
10000 0:02:27 0:02:24 0:00:36 0:00:20 0:15:51 0:02:11

Case 4: SQL Delete w/ Primary Key

Access Local Access Network (Samba) Paradox Local Paradox Network (Samba) Access Network (Win95) Paradox Network (Win95)
500 0:00:00 0:00:00 0:00:00 0:00:01 0:00:00 0:00:32
1000 0:00:00 0:00:00 0:00:01 0:00:01 0:00:00 0:01:05
1500 0:00:00 0:00:00 0:00:02 0:00:02 0:00:00 0:01:30
2000 0:00:00 0:00:00 0:00:02 0:00:02 0:00:00 0:01:59
3000 0:00:00 0:00:00 0:00:04 0:00:04 0:00:00 0:03:16
4000 0:00:00 0:00:00 0:00:05 0:00:12 0:00:00 0:04:01
5000 0:00:00 0:00:00 0:00:07 0:00:14 0:00:00 0:05:07
6000 0:00:00 0:00:00 0:00:14 0:00:21 0:00:00 0:06:17
7000 0:00:00 0:00:00 0:00:17 0:00:27 0:00:00 0:07:08
8000 0:00:00 0:00:00 0:00:24 0:00:35 0:00:00 0:08:05
9000 0:00:00 0:00:00 0:00:36 0:00:42 0:00:00 0:09:03
10000 0:00:00 0:00:00 0:01:08 0:00:54 0:00:00 0:10:16

Case 5: SQL Delete w/o Primary Key

Access Local Access Network (Samba) Paradox Local Paradox Network (Samba) Access Network (Win95) Paradox Network (Win95)
500 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:07
1000 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:13
1500 0:00:00 0:00:00 0:00:01 0:00:01 0:00:00 0:00:21
2000 0:00:00 0:00:00 0:00:01 0:00:01 0:00:00 0:00:26
3000 0:00:00 0:00:00 0:00:01 0:00:01 0:00:00 0:00:41
4000 0:00:00 0:00:00 0:00:02 0:00:02 0:00:00 0:00:53
5000 0:00:00 0:00:00 0:00:02 0:00:02 0:00:00 0:01:07
6000 0:00:00 0:00:00 0:00:03 0:00:03 0:00:00 0:01:24
7000 0:00:00 0:00:00 0:00:03 0:00:03 0:00:00 0:01:34
8000 0:00:00 0:00:00 0:00:06 0:00:06 0:00:00 0:01:47
9000 0:00:00 0:00:00 0:00:35 0:00:09 0:00:00 0:02:01
10000 0:00:00 0:00:00 0:00:43 0:00:14 0:00:00 0:02:15

Case 6: SQL Join

Access Local Access Network (Samba) Paradox Local Paradox Network (Samba) Access Network (Win95) Paradox Network (Win95)
500 0:00:00 0:00:00 0:00:02 0:00:02 0:00:00 0:00:06
1000 0:00:01 0:00:01 0:00:04 0:00:05 0:00:01 0:00:10
1500 0:00:01 0:00:01 0:00:06 0:00:08 0:00:01 0:00:15
2000 0:00:02 0:00:02 0:00:08 0:00:11 0:00:03 0:00:21
3000 0:00:03 0:00:04 0:00:12 0:00:17 0:00:07 0:00:31
4000 0:00:04 0:00:05 0:00:17 0:00:23 0:00:10 0:00:39
5000 0:00:05 0:00:05 0:00:38 0:00:30 0:00:12 0:00:48
6000 0:00:08 0:00:06 0:00:48 0:00:36 0:00:15 0:01:00
7000 0:00:13 0:00:08 0:00:42 0:00:43 0:00:18 0:01:07
8000 0:00:20 0:00:11 0:00:52 0:00:48 0:00:22 0:01:18
9000 0:00:43 0:00:17 0:01:29 0:00:57 0:00:34 0:01:29
10000 0:00:35 0:00:20 0:01:44 0:01:04 0:00:39 0:01:56

Case 7: SQL Record Count

Access Local Access Network (Samba) Paradox Local Paradox Network (Samba) Access Network (Win95) Paradox Network (Win95)
500 0:00:00 0:00:00 0:00:00 0:00:01 0:00:00 0:00:01
1000 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:02
1500 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:03
2000 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:05
3000 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:06
4000 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:06
5000 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:12
6000 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:10
7000 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:16
8000 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:14
9000 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:20
10000 0:00:00 0:00:00 0:00:07 0:00:02 0:00:00 0:00:18

Case 8: SQL Select

Access Local Access Network (Samba) Paradox Local Paradox Network (Samba) Access Network (Win95) Paradox Network (Win95)
500 0:00:01 0:00:01 0:00:00 0:00:01 0:00:01 0:00:01
1000 0:00:01 0:00:01 0:00:00 0:00:01 0:00:01 0:00:01
1500 0:00:02 0:00:02 0:00:01 0:00:01 0:00:02 0:00:02
2000 0:00:02 0:00:02 0:00:01 0:00:02 0:00:03 0:00:02
3000 0:00:03 0:00:04 0:00:02 0:00:03 0:00:05 0:00:03
4000 0:00:04 0:00:05 0:00:03 0:00:03 0:00:06 0:00:04
5000 0:00:06 0:00:06 0:00:04 0:00:04 0:00:07 0:00:05
6000 0:00:07 0:00:08 0:00:04 0:00:05 0:00:09 0:00:06
7000 0:00:08 0:00:09 0:00:06 0:00:06 0:00:11 0:00:08
8000 0:00:10 0:00:11 0:00:06 0:00:07 0:00:12 0:00:08
9000 0:00:11 0:00:12 0:00:07 0:00:08 0:00:14 0:00:09
10000 0:00:13 0:00:14 0:00:09 0:00:10 0:00:16 0:00:11

Case 9: SQL Update

Access Local Access Network (Samba) Paradox Local Paradox Network (Samba) Access Network (Win95) Paradox Network (Win95)
500 0:00:00 0:00:00 0:00:00 0:00:01 0:00:01 0:00:09
1000 0:00:00 0:00:00 0:00:00 0:00:02 0:00:01 0:00:15
1500 0:00:00 0:00:00 0:00:01 0:00:03 0:00:02 0:00:23
2000 0:00:00 0:00:00 0:00:01 0:00:04 0:00:03 0:00:31
3000 0:00:01 0:00:01 0:00:01 0:00:05 0:00:06 0:00:46
4000 0:00:01 0:00:01 0:00:02 0:00:08 0:00:09 0:00:56
5000 0:00:01 0:00:01 0:00:04 0:00:14 0:00:13 0:01:15
6000 0:00:01 0:00:01 0:00:13 0:00:19 0:00:15 0:01:26
7000 0:00:02 0:00:02 0:00:20 0:00:25 0:00:18 0:01:43
8000 0:00:02 0:00:02 0:00:32 0:00:28 0:00:19 0:01:56
9000 0:00:02 0:00:02 0:00:52 0:00:32 0:00:22 0:02:13
10000 0:00:03 0:00:02 0:01:30 0:00:39 0:00:25 0:02:27

Conclusion

In normal database activity, Insert, Edit and Delete operations are not as heavy as SELECT and JOIN. Most Insert/Edit/Delete operation are done via form entry which perform on a single row. Whereas SELECT and JOIN are heavy operations for generating report and inquiry request. The result in case 6 shows that JOIN operation for Microsoft Access has significant different compare with Paradox database. In case 8, Paradox did well in SELECT operation but with just few seconds different.

The result gave me strong confident to deploy Microsoft Access datbase solution to my customer.

This is the first time I did such testing, they may be things I didn't consider and thus the result may not be accurate. Anyway, comments are welcome for those testing.

Author

Chau Chee-Yang
Business Component Enterprise Sdn. Bhd.
Email: This email address is being protected from spambots. You need JavaScript enabled to view it.

Article originally contributed by Chee Yang Chau

Tags: Delphi Database Platforms Programming Linux Performance



Check out more tips and tricks in this development video: