database writing efficiency

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to log 400 values every second into an access database. Previously I
did this in C++ using OLEDB and after a lot of experimentation, I minimized
the peak CPU usage by writing out the data in a separate thread every 10ms.
Now I have to do this in C# and I am uncertain as to the most efficient
method (I am primarily concerned with CPU usage).

My current methodology is below. One inefficiency I recognize but I’m not
sure how to fix is that I’m storing the data in memory as well as the
database but I only really need it in the database. Another specific question
I have is which database adaptor is fastest with access and which update
override is fastest (maybe rows is better than tables?)

Thanks,

Jeff

{
string strSQL;
OleDbCommandBuilder cmdBuilder;
DataSet dsIOChannelData;

m_dbConnection = new OleDbConnection(strConnection);

strSQL = "SELECT TimeStamp, ChannelID, Value FROM tblIOChannelData";
daIOChannelData = new OleDbDataAdapter(strSQL, m_dbConnection);
cmdBuilder = new OleDbCommandBuilder(daIOChannelData);
cmdBuilder.QuotePrefix = "[";
cmdBuilder.QuoteSuffix = "]";
cmdBuilder.SetAllValues = true;
dsIOChannelData = new DataSet();
m_dbConnection.Open();

daIOChannelData.Fill(dsIOChannelData, "tblIOChannelData");

dtIOChannelData = dsIOChannelData.Tables[0];
}

public void LogIO()
{
long lStartTicks;
long lElapsedTicks;
double dInverseTicksPerMS;
OleDbDataAdapter dbAdaptor;
DataTable dtIOChannelData;

loadIOLogDataTable(out dbAdaptor, out dtIOChannelData);

dInverseTicksPerMS = 1.0 / TimeSpan.TicksPerMillisecond;
m_bActiveLog = true;
while (m_bActiveLog) {
lStartTicks = DateTime.Now.Ticks;

for (int i = 0; i != 400; ++i)
dtIOChannelData.Rows.Add(DateTime.Now.ToOADate(), 77.5, 5);
dbAdaptor.Update(dtIOChannelData);

lElapsedTicks = DateTime.Now.Ticks - lStartTicks;
Thread.Sleep(Math.Max(0, 1000 - (int)(lElapsedTicks *
dInverseTicksPerMS + 0.5)));
}
m_dbConnection.Close();
}
 
Hi Jeff,

Why don't you simply use OleDbCommand and a parametrised sql insert
statement?
It is as fast as it gets.
 
Thanks very much Miha, Cor. One more question: Is there any gain in telling
the command the precision of each parameter?

Even executing outside of the debugger I can see the CPU usage spike to 10
and then go back to 0. I guess I have to spread out the transaction to
minimize this.

Jeff

Now my code looks like this

strSQLInsert = "INSERT INTO tblIOChannelData([TimeStamp],ChannelID,[Value])
VALUES (?,?,?)";
cmdInsert = new OleDbCommand(strSQLInsert, m_dbConnection);
cmdInsert.Parameters.Add("p1", OleDbType.Double, 8, "TimeStamp");
cmdInsert.Parameters.Add("p2", OleDbType.SmallInt, 2, "ChannelID");
cmdInsert.Parameters.Add("p3", OleDbType.Double, 8, "Value");

while (m_bActiveLog) {
lStartTicks = DateTime.Now.Ticks;

transaction = m_dbConnection.BeginTransaction();
cmdInsert.Transaction = transaction;
dDateTime = DateTime.Now.ToOADate();
for (int i = 0; i != 400; ++i) {
cmdInsert.Parameters[0].Value = dDateTime;
cmdInsert.Parameters[1].Value = 88;
cmdInsert.Parameters[2].Value = 17.77;
iRowsAdded = cmdInsert.ExecuteNonQuery();
}
transaction.Commit();

lElapsedTicks = DateTime.Now.Ticks - lStartTicks;
Thread.Sleep(Math.Max(0, 1000 - (int)(lElapsedTicks * dInverseTicksPerMS
+ 0.5)));
}



Cor Ligthert said:
Jeff,

In addition to Miha,

And use an oledb.oledbexecutenonquery() command

http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbcommand.executenonquery.aspx

What he probably forgot to write,

Cor

Jeff Boeker said:
I need to log 400 values every second into an access database. Previously
I
did this in C++ using OLEDB and after a lot of experimentation, I
minimized
the peak CPU usage by writing out the data in a separate thread every
10ms.
Now I have to do this in C# and I am uncertain as to the most efficient
method (I am primarily concerned with CPU usage).

My current methodology is below. One inefficiency I recognize but I'm not
sure how to fix is that I'm storing the data in memory as well as the
database but I only really need it in the database. Another specific
question
I have is which database adaptor is fastest with access and which update
override is fastest (maybe rows is better than tables?)

Thanks,

Jeff

{
string strSQL;
OleDbCommandBuilder cmdBuilder;
DataSet dsIOChannelData;

m_dbConnection = new OleDbConnection(strConnection);

strSQL = "SELECT TimeStamp, ChannelID, Value FROM tblIOChannelData";
daIOChannelData = new OleDbDataAdapter(strSQL, m_dbConnection);
cmdBuilder = new OleDbCommandBuilder(daIOChannelData);
cmdBuilder.QuotePrefix = "[";
cmdBuilder.QuoteSuffix = "]";
cmdBuilder.SetAllValues = true;
dsIOChannelData = new DataSet();
m_dbConnection.Open();

daIOChannelData.Fill(dsIOChannelData, "tblIOChannelData");

dtIOChannelData = dsIOChannelData.Tables[0];
}

