Access和Firebird的性能比较
虽然Firebird总体上是比Access好，但是没有传说的那么好，至少在Net环境下。 1、测试环境 A、系统环境 操作系统：Windows XP Professional Server Pack 2 CPU：Intel(R) Pentium(R) 4 CPU 3.00GHZ 2.99GHZ 内存：1G B、代码环境 NET2.0、Access2003、Firebir
虽然Firebird总体上是比Access好，但是没有传说的那么好，至少在Net环境下。
1、测试环境
A、系统环境
操作系统：Windows XP Professional Server Pack 2
CPU：Intel(R) Pentium(R) 4 CPU 3.00GHZ 2.99GHZ
内存：1G
B、代码环境
NET2.0、Access2003、Firebird2.1、
Firebird的NET2.0访问API(FirebirdSql.Data.FirebirdClient.dll)
2、数据库
EmployeeInfo表：
CREATE TABLE EMPLOYEEINFO(<br>EID Integer NOT NULL,<br>ENAME Varchar(50),<br>ETELEPHONE Varchar(50),<br>EMOBILE Varchar(50),<br>EADDRESS Varchar(200),<br>EWORK Varchar(50),<br>ECOMPANY Varchar(50),<br>EAGE Integer,<br>ESCHOOL Varchar(50),<br>EBIRTHDAY Date,<br>EFAVOURATE Varchar(500),<br>ISMVP Integer,<br>ELEVEL Integer,<br>ENABLE_FLAG Integer,<br>CONSTRAINT EMPLOYEEINFO_NAME PRIMARY KEY (EID)<br>);
3、测试代码
见附带文件
4、性能比较
下面的数字是以毫秒为单位的，对于两个数据库连接的性能在4.1中有记录，因为其他的操作连接的性能基本相同，所以在其他的比较中省略了。
新增操作：需要从数据表中获取ID，所以都需要执行ExecuteScalar
Access：SELECT max(EId) + 1 FROM EmployeeInfo
Firebird：SELECT first 1 GEN_ID( EMPLOYEEINFO_KEY_GEN, 1) FROM RDB$GENERATORS
预编优化：这种方式是采用IDbCommand的Prepare方法来进行的。
4.1、单条数据的操作比较
1、新增操作
| 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、 修改操作
| Access | FireBird | |
| IDbCommand.ExecuteNonQuery() | 125.531627 | 88.544622 |
| 105.508891 | 88.177334 | |
| 145.817176 | 107.016208 |
1、新增操作
| IDbCommand.ExecuteScalar() | IDbCommand.ExecuteNonQuery() | |
| Access | 275.494317 | 234.020361 |
| 261.396954 | 237.707107 | |
| 252.611140 | 253.758009 | |
| 预编译优化 | 124.001096 | 100.539268 |
| 124.581257 | 98.269848 | |
| 125.422189 | 99.034516 | |
| 预编译+事务控制 | 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 | |
| 预编译优化 | 308.331690 | 437.868342 |
| 283.292181 | 551.306577 | |
| 222.096816 | 455.877916 | |
| 预编译+事务控制 | 70.281354 | 109.981409 |
| 72.199458 | 96.185741 | |
| 69.851572 | 91.551454 |
2、 修改操作
| Access | FireBird | |
| IDbCommand.ExecuteNonQuery() | 411.009308 | 913.508742 |
| 396.797053 | 868.117194 | |
| 399.259210 | 912.881623 | |
| 预编译优化 | 177.652866 | 692.759320 |
| 163.982479 | 709.243510 | |
| 171.324164 | 644.216015 | |
| 预编译+事务控制 | 158.654429 | 106.195976 |
| 154.795059 | 101.715139 | |
| 157.486357 | 104.424021 |
1、新增操作
| IDbCommand.ExecuteScalar() | IDbCommand.ExecuteNonQuery() | |
| Access | 1651.840012 | 2133.541653 |
| 1663.862358 | 2144.262530 | |
| 1631.403159 | 2135.223692 | |
| 预编译优化 | 796.962979 | 808.875114 |
| 785.243696 | 793.758126 | |
| 809.209726 | 797.399235 | |
| 预编译+事务控制 | 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 | |
| 预编译优化 | 3716.587264 | 5723.248900 |
| 3234.737922 | 5430.311542 | |
| 2686.714810 | 4821.239747 | |
| 预编译+事务控制 | 522.050014 | 642.658276 |
| 522.211388 | 665.879242 | |
| 532.323116 | 658.373523 |
2、 修改操作
| Access | FireBird | |
| IDbCommand.ExecuteNonQuery() | 3290.740559 | 7873.507740 |
| 3991.333695 | 7822.996734 | |
| 3293.068174 | 7116.759956 | |
| 预编译优化 | 1398.160890 | 6482.893171 |
| 1254.979979 | 6302.055985 | |
| 1245.802121 | 6272.648019 | |
| 预编译+事务控制 | 1097.316477 | 648.313099 |
| 1221.636742 | 648.390276 | |
| 1104.532568 | 648.983446 |
1、新增操作
| IDbCommand.ExecuteScalar() | IDbCommand.ExecuteNonQuery() | |
| Access | 15321.344697 | 20695.870283 |
| 15522.056899 | 20775.041631 | |
| 15319.349251 | 20727.514825 | |
| 预编译优化 | 10627.689828 | 9980.130051 |
| 11161.361432 | 10432.259290 | |
| 10580.619317 | 9925.817398 | |
| 预编译+事务控制 | 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 | |
| 预编译优化 | 61204.197587 | 94345.156610 |
| 36930.112494 | 57278.146122 | |
| 40012.081468 | 66210.081814 | |
| 预编译+事务控制 | 5407.627206 | 6910.738469 |
| 5488.005238 | 7106.846560 | |
| 5524.538831 | 6740.408060 |
2、 修改操作
| Access | FireBird | |
| IDbCommand.ExecuteNonQuery() | 39694.855804 | 99310.751707 |
| 35354.716525 | 90011.911178 | |
| 36534.236655 | 91112.061482 | |
| 预编译+事务控制 | 10469.019093 | 7230.535415 |
| 10444.395741 | 7682.581104 | |
| 10329.116616 | 7390.059610 |
1、新增操作
|
IDbCommand.ExecuteScalar() |
IDbCommand.ExecuteNonQuery() | ||
| Access |
198287.389450 |
223781.708768 | |
|
207229.904897 |
227152.302183 | ||
|
236267.203150 |
251924.067059 | ||
| 预编译优化 |
75745.455466 |
80136.166440 | |
|
80215.392531 |
84041.511179 | ||
|
83531.057454 |
85371.502942 | ||
| 预编译+事务控制 |
73753.320106 |
62696.035496 | |
|
70442.642879 |
69222.947557 | ||
|
79447.569370 |
70056.168140 | ||
| FireBird |
>30分钟 |
||
| 预编译优化 | 297619.975597 |
551716.871984 |
|
| 预编译+事务控制 | 50412.421478 |
62230.369322 |
|
| 52912.052985 |
69931.034354 |
||
| 52509.019944 |
66763.649792 |
||
2、 修改操作
| Access | FireBird | |
| IDbCommand.ExecuteNonQuery() | 332451.315712 |
1260805.499906 |
| 347068.025903 | ||
| 预编译优化 | 164528.339360 | 643502.447928 |
| 预编译+事务控制 | 108129.478762 | 81140.664313 |
1、新增操作
| IDbCommand.ExecuteScalar() |
IDbCommand.ExecuteNonQuery() |
||
| 预编译优化Access | 479207.809593 |
465971.617839 |
|
| 377229.922041 |
367370.094465 |
||
| 预编译+事务控制 | 336857.065763 |
316500.809166 |
|
| 预编译优化FireBird |
>60分钟 |
||
| 预编译+事务控制 |
273555.344525 |
361675.703063 | |
2、 修改操作
| Access | FireBird | |
| 预编译+事务控制 | 512516.135296 | 473002.155994 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
Access文件大小的增长是非常恐怖的，1000000条左右的数据基本上可以达到Access的极限(2G)
Firebird文件大小的增长和Access比较起来，比Access要小很多，基本上是差了几个级别
下面是分别进行大数量操作后的文件情况：
Firebird 94808KB
Access 1123424KB
在不压缩数据库的前提下，Access增加100W左右的数据达到2G，Firebrid增加1000W左右的数据达到2G。
5、测试总结
根据上面的性能比较，可以得出以上几点结论：
- 对于大批量的数据操作，一定要采用预编译或批量提交的方式进行操作，如果是在Firebird中，一定加事务进行处理，因为在Firebird中，有事务的性能可以提升6-10倍左右。在Access中，虽然性能提升不多，但是还是最好都加上事务控制。这一方面增加操作的原子性，并且也减少数据库的读写次数。
- Access一般支持2G左右的数据，当数据量超过这个限制后，Access不能写入数据。所以当数据量在2G下的时候才选用。Firebird对于数据的支持大于/等于16G，而且在优化后的整体性能要强于Access。
- Access在没有压缩的前提下，如果大批量的进行数据操作（新增/修改），那么数据大小的增长是是Firebird的几倍，一般连续增长100W多的数据就不能再插入数据了。而Firebird 在这点上是很好的，同时也没有限制。
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
How to use sql if statement
Apr 09, 2025 pm 06:12 PM
SQL IF statements are used to conditionally execute SQL statements, with the syntax as: IF (condition) THEN {statement} ELSE {statement} END IF;. The condition can be any valid SQL expression, and if the condition is true, execute the THEN clause; if the condition is false, execute the ELSE clause. IF statements can be nested, allowing for more complex conditional checks.
Unable to log in to mysql as root
Apr 08, 2025 pm 04:54 PM
The main reasons why you cannot log in to MySQL as root are permission problems, configuration file errors, password inconsistent, socket file problems, or firewall interception. The solution includes: check whether the bind-address parameter in the configuration file is configured correctly. Check whether the root user permissions have been modified or deleted and reset. Verify that the password is accurate, including case and special characters. Check socket file permission settings and paths. Check that the firewall blocks connections to the MySQL server.
How to configure zend for apache
Apr 13, 2025 pm 12:57 PM
How to configure Zend in Apache? The steps to configure Zend Framework in an Apache Web Server are as follows: Install Zend Framework and extract it into the Web Server directory. Create a .htaccess file. Create the Zend application directory and add the index.php file. Configure the Zend application (application.ini). Restart the Apache Web server.
What are the benefits of multithreading in c#?
Apr 03, 2025 pm 02:51 PM
The advantage of multithreading is that it can improve performance and resource utilization, especially for processing large amounts of data or performing time-consuming operations. It allows multiple tasks to be performed simultaneously, improving efficiency. However, too many threads can lead to performance degradation, so you need to carefully select the number of threads based on the number of CPU cores and task characteristics. In addition, multi-threaded programming involves challenges such as deadlock and race conditions, which need to be solved using synchronization mechanisms, and requires solid knowledge of concurrent programming, weighing the pros and cons and using them with caution.
How to solve the 'Network Error' caused by Vue Axios across domains
Apr 07, 2025 pm 10:27 PM
Methods to solve the cross-domain problem of Vue Axios include: Configuring the CORS header on the server side using the Axios proxy using JSONP using WebSocket using the CORS plug-in
Summary of phpmyadmin vulnerabilities
Apr 10, 2025 pm 10:24 PM
The key to PHPMyAdmin security defense strategy is: 1. Use the latest version of PHPMyAdmin and regularly update PHP and MySQL; 2. Strictly control access rights, use .htaccess or web server access control; 3. Enable strong password and two-factor authentication; 4. Back up the database regularly; 5. Carefully check the configuration files to avoid exposing sensitive information; 6. Use Web Application Firewall (WAF); 7. Carry out security audits. These measures can effectively reduce the security risks caused by PHPMyAdmin due to improper configuration, over-old version or environmental security risks, and ensure the security of the database.
How to monitor Nginx SSL performance on Debian
Apr 12, 2025 pm 10:18 PM
This article describes how to effectively monitor the SSL performance of Nginx servers on Debian systems. We will use NginxExporter to export Nginx status data to Prometheus and then visually display it through Grafana. Step 1: Configuring Nginx First, we need to enable the stub_status module in the Nginx configuration file to obtain the status information of Nginx. Add the following snippet in your Nginx configuration file (usually located in /etc/nginx/nginx.conf or its include file): location/nginx_status{stub_status
What is apache server? What is apache server for?
Apr 13, 2025 am 11:57 AM
Apache server is a powerful web server software that acts as a bridge between browsers and website servers. 1. It handles HTTP requests and returns web page content based on requests; 2. Modular design allows extended functions, such as support for SSL encryption and dynamic web pages; 3. Configuration files (such as virtual host configurations) need to be carefully set to avoid security vulnerabilities, and optimize performance parameters, such as thread count and timeout time, in order to build high-performance and secure web applications.
