1. Technology
RTL reference|Glossary|Tips/Tricks|FREE App/VCL|Best'O'Net|Books|Link To

Paradox vs Microsoft Access
This test is to compare various database operations performance on Paradox and Microsoft Access database.
Join the Discussion
"Post your questions, concerns, views and comments to this article..."
Discuss!
Related Resources
Delphi DB articles
Free DB-ADO Course
Interbase and Delphi
From Other Guides
About Databases

Article submitted by: Chee Yang Chau.

My personal opinion
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 are not satisfied 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 customer 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 results
Case 1: Edit Record
Case 2: Insert Record (w/ Primary Key)
Case 3: Insert Record (w/o Primary Key)
Case 4: SQL Delete w/ Primary Key
Case 5: SQL Delete w/o Primary Key
Case 6: SQL Join
Case 7: SQL Record Count
Case 8: SQL Select
Case 9: SQL Update

Case 1: Edit Record

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)

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)

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

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

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

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

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

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

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, the Insert, Edit and Delete operations are not as heavy as SELECT and JOIN. Most Insert/Edit/Delete operation are done via the form entry which perform operations 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 database 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.

Don't forget to post your questions, concerns, views and comments to this article on the Delphi Programming Forum.

All graphics (if any) in this feature created by Zarko Gajic.

More Delphi
· Learn another routine every day - RTL Quick Reference.
· Download free source code applications and components.
· Talk about Delphi Programming, real time.
· Link to the Delphi Programming site from your Web pages.
· Tutorials, articles, tech. tips by date: 2001|2000|1999|1998 or by TOPIC.
· NEXT ARTICLE: Delphi 6 is out there.
Read about exciting new features in Delphi 6.
Stay informed with all new and interesting things about Delphi (for free).
Subscribe to the Newsletter
Name
Email

Got some code to share? Got a question? Need some help?

©2014 About.com. All rights reserved.