question re: data import from remote SQL to sqlCE database

[Milsnips]

Hi there,

i've recently changed my code to use sqlceResultSet with great performance
increase but i just had a question in general.
Im using an imate JAMIN, and when i connect to remote database, get the
sqlDataReader and start looping through it and adding the items to my local
table, i notice on certain tables the import speed starts to drop, on some
tables more, on others less, can anyone explain this?

ex. i have 1 table that imports 400 products and after about 250 records,
the import speed slows down and looks like pretty much the same performance
as cmd.ExecuteNonQuery compared to the sqlCeResultSet row insert.

Could it have anything to do with the datatypes being used in the local
tables? Also i'm importing unicode data but i dont think this should make
much difference or does it?

No other applications are running in the background, and there is plenty of
memory (approx 15-20mb free)

Any feed appreciated,
thanks,
Paul

 

[Alberto Silva]

Hi,
Have you tried prepared commands with parameters for the .ExecuteNonQuery?
Does your table have [many] indexes?

Alberto Silva
MS Mobile Devices MVP
http://msmvps.com/AlbertoSilva

 

[Milsnips]

Hi Alberto,

Yes i did try the preparing commands method also, but im not using
ExecuteNonQuery anymore as the performance was slower, instead i'm using
SqlCeResultSet to directly write to the table (using sqlCeUpdateableRecord)
and bypass the ADO.NET methods for much better write performance.

However when i was using ExecuteNonQuery, the same thing would happen after
similar number of records inserted.
I set a Primary key for ProductID (int), and the query analyser also shows
that it has created an index for that also.

regards,
Paul.

Hi,
Have you tried prepared commands with parameters for the .ExecuteNonQuery?
Does your table have [many] indexes?

Alberto Silva
MS Mobile Devices MVP
http://msmvps.com/AlbertoSilva

Hi there,

i've recently changed my code to use sqlceResultSet with great
performance
increase but i just had a question in general.
Im using an imate JAMIN, and when i connect to remote database, get
the
sqlDataReader and start looping through it and adding the items to my
local
table, i notice on certain tables the import speed starts to drop, on
some
tables more, on others less, can anyone explain this?
ex. i have 1 table that imports 400 products and after about 250
records, the import speed slows down and looks like pretty much the
same performance as cmd.ExecuteNonQuery compared to the sqlCeResultSet
row insert.

Could it have anything to do with the datatypes being used in the
local tables? Also i'm importing unicode data but i dont think this
should make much difference or does it?

No other applications are running in the background, and there is
plenty of memory (approx 15-20mb free)

Any feed appreciated,
thanks,
Paul