I don’t have much contact with local databases. I used Access first, but now SQLite has more powerful functions - and to be honest, I don’t like Access, and I don’t like SqlServer. As long as I see the @大发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发发� It's far less than the limit in mySql/SQLite or rownum in Oracle.
I usually use Oracle and have a good understanding of its performance; mySql has been doing well in recent years, and I believe it has outstanding features and has been included in the test scope.
In addition, Access now has version 2007, I wonder if there is any difference in performance between version 2003? Tested separately.
The test environment is as follows:
Server: Native machine (notebook, I3 370, 8G memory)
Operating system: windows 7 x64
Locale: C#, test using console program.
Virtual machine: Install SqlServer2008, 2CPU, 3G memory.
Database: Oracle11g (native database)
SQLite (native file)
MySql (green version of version 5)
SqlServer (I don't want to install the 2008 version on this machine. I originally wanted to use the learning version of this machine, but I couldn't do it anymore. I had to install a 2008 version on this machine's virtual machine. The test performance has declined, but I had to do so)
Access (2003 edition)
Access (2007 edition)
Test method: Create 10,000 records (6 fields per record), and clear the original data before inserting the data;
Data insertion is divided into transactional writes (start transactions, insert them one by one and commits them) and non-transactional writes (not start transactions, insert them one by one).
The SqlServer database is in the database of the local virtual machine, and the data is not very accurate; in order to eliminate the impact of network operations, the code is moved to the virtual machine and executed directly once.
Several interesting questions were found during the test:
Under a 1.64-bit operating system, Access cannot be executed under a program compiled into AnyCpu, and must be compiled into x86 before it can operate normally. (If it is a website, the pool must be set to enable 32-bit compatibility)
There is a 64-bit version of DLL that can run normally in a 64-bit environment (console program); but it is strange that if it is a website, it will not work properly using a 64-bit DLL. You must use a 32-bit version of DLL and set the pool of IIS to enable 32-bit compatibility to run normally.
The database connection string of the 2003 version and the 2007 version are different, as follows:
2003：@"Provider=.4.0;Data Source=D:\xxx\";
2007:@"Provider=.12.0;Data Source=D:\xxx\";
The test results are as follows:
The results are interesting:
The transaction insertion speed is the fastest, reaching 10,000 pieces per second, but the non-transactional insertion speed is average.
As the leader of the database, transaction insertion speed is second only to SQLite, but non-transactional insertion speed ranks first.
3. Poor Access, I don’t know if I don’t try it. I was startled by it. Transaction insertion was the slowest, and non-transaction insertion was slower. I was looking forward to Access2007, but I didn’t expect it to be worse than the data in the 2003 version.
It's really good. The performance of transaction insertion is second only to Oracle. Non-transactional insertion is not that strong, but it's not bad.
From the data perspective, I found a more regular phenomenon: the difference between large databases and small databases and local databases is not the level of transactional insertion performance, but the level of non-transactional insertion performance. Oracle is the strongest, with more than 1,000 pieces per second; second is SqlServer, which also has 700 pieces per second; mySql is much worse than that of only 240 pieces per second, which is a completely different performance. What is more surprising is SQLite, although its non-transactional insertion performance is a little worse than mySql, it is not a big difference (not an order of magnitude); the worst is Access, which has a terrible performance of non-transactional data insertion, which is an entirely one order of magnitude worse than SQLite.
As can be seen from the above, if you choose a local database, SQLite should be the first choice. In addition, in applications, you should also pay attention to the data being concentrated as much as possible for transactional data writing, which can greatly improve the performance of the database.
I usually use Oracle and have a good understanding of its performance; mySql has been doing well in recent years, and I believe it has outstanding features and has been included in the test scope.
In addition, Access now has version 2007, I wonder if there is any difference in performance between version 2003? Tested separately.
The test environment is as follows:
Server: Native machine (notebook, I3 370, 8G memory)
Operating system: windows 7 x64
Locale: C#, test using console program.
Virtual machine: Install SqlServer2008, 2CPU, 3G memory.
Database: Oracle11g (native database)
SQLite (native file)
MySql (green version of version 5)
SqlServer (I don't want to install the 2008 version on this machine. I originally wanted to use the learning version of this machine, but I couldn't do it anymore. I had to install a 2008 version on this machine's virtual machine. The test performance has declined, but I had to do so)
Access (2003 edition)
Access (2007 edition)
Test method: Create 10,000 records (6 fields per record), and clear the original data before inserting the data;
Data insertion is divided into transactional writes (start transactions, insert them one by one and commits them) and non-transactional writes (not start transactions, insert them one by one).
The SqlServer database is in the database of the local virtual machine, and the data is not very accurate; in order to eliminate the impact of network operations, the code is moved to the virtual machine and executed directly once.
Several interesting questions were found during the test:
Under a 1.64-bit operating system, Access cannot be executed under a program compiled into AnyCpu, and must be compiled into x86 before it can operate normally. (If it is a website, the pool must be set to enable 32-bit compatibility)
There is a 64-bit version of DLL that can run normally in a 64-bit environment (console program); but it is strange that if it is a website, it will not work properly using a 64-bit DLL. You must use a 32-bit version of DLL and set the pool of IIS to enable 32-bit compatibility to run normally.
The database connection string of the 2003 version and the 2007 version are different, as follows:
2003：@"Provider=.4.0;Data Source=D:\xxx\";
2007:@"Provider=.12.0;Data Source=D:\xxx\";
The test results are as follows:
| Database Category | Insert quantity | Local transactions (milliseconds) | No transactions locally (milliseconds) | illustrate | ||
| Time-consuming for each record | Number of inserts per second | Time-consuming for each record | Number of inserts per second | |||
| Oracle | 10000 | 0.23 | 4300 | 0.9 | 1103 | Local database |
| Sqlite | 10000 | 0.0998 | 10016 | 6.86 | 146 | Local database |
| MySql | 10000 | 0.2574 | 3884 | 4.132 | 241 | Local database |
| Sql Server | 10000 | 0.42 | 2380 | 1.52 | 654 | Remote database |
| Sql Server | 10000 | 0.413 | 2418 | 1.433 | 697 | Local database, 2CPU, 2G memory The code runs directly on the virtual machine |
| Access2003 | 10000 | 0.6 | 1664 | 46.87 | 21.33 | Local database |
| Access2007 | 10000 | 0.73 | 1369 | 47.57 | 21.02 | Local database |
The transaction insertion speed is the fastest, reaching 10,000 pieces per second, but the non-transactional insertion speed is average.
As the leader of the database, transaction insertion speed is second only to SQLite, but non-transactional insertion speed ranks first.
3. Poor Access, I don’t know if I don’t try it. I was startled by it. Transaction insertion was the slowest, and non-transaction insertion was slower. I was looking forward to Access2007, but I didn’t expect it to be worse than the data in the 2003 version.
It's really good. The performance of transaction insertion is second only to Oracle. Non-transactional insertion is not that strong, but it's not bad.
From the data perspective, I found a more regular phenomenon: the difference between large databases and small databases and local databases is not the level of transactional insertion performance, but the level of non-transactional insertion performance. Oracle is the strongest, with more than 1,000 pieces per second; second is SqlServer, which also has 700 pieces per second; mySql is much worse than that of only 240 pieces per second, which is a completely different performance. What is more surprising is SQLite, although its non-transactional insertion performance is a little worse than mySql, it is not a big difference (not an order of magnitude); the worst is Access, which has a terrible performance of non-transactional data insertion, which is an entirely one order of magnitude worse than SQLite.
As can be seen from the above, if you choose a local database, SQLite should be the first choice. In addition, in applications, you should also pay attention to the data being concentrated as much as possible for transactional data writing, which can greatly improve the performance of the database.