Sqlite vs Sql Server CE

  • Thread starter Thread starter Finn J Johnsen
  • Start date Start date
F

Finn J Johnsen

Hi

I've only tried SQL Server CE, which seems to be fine. However I am
curious if someone has experience with sqlite. Why go thorugt with the
hassle? The big advantage SQLServerCE has - is that it's really easy to
add when using in Visual Studio.

..NET development is of most interrest, if that matters in this "discussion".

any opinions/experiences are most welcome,
Finn
 
Finn said:
Hi

I've only tried SQL Server CE, which seems to be fine. However I am
curious if someone has experience with sqlite. Why go thorugt with the
hassle? The big advantage SQLServerCE has - is that it's really easy to
add when using in Visual Studio.

.NET development is of most interrest, if that matters in this "discussion".

any opinions/experiences are most welcome,
Finn
Click to expand...

I actually just chose SQLite over SQL Server CE for a project I'm
starting on, so I'll give you my reasons.

First, what I've read says that SQLSCE takes over a megabyte of
installed space on the device. Considering my program will be maybe
25% of that, that's completely unreasonable. The SQLite DLL is 230K,
the .Net wrapper DLL I'm using is about 50K, and System.Data.Common.dll
is about 105K, for a total of just under 400K. Still extremely large,
but more acceptable.

Second, I had no interest in using SQL Server on the desktop,
especially considering licensing issues.

Third, SQLite database files are identical either way. Since I want to
synchronize databases between the device and the desktop, all I have to
do is a simple file transfer, then whatever syncing stuff I need to.
All straightforward, nothing fancy.


So, those are my reasons. I'd be interested in hearing other people's
thoughts as well.

Mark Erikson
http://www.isquaredsoftware.com
 
I'm not sure that 'syncing the data' really means 'copying the data from the
device and overwriting what's on the desktop'. Maybe SQLite can do this
too, but I think that, frequently, what you want is to update *some* records
in the desktop (main) database with changes from the portable device and
maybe add some records. Further, it's quite common to have multiple
portable devices working with the 'same' data, so you might have
modifications from several devices which you have to resolve, as well as
data to move out, as well as in, to the main database (you might want to
replicate the new records from device 1 to device 2 and vice versa, without
copying the entire database).

I don't use database stuff on a regular basis myself, but our devices have
SQL Server CE built into the ROM because our customers seem to want it...

Paul T.
 
Paul said:
I'm not sure that 'syncing the data' really means 'copying the data from the
device and overwriting what's on the desktop'. Maybe SQLite can do this
too, but I think that, frequently, what you want is to update *some* records
in the desktop (main) database with changes from the portable device and
maybe add some records.
Click to expand...

That may be true, but compatibility of the database file
format gives you one big advantage:

You can send a complete database file to the PC or server
and do the replication there, where you have no issues
with the reduced performance of the PocketPC device.

We advise to use our database engine this way:
- Open a second database on the device and copy all
new and modified objects to this database.
- Send the complete replication database to the server
to do replication there.
 
Paul said:
I'm not sure that 'syncing the data' really means 'copying the data from the
device and overwriting what's on the desktop'. Maybe SQLite can do this
too, but I think that, frequently, what you want is to update *some* records
in the desktop (main) database with changes from the portable device and
maybe add some records. Further, it's quite common to have multiple
portable devices working with the 'same' data, so you might have
modifications from several devices which you have to resolve, as well as
data to move out, as well as in, to the main database (you might want to
replicate the new records from device 1 to device 2 and vice versa, without
copying the entire database).

I don't use database stuff on a regular basis myself, but our devices have
SQL Server CE built into the ROM because our customers seem to want it...

Paul T.
Click to expand...


Sorry, perhaps "sync" was a bad choice of words. What I'm planning is
merging changes between the device database and the desktop database,
kinda like CVS-style diffing (pretty much like you described). As Carl
said, though, the ability to move the whole DB file to the desktop and
do the processing there is nice.
 
Hi Mark,

do you have any information on performance?
i would have to use SQLite on a WinCE Project, so any hints is usefull.

Thanks
 
Lemme start this with some relevant links. First, SQLite for Windows
CE is available at http://sqlite-wince.sourceforge.net , and the .Net
wrapper I chose is available at
http://www.phpguru.org/static/SQLite.NET.html . There's a couple of
others available (see
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers for a listing).

Performance... I believe the sample program that comes with the wrapper
was running at about 25 reads and writes per second, running on an iPaq
3835 with PPC 2002.
Hope this helps!

Mark Erikson
http://www.isquaredsoftware.com
 
Carl said:
We advise to use our database engine this way:
- Open a second database on the device and copy all
new and modified objects to this database.
Click to expand...

a bit off topic here. But why do you refer to "objects" instead of records?

regards,
Finn
 
Finn said:
a bit off topic here. But why do you refer to "objects" instead of records?
Click to expand...

Our product is an object database, it stores objects directly
without the detour through SQL.

Feel free to try it out, a trial version is available on our
website.

If you don't like our licensing model, please stay tuned, we
will release under a very developer-friendly licensing model
in November.

