Mega Code Archive
Paradox vs Microsoft Access
<![CDATA[
Title: Paradox vs Microsoft Access
Question: 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.
Answer:
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 RAMLinux Kernel Version: 2.2.5-15Samba
version: samba-2.0.6-19991110Hard disk: IBM-DPTA-372050 19574MB w/1961KB
Cache
Windows 95 Machine
Pentium 133Mhz with 32M RAMHard disk: 2.1GB Quantum Fireball
Local machine
AMD K6-2, 450Mhz with 64M RAMOS: Windows NT Wosktation 4 with Service
Pack 5 installedHard disk: 6.4Gb Quantum Fireball CX1
Network configuration
10Mbps, when doing testing, only client and server machines are working.
Database EngineParadox: BDE version 5.10, Paradox Driver Version 4.0
Table Level 7Microsoft Access: Microsoft Access 2000 database. Engine
version: Microsoft DAO 3.6 Object Library DBEngine
Access - LocalApplication access Microsoft Access database stored in local
machine.
Paradox - LocalApplication 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
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
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.
AuthorChau Chee-YangE STREAM Software Sdn.
Bhd.Email: ccy@sql.com.my
]]>