public void LogIO()
{
long lStartTicks;
long lElapsedTicks;
double dInverseTicksPerMS;
OleDbDataAdapter dbAdaptor;
DataTable dtIOChannelData;

loadIOLogDataTable(out dbAdaptor, out dtIOChannelData);

dInverseTicksPerMS = 1.0 / TimeSpan.TicksPerMillisecond;
m_bActiveLog = true;
while (m_bActiveLog) {
lStartTicks = DateTime.Now.Ticks;

for (int i = 0; i != 400; ++i)
dtIOChannelData.Rows.Add(DateTime.Now.ToOADate(), 77.5, 5);
dbAdaptor.Update(dtIOChannelData);

lElapsedTicks = DateTime.Now.Ticks - lStartTicks;
Thread.Sleep(Math.Max(0, 1000 - (int)(lElapsedTicks *
dInverseTicksPerMS + 0.5)));
}
m_dbConnection.Close();
}
Click to expand...
Click to expand...
 
Jeff Boeker said:
Thanks very much Miha, Cor. One more question: Is there any gain in
telling
the command the precision of each parameter?
Click to expand...

I am not quite sure about that but I think that settings are for client side
verification/trimming only.
Even executing outside of the debugger I can see the CPU usage spike to 10
and then go back to 0. I guess I have to spread out the transaction to
minimize this.
Click to expand...

Or run in a thread with lower priority.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
Jeff

Now my code looks like this

strSQLInsert = "INSERT INTO
tblIOChannelData([TimeStamp],ChannelID,[Value])
VALUES (?,?,?)";
cmdInsert = new OleDbCommand(strSQLInsert, m_dbConnection);
cmdInsert.Parameters.Add("p1", OleDbType.Double, 8, "TimeStamp");
cmdInsert.Parameters.Add("p2", OleDbType.SmallInt, 2, "ChannelID");
cmdInsert.Parameters.Add("p3", OleDbType.Double, 8, "Value");

while (m_bActiveLog) {
lStartTicks = DateTime.Now.Ticks;

transaction = m_dbConnection.BeginTransaction();
cmdInsert.Transaction = transaction;
dDateTime = DateTime.Now.ToOADate();
for (int i = 0; i != 400; ++i) {
cmdInsert.Parameters[0].Value = dDateTime;
cmdInsert.Parameters[1].Value = 88;
cmdInsert.Parameters[2].Value = 17.77;
iRowsAdded = cmdInsert.ExecuteNonQuery();
}
transaction.Commit();

lElapsedTicks = DateTime.Now.Ticks - lStartTicks;
Thread.Sleep(Math.Max(0, 1000 - (int)(lElapsedTicks *
dInverseTicksPerMS
+ 0.5)));
}



Cor Ligthert said:
Jeff,

In addition to Miha,

And use an oledb.oledbexecutenonquery() command

http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbcommand.executenonquery.aspx

What he probably forgot to write,

Cor

Jeff Boeker said:
I need to log 400 values every second into an access database.
Previously
I
did this in C++ using OLEDB and after a lot of experimentation, I
minimized
the peak CPU usage by writing out the data in a separate thread every
10ms.
Now I have to do this in C# and I am uncertain as to the most efficient
method (I am primarily concerned with CPU usage).

My current methodology is below. One inefficiency I recognize but I'm
not
sure how to fix is that I'm storing the data in memory as well as the
database but I only really need it in the database. Another specific
question
I have is which database adaptor is fastest with access and which
update
override is fastest (maybe rows is better than tables?)

Thanks,

Jeff

{
string strSQL;
OleDbCommandBuilder cmdBuilder;
DataSet dsIOChannelData;

m_dbConnection = new OleDbConnection(strConnection);

strSQL = "SELECT TimeStamp, ChannelID, Value FROM tblIOChannelData";
daIOChannelData = new OleDbDataAdapter(strSQL, m_dbConnection);
cmdBuilder = new OleDbCommandBuilder(daIOChannelData);
cmdBuilder.QuotePrefix = "[";
cmdBuilder.QuoteSuffix = "]";
cmdBuilder.SetAllValues = true;
dsIOChannelData = new DataSet();
m_dbConnection.Open();

daIOChannelData.Fill(dsIOChannelData, "tblIOChannelData");

dtIOChannelData = dsIOChannelData.Tables[0];
}

public void LogIO()
{
long lStartTicks;
long lElapsedTicks;
double dInverseTicksPerMS;
OleDbDataAdapter dbAdaptor;
DataTable dtIOChannelData;

loadIOLogDataTable(out dbAdaptor, out dtIOChannelData);

dInverseTicksPerMS = 1.0 / TimeSpan.TicksPerMillisecond;
m_bActiveLog = true;
while (m_bActiveLog) {
lStartTicks = DateTime.Now.Ticks;

for (int i = 0; i != 400; ++i)
dtIOChannelData.Rows.Add(DateTime.Now.ToOADate(), 77.5, 5);
dbAdaptor.Update(dtIOChannelData);

lElapsedTicks = DateTime.Now.Ticks - lStartTicks;
Thread.Sleep(Math.Max(0, 1000 - (int)(lElapsedTicks *
dInverseTicksPerMS + 0.5)));
}
m_dbConnection.Close();
}
Click to expand...
Click to expand...
Click to expand...
 
Back
Top