A comparative analysis of data insertion performance of several databases _ database other

There are not many local database contacts, the first access, but now sqlite more powerful--and, frankly, I do not like access, even if I do not like SQL Server, as long as the sight of the @ # go number I am dizzy , not to mention having a problem that I feel very deadly: paging too troublesome! Far less than the limit in mysql/sqlite or the rownum in Oracle.

At ordinary times, the basic use of Oracle, its performance is well over; MySQL has been mixed in recent years, there must be something extraordinary, also included in the test scope.

In addition, Access now has 2007 version, do not know and 2003 version in the performance of any difference? Deliberately separate the tests.

The test environment is as follows:
Server: Native (notebook, I3 370,8g memory)
Operating system: Windows 7 x64
Locale: C #, using a console program test.
Virtual machine: Installs SQLSERVER2008,2CPU,3G memory.
Database: oracle11g (native database)
SQLite (native file)
MYSQL (version 5 green edition)
SQL Server (do not want to install version 2008 on this machine, originally want to use the learning version of the machine, but anyway, had to install a 2008 version on the local virtual machine, test performance has declined, but also have to do so)
Access (Version 2003)
Access (Version 2007)
Test method: Sir into 10,000 records (6 fields per record), before inserting data to clear the original data;
Data inserts are divided into transactional writes (starting a transaction, submitting it after an insert), and non-transactional writing (not starting a transaction, inserting each).
SQL Server database in the local virtual machine database, the data is not accurate, to eliminate the impact of network operations, move the code to the virtual directly to execute once.

Several interesting questions were identified during the test:
Under 1.64-bit operating systems, Access cannot execute under a program compiled to ANYCPU, and must be compiled to x86 to operate normally. (If this is a Web site, the pool must be set to enable 32-bit compatibility)
2.SQLite has a 64-bit version of the DLL, you can run normally under a 64-bit environment (console program), but oddly enough, if you are a Web site, using a 64-bit DLL will not work, you must use a 32-bit version of the DLL, and the IIS pool is set to enable 32-bit compatibility to function correctly.
The 2003 version of 3.Access is not the same as the 2007 version of the database connection string, as follows:
2003:@ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\xxx\test.mdb";
2007:@ "Provider=microsoft.ace.oledb.12.0;data source=d:\xxx\test.accdb";
The test results are as follows:
146 241 local database, 2cpu,2g memory
code runs directly on the virtual machine TD class= "XL65" align= "right" > TD class= "XL65" align= "right" > TD class= "xl69" width= "179" >

Database categories	Insert Quantity	Local transaction (MS)		No transactions locally (ms)		Description
		Each record is time-consuming	Number of bars inserted per second	Each record is time-consuming	Number of bars inserted per second
oracle	10000	0.23		0.9	1103	local database
sqlite	10000	0.0998		6.86	local database
mysql	10000	0.2574		4.132	local database
sql Server	10000	0.42	2380		654	remote database
sql Server	10000	0.413	2418		697
access2003	10000	0.6	1664		21.33	local database
Access2007	10000	0.73	1369	47.57	21.02	Local Database

The results were interesting:
1.SQLite transactions are inserted at the fastest rate, reaching 10,000 per second, but the rate of non-transactional inserts is general.
2.Oracle as the eldest of the database, transaction insertion speed is second only to SQLite, but the rate of non transactional inserts is the highest.
3. Poor access, do not try not to know, a try to surprise, transaction insert slowest, non-transaction insert slower; originally to Access2007 still a little look forward to, did not think it is worse than the 2003 version of the data.
4.MYSQL is pretty good. The performance of transaction inserts is second only to Oracle, and non-transactional inserts are not that strong, but they are not bad.
From the data point of view, found a more regular phenomenon: large databases and small databases, local database differences, not the level of transactional insert performance, but the level of non-transactional insert performance. Oracle is the strongest, reaching more than 1000 per second, followed by SQL Server, and 700 per second; MySQL is much worse, only 240 per second, this performance is a whole poor grade; the SQLite Non-transactional insert performance is almost as close to MySQL as it is, but it's not a significant difference (not an order of magnitude); Worst of all, access, which is bad for non transactional data inserts, is a whole order of magnitude worse than SQLite.

Visible from above, if the local database is selected, SQLite should be preferred. And in the application should also be noted that as much as possible to centralize data for transactional data writing, so can greatly improve the performance of the database.