SQL CE Insert Performance

[info]

Hi there

With what duration do I have to calculate when inserting about 10000
Rows in the SQL CE Database?

Thanks

 

[Alex Feinman [MVP]]

If you use prepared command with parameters - I'd say tens of seconds to
several minutes. This is only a guess of course as the time greatly depends
on indexes (the more indexes - the slower insert is), data types (int - very
fast. Image - very slow) and a number of fields. Finally, the device itself
is of course a factor as they all have different clock speed.

 

[Damon Payne]

The type of media you are using matters a little too. FWIW I have some
numbers (your device will be different)
here
http://www.damonpayne.com/PermaLink,guid,631ca9a4-c3c5-4c1d-92bc-c0aa3e1d761f.aspx
and here
http://www.damonpayne.com/PermaLink,guid,c5553d16-d02b-4362-a0bf-a6912b64d16b.aspx

 

[Alex Feinman [MVP]]

Well, I was going to mention that but since my belief is that it's
extrememly bad idea to use a flash storage for read/write database (at least
a storage card), I decided not to at the end.

 

[Damon Payne]

I'm afraid to ask: why are against putting a read/write database on a
storage card?
I could think of some reasons, but there are also some arguments for
doing so.

 

[Alex Feinman [MVP]]

Primarily performance. This may become less of an issue with the new faster
cards (such as Sandisk Ultra and Extreme, or Lexar Pro). Another factor is
limited lifetime of a flash card especially with regard to number of writes.
Again, newer cards may be more robust

 

[Chris Tacke, eMVP]

And the huge possibility of corrupting the entire thing if the device is put
to sleep or reset during a write, or if the card is pulled during a write.

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

 

[Damon Payne]

Very good points. Having some apps with very large data stores (almost
impossible to rebuild remotely), removable storage has been a safe bet
for me in the past. Hopefully SQL mobile will be as uncorruptable as
claimed, mitigating some of these concerns.

 

[Guest]

The concern isn't SQL Mobile, it's the driver writing to the flash store
itself that's the issue. If the media has a controller that handles power
loss mid erase/write you'll be alright. If not, there's nothing SQL Mobile
can do about it.

-Chris

 

[info]

Thank you a lot for your answers they are very helpfull.

And what about in comparison the performance of an replication?

 

[Damon Payne]

I'm not a big fan of Merg Rep, so I haven't done any testing with the
new stuff yet.

 

[Marc Ambrosius]

And the huge possibility of corrupting the entire thing if the device is
put to sleep or reset during a write, or if the card is pulled during a
write.

But where would you store the database in an ofline scenario?

Marc