OLE DB connectivity and efficiency (MS SQL, Oracle, ACESS)

This half-year project in hand, I write code when the preference of MS SQL, deployment Server installed is Oralce and the actual site is a minicomputer, there is no installed database had to choose ACESS, three kinds of environment inconsistent, only decided to use OLE DB to do database access. Before busy to catch up on the progress of a few additions and deletions to change the operation, years ago idle down to do the test also found a lot of problems. Take advantage of these days to wrap up from scratch, to make a single simple class out.

In order to invoke the convenience of passing directly from the IDbCommand inheritance, so that the transfer of parameters or calls left room, encapsulation is simple directly on the class diagram

The gadget and encapsulation classes in this article have been submitted to git

Toater_osc in the Databaseacess project in Git.

This version of the package provides two simple options

1. Whether to stay connected

The option to close the connection after the command is executed, as it is found that if you open the close file frequently when using ACESS, you will encounter an IO-caused error, the following is a set of tests to show that ACESS is performing more efficiently than the two mainstream databases while maintaining a connection.

2. Database type selection

 <summary>/// /// <summary>/// /// </summary>/// < constructor function Param name= "connectionString" > Connection string </param>/// <param name= "DbType" > Database type </ Param>/// <param name= "KeepAlive" > whether to keep the connection </param>public dbfairy (string  connectionstring, string dbtype = dbtype_oracle, bool keepalive =  FALSE) { //  non-default check keyword match  if  (!string. IsNullOrEmpty (DbType))  {  if  (dbtype_oracle != dbtype &&  Dbtype_mssql != dbtype && dbtype_access != dbtype)   {    throw new notsupportedexception ("Unsupported type description, please use  DBFairy.DBType  definition.");   }  _DBType = dbType; } _ConnectionString =  connectionstring; _alwaysclose = !keepalive; dbcommand.connection = new  OledBconnection (_connectionstring);} 

The three databases in the support options were tested, and the following is the test process.

Environment: Acer Desktop, I3 CPU, 4G memory

The method is: three background threads, while performing database operations

But I was very careful to put ACCESS to the first place in the call, it is because everyone said that the lad is too slow, only love to put in the first place.

Bgacessworker.runworkerasync (); Bgmsworker.runworkerasync (); Bgoracleworker.runworkerasync ();

Three Tests in case of closing the connection:

First time:

Second time:

Third time:

In the case of closing the connection: MS SQL is similar to Oracle, but it feels like Oracle is slightly faster, with ACESS operating time of more than twice times

Question: Does the hair ACCESS execute more and more quickly? Is this a problem with the file IO cache, or is the process scheduling priority for the file?

Three groups of tests in a stay-connected state:

First: MSSQL and Oracle are almost as good as before, but as you can see, the time has been reduced from 17000 to 15000, but the loss of the switch connection is not very large in service-type data.

Look again ACESS obviously already inverse day, direct second kill two bit eldest brother. It only took 10075, 10 seconds or so to insert 1000 records.

Second time:

Third time:

May see colleagues have left the station, home for the new year, the computer is also excited more and more cards, 1000 lines of execution time is getting longer, but the contrast is still very obvious.

ACESS in a connected state, a small amount of data is plugged in fast enough to drive faster than MS SQL and ORACLE. After the two have been very tacit understanding, not abandon to maintain the pace of the not divided.

(guess that's the reason for Valentine's Day today ^_^)

-----------------------------------------------------------MARK------------------------------------------------ ----------------

 ------ MS SQL  Additional database used here  ,VS 2013  comes with   virtual  sql functionality <add name= "OLEDBConnectionString"  connectionstring= "provider=sqlncli11.1;data source= (LocalDB) \V11.0; Persist security info=false;integrated security=sspi;initial file name=e:\oschina\ Source\repos\thenu.tinyhourselib\thenu.tinyhourselib\databaseacess\windowsformsacess\app_data\studentdb.mdf "  providername= "System.Data.OleDb"  /> --- oracl <add name= " Oracleconnectionstring " connectionstring=" provider=msdaora.1;data source=orcl; persist security info=true; password=hhkj; User id=hhkj " providername=" System.Data.OleDb " /> --- acess<add name=" Accessconnectionstring " connectionstring=" provider=microsoft.jet.oledb.4.0;data source=e:\oschina\ Source\repos\thenu.tinyhourselib\thenu.tinyhourselib\databaseacess\windowsformsacess\app_data\student.mdb "  providername= "SYstem. Data.oledb " />

With regard to the connection string, as far as your own version, choose Good Provider, SQL 2005,2008 and 2012 use different, and for service data and attached database is also a little difference. Oracle's common use is the above approach, and of course you can choose to oralce your own OLE DB Provider .

We recommend a very full connection string reference site:

Www.connectionstrings.com

Problem summary part is too late to write, do not scold me is a liar, anxious to go home to wash socks at noon This is a pair of tomorrow morning to wear, this part of the year after the record it

Today is Valentine's Day plus go home big day, as senior cock Silk also has a lot of things to do

@MISS Bingbing

You oscer Happy New Year, the goat is auspicious, radiant, beautiful sheep and sheep, lazy, I am a gray wolf ....

OLE DB connectivity and efficiency (MS SQL, Oracle, ACESS)