Performance Comparison between Access and Firebird

Firebird is better than Access in general, but it is not as good as the legend, at least in the Net environment. 1. test environment A. System Environment Operating System: WindowsXPProfessionalServerPack2CPU: Intel (R) Pentium (R) 4CPU3. 00GHZ2. 99GHZ memory: 1 GB, code environment NET2.0, Access2003, Firebir

Firebird is better than Access in general, but it is not as good as the legend, at least in the Net environment. 1. test environment A. System Environment OS: Windows XP Professional Server Pack 2 CPU: Intel (R) Pentium (R) 4 CPU 3.00 GHZ 2.99 GHZ memory: 1g B, code environment NET2.0, Access2003, Firebir

Firebird is better than Access in general, but it is not as good as the legend, at least in the Net environment.

1. Test Environment

A. System Environment

Operating System: Windows XP Professional Server Pack 2

CPU: Intel (R) Pentium (R) 4 CPU 3.00 GHZ 2.99 GHZ

Memory: 1 GB

B. Code Environment

NET2.0, Access2003, Firebird2.1,

Firebird's NET2.0 access API (FirebirdSql. Data. FirebirdClient. dll)

2. Databases

EmployeeInfo table:

CREATE TABLE EMPLOYEEINFO(
EID Integer NOT NULL,
ENAME Varchar(50),
ETELEPHONE Varchar(50),
EMOBILE Varchar(50),
EADDRESS Varchar(200),
EWORK Varchar(50),
ECOMPANY Varchar(50),
EAGE Integer,
ESCHOOL Varchar(50),
EBIRTHDAY Date,
EFAVOURATE Varchar(500),
ISMVP Integer,
ELEVEL Integer,
ENABLE_FLAG Integer,
CONSTRAINT EMPLOYEEINFO_NAME PRIMARY KEY (EID)
);

3. Test code

See attached files

4. Performance Comparison

The following number is in milliseconds. The performance of the two databases is recorded in 4.1. Because the performance of other operation connections is basically the same, it is omitted in other comparisons.

Add operation: You need to obtain the ID from the data table, so you need to execute ExecuteScalar

Access: SELECT max (EId) + 1 FROM EmployeeInfo

Firebird: SELECT first 1 GEN_ID (EMPLOYEEINFO_KEY_GEN, 1) from rdb $ GENERATORS

Pre-editing optimization: This method uses the IDbCommand Prepare method.

4.1 comparison of operations on a single piece of data

1. New operations

	IDbConnection. Open ()	IDbCommand. ExecuteScalar ()	IDbCommand. ExecuteNonQuery ()
Access	174.238416	102.448561	41.695030
	159.298931	101.921224	41.537487
	185.202748	102.383310	36.008230
FireBird	381.801163	59.864800	38.652679
	360.196079	69.475482	39.371224
	343.838800	60.606686	39.241575

2. Modification Operation

	Access	FireBird
IDbCommand. ExecuteNonQuery ()	125.531627	88.544622
	105.508891	88.177334
	145.817176	107.016208

Comparison of operations on 4.2 and 100 pieces of data

1. New operations

	IDbCommand. ExecuteScalar ()	IDbCommand. ExecuteNonQuery ()
Access	275.494317	234.020361
	261.396954	237.707107
	252.611140	253.758009
Pre-compilation Optimization	124.001096	100.539268
	124.581257	98.269848
	125.422189	99.034516
Pre-compilation + transaction control	156.688199	99.945657
	116.741034	80.133735
	113.269134	82.601144
FireBird	838.318433	969.816292
	887.597984	1064.949756
	818.385955	1022.706634
Pre-compilation Optimization	308.331690	437.868342
	283.292181	551.306577
	222.096816	455.877916
Pre-compilation + transaction control	70.281354	109.981409
	72.199458	96.185741
	69.851572	91.551454

2. Modification Operation

	Access	FireBird
IDbCommand. ExecuteNonQuery ()	411.009308	913.508742
	396.797053	868.117194
	399.259210	912.881623
Pre-compilation Optimization	177.652866	692.759320
	163.982479	709.243510
	171.324164	644.216015
Pre-compilation + transaction control	158.654429	106.195976
	154.795059	101.715139
	157.486357	104.424021

Comparison of operations on 4.3 and 1000 pieces of data

1. New operations

	IDbCommand. ExecuteScalar ()	IDbCommand. ExecuteNonQuery ()
Access	1651.840012	2133.541653
	1663.862358	2144.262530
	1631.403159	2135.223692
Pre-compilation Optimization	796.962979	808.875114
	785.243696	793.758126
	809.209726	797.399235
Pre-compilation + transaction control	728.416438	610.310033
	873.088523	898.503055
	673.583191	603.249033
FireBird	7737.366552	9359.178169
	7308.689064	10904.423101
	7724.148976	11846.604215
Pre-compilation Optimization	3716.587264	5723.248900
	3234.737922	5430.311542
	2686.714810	4821.239747
Pre-compilation + transaction control	522.050014	642.658276
	522.211388	665.879242
	532.323116	658.373523

2. Modification Operation

	Access	FireBird
IDbCommand. ExecuteNonQuery ()	3290.740559	7873.507740
	3991.333695	7822.996734
	3293.068174	7116.759956
Pre-compilation Optimization	1398.160890	6482.893171
	1254.979979	6302.055985
	1245.802121	6272.648019
Pre-compilation + transaction control	1097.316477	648.313099
	1221.636742	648.390276
	1104.532568	648.983446

