OLEDB performance in ADO.NET

S

Sek

Hi Folks,

I am using OLEDB to log real-time data to a local mdb file from my C#
app.

I will be generating a new .mdb file every 1 hour on the local machine
and continue to log the data into the new file. So, the connection
pooling is of little help to me.

I use QueryPerformanceCounter to calculate the time taken by method
invocations.I want to reduce the time taken by the calls to DB.
Following is the code to create DB, Table, Open and Close connection.

*******************************************************
// Create DB
myTimer.Start();
string dbStr = connecStr + folderPath + "\\" + fileName;

ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create(dbStr);
cat = null;

myTimer.Stop();
log(myTimer.Duration(1));

odb = new OleDbConnection(dbStr);

// Construct CreateTable command
StringBuilder crtTableCmd = new StringBuilder();
......

myTimer.Start();
odb.Open();

// Create Table
OleDbCommand odc = new OleDbCommand(crtTableCmd.ToString(),odb);
odc.ExecuteNonQuery();
myTimer.Stop();

log(myTimer.Duration(1));

dap = new OleDbDataAdapter();
dap.SelectCommand = new OleDbCommand("SELECT * FROM Readings",odb);

ds.Reset();

myTimer.Start();
dap.Fill(ds,"Readings");
myTimer.Stop();
log(myTimer.Duration(1));

myTimer.Start();
odb.Close();
myTimer.Stop();
log(myTimer.Duration(1));
*****************************************************

Following are my questions:
1) The time taken by ADOX.CatalogClass.Create method is varying
rampantly from 25ms to 200ms. Why is this so?
2) OleDbConnection.Open takes around 5ms and OleDbConnection.Close
takes around 10ms. Why does close take more time than open? Is there
any way to reduce the time consumption further.
3) OleDbDataAdapter.Update takes around 20ms. Is there any way to
reduce it further?

TIA.
Sek
 
M

Mark Rae

1) The time taken by ADOX.CatalogClass.Create method is varying
rampantly from 25ms to 200ms. Why is this so?
Click to expand...

Probably just other disk activity. Have you considered creating a template
MDB file and just making a copy of it using System.IO every time you need a
new one, rather than InterOp with ADOX?
 
S

Sek

Mark said:
Probably just other disk activity. Have you considered creating a template
MDB file and just making a copy of it using System.IO every time you need a
new one, rather than InterOp with ADOX?
Click to expand...
Hi Mark,

I will try it out and let u know.

You have any suggestions for me for the other two questions.

Will i get any performance improvements by using ODBC instead of OLEDB?

THanks a lot.
Sek
 
C

Cor Ligthert [MVP]

Sek,

You tell that the creating of your database takes about half a second. That
is not visible for humans.

You use a dataadapter to add login records while more normal would be in my
opinion to use an executenonquery with a SQL string to Insert those records.

I hope this helps,

Cor
 
M

Mark Ashton

Try adding 'ole db services=0' to the connection string, if it works it will
turn off pooling and autoenlistment in distributed transactions.
You might also test to see of creating a new DataSet vs. calling Reset which
is faster.
 
Top