Darren
Wed Oct 20 13:53:42 CDT 2004
You're in a Microsoft forum. The question was seeking performance
characterization
of SQL CE. My answer shared my personal experience with large datasets
using
SQL CE.
Good to know there are alternatives, but that wasn't the question.
-Darren
<hel@40th.com> wrote in message
news:enl7rYstEHA.2808@TK2MSFTNGP14.phx.gbl...
> DS- [Wed, 20 Oct 2004 08:53:18 -0600]:
> >I have a 42,508 row table in SQL CE that holds Zip Codes, City, State
> >used
> >as >lookup data to accelerate data entry.
> >The size of the database on device is 4MB.
> >Here are some performance comparisons:
>
> >iPaq 4355 (XScale 400Mhz), 64MB RAM, PPC 2003
> >Test 1 = SELECT city & state based on zip code with nonclustered index on
> >zipcode - < 1 sec Test 2 = same without the index: 7 seconds
> >
> >iPaq 3975 (XScale 400Mhz), 64 MB RAM, PPC 2002
> >Test 1: 2 - 3 seconds Test 2: 18 seconds
>
>
> Sort of vague. I HOPE you don't mean you're looking for
> -ONE SINGLE ITEM-. That'd be terrible. And what happens
> when you put it on a storage card? How about encryption?
> All very necessary things, but easy to dismiss off-hand.
>
>
> >Moral of this story: use indexes and the most powerful device you can
> >with
> >large data tables on SQL CE.
>
>
> Actually, use the best software know-how, not stuff shoved
> down your throat by way of you-know-who (aha):
>
> Full bench results
>
>
http://gt40.40th.com/bench_gt40_arm.html
>
> Very brief summary of those results:
>
> 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%
>
> - - - - - -
>
> .. and observe from the full bench results how well it
> scales to more and more rows -- practically linear.
>
> --
> 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