Comparison of operations on 4.4 and 10000 pieces of data

1. New operations

	IDbCommand. ExecuteScalar ()	IDbCommand. ExecuteNonQuery ()
Access	15321.344697	20695.870283
	15522.056899	20775.041631
	15319.349251	20727.514825
Pre-compilation Optimization	10627.689828	9980.130051
	11161.361432	10432.259290
	10580.619317	9925.817398
Pre-compilation + transaction control	6191.647891	6037.020082
	6855.991305	6306.552880
	6659.638395	6042.067384
FireBird	92770.835360	119561.011190
	115369.304783	143528.391259
	135761.012112	165465.676440
Pre-compilation Optimization	61204.197587	94345.156610
	36930.112494	57278.146122
	40012.081468	66210.081814
Pre-compilation + transaction control	5407.627206	6910.738469
	5488.005238	7106.846560
	5524.538831	6740.408060

2. Modification Operation

	Access	FireBird
IDbCommand. ExecuteNonQuery ()	39694.855804	99310.751707
	35354.716525	90011.911178
	36534.236655	91112.061482
Pre-compilation + transaction control	10469.019093	7230.535415
	10444.395741	7682.581104
	10329.116616	7390.059610

Comparison of operations on 4.5 and 100000 pieces of data

1. New operations

	IDbCommand. ExecuteScalar ()		IDbCommand. ExecuteNonQuery ()
Access	198287.389450		223781.708768
	207229.904897		227152.302183
	236267.203150		251924.067059
Pre-compilation Optimization	75745.455466		80136.166440
	80215.392531		84041.511179
	83531.057454		85371.502942
Pre-compilation + transaction control	73753.320106		62696.035496
	70442.642879		69222.947557
	79447.569370		70056.168140
FireBird	> 30 minutes
Pre-compilation Optimization	297619.975597	551716.871984
Pre-compilation + transaction control	50412.421478	62230.369322
	52912.052985	69931.034354
	52509.019944	66763.649792

2. Modification Operation

	Access	FireBird
IDbCommand. ExecuteNonQuery ()	332451.315712	1260805.499906
	347068.025903
Pre-compilation Optimization	164528.339360	643502.447928
Pre-compilation + transaction control	108129.478762	81140.664313

Comparison of operations on 4.6 and 500000 pieces of data

1. New operations

	IDbCommand. ExecuteScalar ()	IDbCommand. ExecuteNonQuery ()
Pre-compiled and optimized Access	479207.809593	465971.617839
	377229.922041	367370.094465
Pre-compilation + transaction control	336857.065763	316500.809166
Pre-compiled and optimized FireBird	> 60 minutes
Pre-compilation + transaction control	273555.344525		361675.703063

2. Modification Operation

	Access	FireBird
Pre-compilation + transaction control	512516.135296	473002.155994

Query and comparison of 4.7 and 100 pieces of data

	Access	FireBird
SELECT * FROM table	561.603041	705.621894
	528.617866	804.226516
SELECT * From table WHERE name like '%... %'	531.510943	720.582087
	525.499398	761.811122

Query and comparison of 4.8 and 1000 pieces of data

	Access	FireBird
SELECT * FROM table	588.116789	771.333159
	615.835833	743.432148
SELECT * From table WHERE name like '%... %'	557.460599	715.724471
	564.812336	724.736215

Query and comparison of 4.9 and 10000 pieces of data

	Access	FireBird
SELECT * FROM table	1134.614770	1337.971064
	1015.374508	1261.249305
SELECT * From table WHERE name like '%... %'	737.451880	925.413277
	751.952307	910.842727

Query and comparison of 4.10 and 100000 pieces of data

	Access	FireBird
SELECT * FROM table	6501.658483	6335.985464
	5426.486788	6899.610531
SELECT * From table WHERE name like '%... %'	3204.588434	3298.303960
	3203.261492	3810.441583

Query and comparison of 4.11 and 500000 pieces of data

	Access	FireBird
SELECT * FROM table	28380.649119	34032.733181
	28227.096199	34557.834127
SELECT * From table WHERE name like '%... %'	18065.770127	19266.049635
	18412.904426	17163.350933

4.12 comparison of database file growth volume

The increase in Access file size is terrible. About 1000000 pieces of data can basically reach the Access limit (2 GB)

Comparing the increase in Firebird file size with Access, Firebird file size is much smaller than Access, which is basically several different levels.

The following are the files after a large number of operations:

Firebird 94808KB

Access limit 3424kb

Without compressing the database, Access increases by about to 2 GB, and Firebrid increases by about to 2 GB.

5. Test Summary

Based on the above performance comparison, we can draw the following conclusions:

1. For a large volume of data operations, you must use the pre-compilation or batch commit method. If it is in Firebird, you must add a transaction for processing, because in Firebird, the transaction performance can be improved by about 6-10 times. In Access, although the performance is not improved much, it is best to add transaction control. This increases the atomicity of operations and reduces the number of database reads and writes.
2. Access generally supports about 2 GB of data. When the data volume exceeds this limit, Access cannot write data. Therefore, this parameter is used when the data volume is 2 GB. Firebird supports data larger than/equal to 16 GB, and the overall performance after optimization is better than Access.
3. Without compression, if a large number of data operations (New/modified) are performed on Access, the data size increases by several times that of Firebird, generally, data cannot be inserted if it increases by more than continuously. Firebird is good at this point, and there are no restrictions at the same time.