Questions on Using SqlCeResult sets for Many Inserts

  • Thread starter Thread starter Boas Enkler
  • Start date Start date
B

Boas Enkler

Hi

I found this code here :

cmd.CommandText = "SELECT * FROM myTable";
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable |
ResultSetOptions.Scrollable);
// Add a record
SqlCeUpdatableRecord rec = rs.CreateRecord();
// Insert values into the 3 fields
rec.SetInt32(0, 34);
rec.SetDecimal(1, (decimal)44.66);
rec.SetString(2, "Sample text");
rs.Insert(rec);

According to an situation I've in my application I got some questions
and I hope someone may help me :)

As background information: As part of an synchronisation process the
Insert method could fire about 30.000 times in a row. So I search each
possibility to improve this process.

wouldn't it better to set "Select * from myTable where 1=2" when I just
use the Result set for inserts? So I just would get the structure and no
data?

Whats the better practive to use SetString or SetSqlString ?When is
which best?

Should I create everytime a new SqlCeUpdateableRecord or may I stored it
in an class propertie and just assign new values each time? So I would
save time for the new() command, wouldn't I ? Same for SqlCeResultSet ?

Are there anymore ways to improve insert times ?

Thanks in advance!
 
Boas,

You can reuse the same SqlCeUpdateableRecord, and that should save you time.
You can also reuse the same SqlCeResultSet unless you need to issue new
queries for the result set or its SqlCeCommand object. Rather than using any
select statement at all, you'd probably get the best insert performance by
using TableDirect similar to this:

SqlCeEngine engine = new SqlCeEngine("Data Source = " + FileName2);
engine.CreateDatabase();
SqlCeConnection conn = new SqlCeConnection("Data Source = " + FileName2);
conn.Open();
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = CreateTableCommand;
cmd.ExecuteNonQuery();
// Add 10,000 rows using SqlCeResultSet
cmd.CommandText = "TestTable"; // name of table
cmd.CommandType = CommandType.TableDirect;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable);
SqlCeUpdatableRecord rec = rs.CreateRecord();
rec.SetString(1, "ResultSet");
rec.SetDateTime(2, DateTime.Now);
rec.SetDecimal(3, (decimal)987654321);
int startTick = Environment.TickCount;
for (int i = 0; i < 10000; i++)
{
rs.Insert(rec);
}
int endTick = Environment.TickCount;
conn.Close();


I haven't tested using SetString vs. SetSqlString so I don't know the
answer, but it would be an easy test for you to do.
 
Back
Top