Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the differences in the performance of data insertion in several databases". In the daily operation, I believe that many people have doubts about the differences in the performance of data insertion in several databases. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "what are the differences in the performance of data insertion in several databases?" Next, please follow the editor to study!

The test environment is as follows:

Server: local (notebook, i3 370, 8G memory)

Operating system: windows 7 x64

Locale: caching, using the console program to test.

Virtual machine: install SqlServer2008,2CPU,3G memory.

Database: Oracle11g (native database)

SQLite (native files)

MySql (green version of version 5)

SqlServer (I don't want to install version 2008 on this machine, I originally wanted to use the learning version of this machine, but I couldn't live or die, so I had to install a version 2008 on the local virtual machine, which degraded the test performance, but I had no choice but to do so)

Access (version 2003)

Access (version 2007)

Test method: Mr. into 10000 records (6 fields for each record), erase the original data before inserting the data

Data insertion is divided into transactional writes (starting transactions and committing after inserting one by one) and non-transactional writes (inserting one by one without starting transactions).

The SqlServer database is not accurate in the database of the local virtual machine; 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:

1. Under 64-bit operating system, Access cannot be executed under programs compiled to AnyCpu and must be compiled to x86 in order to operate normally. (if it is a website, the pool must be set to enable 32-bit compatibility)

2.SQLite has a 64-bit version of DLL, which can run normally in a 64-bit environment (console program), but it is strange that if it is a website, using 64-bit DLL does not work properly, you must use the 32-bit version of DLL, and the pool of IIS is set to enable 32-bit compatibility to work properly.

The database connection string of version 2003 of 3.Access is different from that of version 2007, as follows:

2003 Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D @ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\ xxx\ test.mdb"

2007 Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D @ "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\ xxx\ test.accdb"

The test results are as follows:

Number of database category inserts local transactions (milliseconds) local no transactions (milliseconds) indicates that each record takes time to insert per second Oracle100000.2343000.91103 local database Sqlite100000.0998100166.86146 local database MySql100000.257438844.132241 local database Sql Server100000.4223801.52654 remote database Sql Server100000.41324181.433697 local database, 2CPU memory 2G memory

The code runs the Access2003100000.6166446.8721.33 local database Access2007100000.73136947.5721.02 local database directly on the virtual machine

The results are interesting:

The transaction insertion speed of 1.SQLite is the fastest, reaching 10000 transactions per second, but the non-transactional insertion speed is average.

As the leader of the database, the transaction insertion speed of 2.Oracle is second only to SQLite, but the non-transactional insertion speed ranks first.

3. Poor Access, do not try do not know, a try startled, transaction insertion is the slowest, non-transaction insertion is slower; I had expected Access2007, but it is worse than the 2003 version of the data.

4.mySql is really not bad, transaction insertion performance is second only to Oracle, non-transactional insertion is not so strong, but not bad.

From the perspective of data, we find a more regular phenomenon: the difference between large database and small database and local database does not lie in the performance of transactional insertion, but in the performance of non-transactional insertion. Oracle is the strongest, reaching more than 1000 entries per second, followed by SqlServer, which also has 700messages per second. MySql is much worse by comparison, reaching only about 240messages per second, which is a full grade of poor performance. what is more unexpected is that SQLite, although the performance of non-transactional insertion is a little worse than that of mySql, there is little difference (not an order of magnitude difference) Worst of all is Access, where the performance of non-transactional data insertion is appalling, a full order of magnitude worse than SQLite.

As you can see from the above, if you choose a local database, SQLite should be the first choice. And in the application, we should also pay attention to centralizing the data as much as possible for transactional data writing, which can greatly improve the performance of the database.

At this point, the study on "what are the differences in the performance of data insertion in several databases" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.