虽然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 在这点上是很好的,同时也没有限制。

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.

The steps to build a MySQL database include: 1. Create a database and table, 2. Insert data, and 3. Conduct queries. First, use the CREATEDATABASE and CREATETABLE statements to create the database and table, then use the INSERTINTO statement to insert the data, and finally use the SELECT statement to query the data.

MySQL is suitable for beginners because it is easy to use and powerful. 1.MySQL is a relational database, and uses SQL for CRUD operations. 2. It is simple to install and requires the root user password to be configured. 3. Use INSERT, UPDATE, DELETE, and SELECT to perform data operations. 4. ORDERBY, WHERE and JOIN can be used for complex queries. 5. Debugging requires checking the syntax and use EXPLAIN to analyze the query. 6. Optimization suggestions include using indexes, choosing the right data type and good programming habits.


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

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

SublimeText3 English version
Recommended: Win version, supports code prompts!

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment