0

I have been working on C# Compact Framework 4.0 for my product.

I have an server and an mobile and i have to synchronize all the data from the server to my mobile device .

Usually around 6000 entries will be inserted into my mobile device on an single table .

Now i use table direct with index name of the table specified .

It takes around 55 sec for 6000 entries.

Is there an faster way to insert ?? 

SqlCeCommand cmdItem = conn.CreateCommand();
SqlCeResultSet rsItem;
cmdItem.CommandText = "item_info";
cmdItem.IndexName = "PK_item_info";
cmdItem.CommandType = CommandType.TableDirect;
rsItem = cmdItem.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
while(reader.read()){

    SqlCeUpdatableRecord recItem = rsItem.CreateRecord();
    recItem.SetString(1, cmdItem.SerialNo);
    recItem.SetInt32(10, 0);
    rsItem.Insert(recItem);  

}
user3383301
  • 1,891
  • 3
  • 21
  • 49
  • Maybe it would be better if you've shared your current code – Ghasem Dec 28 '15 at 09:01
  • Well, hard to say without seeing your code, but keeping the connection open and specifying the data type explicitly when adding parameters to your SQL commands are two things that can influence speed. Otherwise, well, it's a mobile device and the mobile SQL Server. Don't expect miracles to happen in terms of speed. – Thorsten Dittmar Dec 28 '15 at 09:13

2 Answers2

1

Remove " | ResultSetOptions.Scrollable"

And move the line:

SqlCeUpdatableRecord recItem = rsItem.CreateRecord();

Above the while loop.

var cmdItem = conn.CreateCommand();
cmdItem.CommandText = "item_info";
cmdItem.CommandType = CommandType.TableDirect;
var rsItem = cmdItem.ExecuteResultSet(ResultSetOptions.Updatable);
var recItem = rsItem.CreateRecord();
while(reader.read())
{        
    recItem.SetString(1, cmdItem.SerialNo);
    recItem.SetInt32(10, 0);
    rsItem.Insert(recItem);
}
ErikEJ
  • 40,951
  • 5
  • 75
  • 115
  • What about the indexName ?? WIll using index name increase perfomance ?? – user3383301 Dec 28 '15 at 09:55
  • Nope, it is not used at all (Index name is used when using Seek and SetRange) – ErikEJ Dec 28 '15 at 09:59
  • I get a speed of 109 rows per second approx . Before am getting about 95 rows per second approx . Any other methodologies ?? I would be great if i get about 130 rows per second approx. – user3383301 Dec 28 '15 at 10:22
  • So it is faster now? Make sure all data types are the smallest size possible, You could experiment with dropping and recreating indexes. Look at using a faster disk (maybe fast SD Card?) – ErikEJ Dec 28 '15 at 11:58
  • Too many indexes will slow down the insertion process. Maybe there are indexes you don't need or that you can optimize? – Thorsten Dittmar Dec 31 '15 at 14:14
0

Using this library you can use SqlBulkCopy (SQLBulkCopy Class (Microsoft website)) with your SQL Server CE database.

Library which supports SQL Server CE

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
usselite
  • 846
  • 7
  • 24