Speed of inserting data into a table SqlServerCE

[Brett Miller]

I have written some proof of concept code that adds records to a table,
utilizing SqlServerCe, using a parameters in a insert statement.

I am forced to read data from a BinaryStream (in the form of a file), assign
values to the parameters and execute the sql insert statement from within a
while loop

The table contains 11 fields, and it currently takes over two minutes to
read 4500 records...

This is maddness, as I am porting from an old application, and I can
accomplish the above in about 2 seconds using my old code (written in OPL)
using a device thats 4x slower than the one I'm currently developing on.

Anybody have an idea how to speed this up? or mybe a sneaky workaround...

Regards BM

 

[Jon Skeet [C# MVP]]

I have written some proof of concept code that adds records to a table,
utilizing SqlServerCe, using a parameters in a insert statement.

I am forced to read data from a BinaryStream (in the form of a file), assign
values to the parameters and execute the sql insert statement from within a
while loop

I presume you're using a single SqlCeCommand repeatedly, just changing
the parameters? If not, that would be a good start.

The table contains 11 fields, and it currently takes over two minutes to
read 4500 records...

This is maddness, as I am porting from an old application, and I can
accomplish the above in about 2 seconds using my old code (written in OPL)
using a device thats 4x slower than the one I'm currently developing on.

Anybody have an idea how to speed this up? or mybe a sneaky workaround...

I don't at the moment, but I'll be looking at something similar myself
soon, I believe. We're seeing similar kinds of slow-downs. For loading
the table back from the database into memory, we found that setting the
MissingSchemaAction of an adapter to Add rather than AddWithKey made a
huge difference (from over a minute to about 8 seconds).

I'll let you know if I find a similarly efficient technique with
inserts.

 

[Richard Thombs]

I presume you're using a single SqlCeCommand repeatedly, just changing
the parameters? If not, that would be a good start.




I don't at the moment, but I'll be looking at something similar myself
soon, I believe. We're seeing similar kinds of slow-downs. For loading
the table back from the database into memory, we found that setting the
MissingSchemaAction of an adapter to Add rather than AddWithKey made a
huge difference (from over a minute to about 8 seconds).

I'll let you know if I find a similarly efficient technique with
inserts.

I have a problem with the speed of inserts too. The insert performance
in my app took a dive over the last few revisions so I'm going to go
back and see what I did to break it. If I spot anything obvious I'll
post it.

Also, I came across this SSCEDirect product
(http://www.fitiri.com/SSCEDirect.html) which I might be turning to if I
can't improve performance.

Cheers,

R.

 

[Richard Thombs]

Found the cause for my slowdown and it had nothing to do with SqlCe

I was creating a fresh CultureInfo on every row insert so I could do
some parsing and boy did it whack performance - performance went from
340 seconds to 540 seconds!!

R.

 

[tiger79]

Well, I'm filling a tabase with 41000 records on an Ipaq 3870 and it takes
around 17 minutes so I guess ur speed is quite normal... I wanted to try out
on a new Toshiba e750 which should be way faster only problem is that I get
: The rmote connection to the device has been lost. Pleae verify the device
connection and restart debugging.... obviously ActiveSync sees the pda
still and the connection didnt really get broken...
But I guess the speed depends on the pda as well....

 

[Khanh]

We are downloading a table with 175,000 records from webservice then
insert to SQL CE.Approximate time:
- Time for downloading : 1h
- time for insert: 1h

We are trying and experiencing many methods to improve such as :
config IIS as Microsft hints, index Database server, write optimize
Insert command to SQL CE, and the time above is smallest.

Just share my experience.

Regards,
Nguyen Truong Khanh

 

[Khanh]

Dear all,

In order to improve the time, I intend to implement 2 threads: 1 for
downloading data, 1 for inserting SQL CE. Is that good solution?
Hope to share and hear insight idea from everybody.

Thanks

Khanh