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 EngineParadox: 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 - 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
|
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-Yang
E STREAM Software Sdn.
Bhd.
Email: ccy@sql.com.my
|