Best,
Carl
 
Definitely no argument from me that it's nice to have a portable file
format...

Paul T.
 
ME [27 Sep 2004 02:14:30 -0700]:
Performance... I believe the sample program that comes with the wrapper
was running at about 25 reads and writes per second, running on an iPaq
Click to expand...

Ooooo, you give PPC a bad name!

- - - - -

Here's a table comparing the StrongARM, PXA250, and PXA255.

http://gt40.40th.com/bench_gt40_arm.html

There's a rather complete-ish benchmark app there (MONZA, used to
generate the tables there, a newer monza is available by request)
if you want to compare against your hardware. You'll also find
similar performance from an SD or CF destination. Try it. Does
it matter that I could read 20 million items (in order) a minute?
(Especially since the OP mentioned "2 million records".) It might
not, but I won't ever complain about the speed. And it all fits 7
code pages (under 30 KB). Data is also so much more compact.
Turn on the blowfish and no one else will ever know what's in it.

Here's a brief summary of that page:

Performance summary
------------------------------------------------------------------------------

PXA255/400, Insert: 16,000 items per second (insert rate: 960,000 items/min
Read: 333,000 items per second (read rate: 20+ million items/min

PXA250/400, Insert: 7,000 items per second (insert rate: 425,000 items/min
Read: 200,000 items per second (read rate: 12+ million items/min

SA1110/206, Insert: 3,900 items per second (insert rate: 234,000 items/min
Read: 185,000 items per second (read rate: 11+ million items/min

Notes
------------------------------------------------------------------------------
o The index data structure remains balanced during inserts and deletes (66%
node utilization typical)
o Deletes physically remove the key and data items
o Deleted space is immediately available for reuse
o Optimize can produce node utilization > 95%

- - - - -

Unless you meant something other than 25 a second. That's too slow
to be believable from anything done in the last 25^H^H50 years.
 
Yeah, 25 is pretty ugly. It's not PPC though, it's SQL CE 2.0, especially
it's managed provider. It's insert perf is terrible. Very, very terrrible.

-Chris


ME [27 Sep 2004 02:14:30 -0700]:
Performance... I believe the sample program that comes with the wrapper
was running at about 25 reads and writes per second, running on an iPaq
Click to expand...

Ooooo, you give PPC a bad name!

- - - - -

Here's a table comparing the StrongARM, PXA250, and PXA255.

http://gt40.40th.com/bench_gt40_arm.html

There's a rather complete-ish benchmark app there (MONZA, used to
generate the tables there, a newer monza is available by request)
if you want to compare against your hardware. You'll also find
similar performance from an SD or CF destination. Try it. Does
it matter that I could read 20 million items (in order) a minute?
(Especially since the OP mentioned "2 million records".) It might
not, but I won't ever complain about the speed. And it all fits 7
code pages (under 30 KB). Data is also so much more compact.
Turn on the blowfish and no one else will ever know what's in it.

Here's a brief summary of that page:

Performance summary
------------------------------------------------------------------------- -----

PXA255/400, Insert: 16,000 items per second (insert rate: 960,000 items/min
Read: 333,000 items per second (read rate: 20+ million items/min

PXA250/400, Insert: 7,000 items per second (insert rate: 425,000 items/min
Read: 200,000 items per second (read rate: 12+ million items/min

SA1110/206, Insert: 3,900 items per second (insert rate: 234,000 items/min
Read: 185,000 items per second (read rate: 11+ million items/min
Notes
-------------------------------------------------------------------------
Click to expand...
-----
o The index data structure remains balanced during inserts and deletes (66%
node utilization typical)
o Deletes physically remove the key and data items
o Deleted space is immediately available for reuse
o Optimize can produce node utilization > 95%

- - - - -

Unless you meant something other than 25 a second. That's too slow
to be believable from anything done in the last 25^H^H50 years.

--
40th Floor - Software @ http://40th.com/
iPlay : the ultimate audio player for iPAQs
mp3, ogg, mp4, m4a, aac, wav, play & record
parametric eq, xfeed, reverb - all on a ppc
Click to expand...
 
Okay, three things.

First, updated benchmark. This time, it's 350 writes/sec and 750
reads/sec, iPaq 3870, WM2003. Lot better than the previous one. I
have no idea how that compares with SQL Server CE, but it should work
for me.

Second... some googling around for your product in the newsgroups
turned up several posts similar to this one, including a nice
semi-flame thread in this newsgroup. As I believe has been stated
previously, this is the "Compact Framework" newsgroup. You can
advertise your product here, but it's not going to do many of us much
good unless there's a wrapper we can use.

Finally, just wanted to toss in my thoughts on CF vs C(++). Right now,
my goal is to write programs. I don't want to have to spend all my
time manually tracking memory, string lengths, pointers, and all the
low-level details of the Win32 API (or, in this case, your database).
I'm willing to trade off some level of raw application speed for faster
development time. I see you've got differing opinions on that
tradeoff, and that's fine.

Mark Erikson
http://www.isquaredsoftware.com
 
Back
Top