SqlCe Insert Record Performance

[Guest]

I use slqce database to store master-detail table.It took too long to insert
data.
Could anyone give me some suggestion on how to improvement it.Following is
my code:
public static void RecordTask(ArrayList otask)
{
string scmd1 = "INSERT INTO TASK (date,taskname) VALUES ('{0}','{1}')";
string scmd2 = "INSERT INTO ASKLIST
(TASKID,tlist,state,begintime,endtime) VALUES
({0},'{1}',{2},'{3}','{4}')";

SqlCeConnection cn = new SqlCeConnection("Data
Source="+dbpath+@"\PDAKEYDB.SDF");

try
{
SqlCeCommand sqlcmd = cn.CreateCommand();
cn.Open();
sqlcmd.CommandText = string.Format(scmd1,DateTime.Now,Config.TaskName);
sqlcmd.ExecuteNonQuery();

sqlcmd.CommandText = "Select @@IDENTITY";
object o = sqlcmd.ExecuteScalar();
int taskid = (int) ((System.Data.SqlTypes.SqlDecimal) o).Value;


foreach (Operate op in otask) {
sqlcmd.CommandText =
string.Format(scmd2,taskid,op.Description,op.OpResult,DateTime.Now,DateTime.Now);
sqlcmd.ExecuteNonQuery();
}

}
catch (SqlCeException sex)
{
MessageBox.Show(sex.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
cn.Close();
}
}


Thanks in advance,
wyghf

 

[Sergey Bogdanov]

You did not say how many items you have in the otask variable. Also you
have potential memory leaks in your code - do not forget to call
SqlCeCommand.Dispose.

 

[João Paulo Figueira [eMVP]]

Inserting through a SQL statement is slow even if you use a parameterized
and prepared statement. The fastest way to insert records in SQL CE 2.0 is
through the base table cursor, an option that is not available through .NET
CF. I am writing a managed wrapper to the OLE DB interfaces of SQL CE 2.0
and already managed to benchmark both approaches on an iPAQ 3850. The base
table approach can be up to 5 times faster than a prepared and parameterized
SQL INSERT statement.

This will be a non-issue with CF 2.0 as it exposes the base table cursor
through the SqlCeResultSet class.

 

[Enrico Pavesi]

Why SqlCeCommand.Dispose??

GC does not do that ?

Thanks

 

[Sergey Bogdanov]

You will need to Dispose any objects that require it (i.e. SqlCeCommand
uses unmanaged resources, when you call Dispose you will release it).

 

[Enrico Pavesi]

Ok but when an object is Garbage collected dispose is called.
Correct?

 

[Chris Tacke, eMVP]

Sure, but when will that happen? Calling Dispose gives you control. The GC
may not call it for minutes, hours or even days depending on the app.

--
Chris Tacke
Co-founder
OpenNETCF.org
Has OpenNETCF helped you? Consider donating to support us!
http://www.opennetcf.org/donate

 

[Guest]

I used Acer n10 (Intel PXA255 300MHZ) did the following tests:
otask.count 10 55 100 200
1000
Time(ms) 520 1923 3333 6419 30610


It's very slow.If I ran it in our wince device (80MHZ CPU).We cannot stand it.
I found the foreach { ...} statement take a lot of time.Is there anyway we
can
handle it batchly?Don't call sqlcmd.ExecuteNonQuery(); for every insert
command.

by the way,we need't call dispose,because the following SqlCeCommand code:
public virtual void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}

~SqlCeCommand()
{
try
{
Dispose(false);
}
finally
{
base.Finalize();
}
}

private void Dispose(bool disposing)
{
if (disposing)
{
if (connection != null)
{
connection.RemoveWeakReference(this);
connection = null;
}
transaction = null;
cmdText = null;
cmdParameters = null;
}
CloseInternal();
}

 

[Guest]

Have you finished your wrapper?Is it free?

Thx,
wyghf

 

[Sergey Bogdanov]

Have you seen the method CloseInternal() in Dispose method?

I see here that it frees at least two unmanaged resources:

....
NativeMethods.uwoledb_ReleaseAccessor(this.pUnkCommand, this.pAccessor);
....
NativeMethods.uwoledb_ReleaseObject(this.pUnkCommand);

 

[Guest]

Yes.Because the destructor has called dispose(),so there is no need to call
it again.

 

[Guest]

I don't think not calling dispose() is the cause of the poor performance.May
be
I use the wrong way to insert data?I don't know. Do you have another way?

Thx,
wyghf

 

[Sergey Bogdanov]

It would be called some time. But when? The best practice to call it
manually (deterministic finalization). See this link it will help you to
understand the differences:

"Dispose, Finalization, and Resource Management "
http://www.bluebytesoftware.com/blog/PermaLink.aspx?guid=88e62cdf-5919-4ac7-bc33-20c06ae539ae

 

[Enrico Pavesi]

Thanks,

i added various dispose to one of my application and now it gives me error
on a BeginTransaction saing "connection state is open-fetching" ?????

The logic is not changed, just added some dispose on SQLCeCommand.

Is this a SqlCe bug??

Thanks

 

[João Paulo Figueira [eMVP]]

Not finished yet and still thinking about what price (if any) to charge...

 

[Ilya Tumanov [MS]]

In our tests 400MHz PXA 255 inserts 1200 records with 33 columns of various
data type each in about 33 seconds using DataSet/DataAdapter.

With CF V2 and SQL Mobile it's down to 14 seconds.



I'd say you should at least use parameterized query to speed it up.


Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

*** Want to find answers instantly? Here's how... ***

1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).

 

[Guest]

Thanks for your advice.I'll read it when I am free.I knew them before from
Jeffy's book.