how can i access to 30.000 records , sort,search.....quiclky

Loading 30k records, no matter what the engine, is going to suck Querying
the data is easiest and most performant with SQL CE. You could get similar
or even better perf using indexed binary files, but that a whole lot of
work, so unless you expect big payoffs, or it is super time critical (in
which case I'd question the decision to use managed code), you've got only
one option here.

-Chris


"William Ryan" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> IMHO, 30,000 records on a PDA won't ever be that quick or easy to see.
I've
> tried loading 300 plus records before and wasn't really impressed with the
> performance..
>
> However, if you must get that much data in one sitting, Chris take Chris'
> advice, he definitely knows his stuff.
>
> Cheers,
>
> Bill
> "DEVELOPER" <(E-Mail Removed)> wrote in message
> news:#(E-Mail Removed)...
> > how can i access to 30.000 records , sort,search.....quiclky
> >
> >
>
>

Couldn't agree with you more.
"Chris Tacke, eMVP" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Loading 30k records, no matter what the engine, is going to suck Querying
> the data is easiest and most performant with SQL CE. You could get
similar
> or even better perf using indexed binary files, but that a whole lot of
> work, so unless you expect big payoffs, or it is super time critical (in
> which case I'd question the decision to use managed code), you've got only
> one option here.
>
> -Chris
>
>
> "William Ryan" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > IMHO, 30,000 records on a PDA won't ever be that quick or easy to see.
> I've
> > tried loading 300 plus records before and wasn't really impressed with
the
> > performance..
> >
> > However, if you must get that much data in one sitting, Chris take
Chris'
> > advice, he definitely knows his stuff.
> >
> > Cheers,
> >
> > Bill
> > "DEVELOPER" <(E-Mail Removed)> wrote in message
> > news:#(E-Mail Removed)...
> > > how can i access to 30.000 records , sort,search.....quiclky
> > >
> > >
> >
> >
>
>

WR [Sun, 7 Sep 2003 18:54:26 -0400]:
>IMHO, 30,000 records on a PDA won't ever be that quick or easy to see.

You don't get around much. 30k is only where things start to even be
measurable, if you know the right (good) software. If you only know the
wrong (bad) software, I can see where you might say that.

PXA255/400, Insert: 16,000 items per second (insert rate: 960,000 items/minute
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/minute)
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/minute)
Read: 185,000 items per second (read rate: 11+ million items/min

Notes
------------------------------------------------------------------------------

The index data structure remains balanced during inserts and deletes
(66% node utilization typical)
Deletes physically remove the key and data items
Deleted space is immediately available for reuse
Variable-length keys, variable-length data records
Unlimited sort orders (any and all types, compound, anything) per file
Optimize can produce node utilization > 95% (optimize 100,000 recs in 5 secs)


Full story:

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

--
40th Floor - Software @ http://40th.com/
GT40 encryption-database toolkit for Win32 and Linux
Download today for vc6, vb6, vc7, evc & gcc

Your promoting a non CF compatible database (that doesn't support SQL
statements so far as I could tell) and comparing it to SQL Server CE? I'm
pretty impressed by SQL Server CE to date. That team has done a wonderful
job and I'm still amazed it's a freebie.

I think a reasonable question regarding retrieving 30K worth of records is,
has the relevant functional requirement been correctly mapped to a handheld
device design constraint. I'm sure cases do exist where one needs 30K of
records at once, and this may be one of them, but there may also be a better
way to approach the problem.

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> WR [Sun, 7 Sep 2003 18:54:26 -0400]:
> >IMHO, 30,000 records on a PDA won't ever be that quick or easy to see.
>
> You don't get around much. 30k is only where things start to even be
> measurable, if you know the right (good) software. If you only know the
> wrong (bad) software, I can see where you might say that.
>
> PXA255/400, Insert: 16,000 items per second (insert rate: 960,000
items/minute
> 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/minute)
> 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/minute)
> Read: 185,000 items per second (read rate: 11+ million
items/min
>
> Notes
> -------------------------------------------------------------------------
-----
>
> The index data structure remains balanced during inserts and deletes
> (66% node utilization typical)
> Deletes physically remove the key and data items
> Deleted space is immediately available for reuse
> Variable-length keys, variable-length data records
> Unlimited sort orders (any and all types, compound, anything) per file
> Optimize can produce node utilization > 95% (optimize 100,000 recs in 5
secs)
>
>
> Full story:
>
> http://40th.com/gt40/bench_gt40_arm.html
>
> --
> 40th Floor - Software @ http://40th.com/
> GT40 encryption-database toolkit for Win32 and Linux
> Download today for vc6, vb6, vc7, evc & gcc

Navigating through 30K records on a QVGA screen is close to my idea of hell.
Think hundreds, not thousands. So I concur with Justin -- go back and look
at your solution design and see if you can trim those records down.


"Justin Weinberg" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Your promoting a non CF compatible database (that doesn't support SQL
> statements so far as I could tell) and comparing it to SQL Server CE? I'm
> pretty impressed by SQL Server CE to date. That team has done a wonderful
> job and I'm still amazed it's a freebie.
>
> I think a reasonable question regarding retrieving 30K worth of records
is,
> has the relevant functional requirement been correctly mapped to a
handheld
> device design constraint. I'm sure cases do exist where one needs 30K of
> records at once, and this may be one of them, but there may also be a
better
> way to approach the problem.
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > WR [Sun, 7 Sep 2003 18:54:26 -0400]:
> > >IMHO, 30,000 records on a PDA won't ever be that quick or easy to see.
> >
> > You don't get around much. 30k is only where things start to even be
> > measurable, if you know the right (good) software. If you only know the
> > wrong (bad) software, I can see where you might say that.
> >
> > PXA255/400, Insert: 16,000 items per second (insert rate: 960,000
> items/minute
> > 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/minute)
> > 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/minute)
> > Read: 185,000 items per second (read rate: 11+ million
> items/min
> >
> > Notes
>
-------------------------------------------------------------------------
> -----
> >
> > The index data structure remains balanced during inserts and deletes
> > (66% node utilization typical)
> > Deletes physically remove the key and data items
> > Deleted space is immediately available for reuse
> > Variable-length keys, variable-length data records
> > Unlimited sort orders (any and all types, compound, anything) per file
> > Optimize can produce node utilization > 95% (optimize 100,000 recs in
5
> secs)
> >
> >
> > Full story:
> >
> > http://40th.com/gt40/bench_gt40_arm.html
> >
> > --
> > 40th Floor - Software @ http://40th.com/
> > GT40 encryption-database toolkit for Win32 and Linux
> > Download today for vc6, vb6, vc7, evc & gcc
>
>

Again, your proposal is a non-CF solution, so your comment is irrelevant
here. The point is that for a CF, Windows CE solution, he currently only
has one choice for reasonable performance on this size data set.

There are several non-CF databases that perform substantially better than
SQL CE. I've used a few of them, one of which is *way* faster than 16,000
inserts per second, but it's an academic argument - there's no CF interface
for it, so the original poster couldn't use it for his solution.

--
Chris Tacke, eMVP
Advisory Board Member
www.OpenNETCF.org
---
Windows CE Product Manager
Applied Data Systems
www.applieddata.net


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> WR [Sun, 7 Sep 2003 18:54:26 -0400]:
> >IMHO, 30,000 records on a PDA won't ever be that quick or easy to see.
>
> You don't get around much. 30k is only where things start to even be
> measurable, if you know the right (good) software. If you only know the
> wrong (bad) software, I can see where you might say that.
>
> PXA255/400, Insert: 16,000 items per second (insert rate: 960,000
items/minute
> 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/minute)
> 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/minute)
> Read: 185,000 items per second (read rate: 11+ million
items/min
>
> Notes
> -------------------------------------------------------------------------
-----
>
> The index data structure remains balanced during inserts and deletes
> (66% node utilization typical)
> Deletes physically remove the key and data items
> Deleted space is immediately available for reuse
> Variable-length keys, variable-length data records
> Unlimited sort orders (any and all types, compound, anything) per file
> Optimize can produce node utilization > 95% (optimize 100,000 recs in 5
secs)
>
>
> Full story:
>
> http://40th.com/gt40/bench_gt40_arm.html
>
> --
> 40th Floor - Software @ http://40th.com/
> GT40 encryption-database toolkit for Win32 and Linux
> Download today for vc6, vb6, vc7, evc & gcc

There is a trick you can use...

Obtain a total count of all the records on your device (30,000). Place a
seperate scrollbar on your form (not attached to the data grid) and set the
max value of that scrollbar to the high limit you just retrived. Next, if
your display window can show say... 20 records at a time - then load 200 -
300 records into an array. Use your scrollbar to navigate the record array
and if the user ever goes over the cached amount - load the next array. For
extra speedy response times... have a previous, current and next array of
records.

Essentially - this is a programatic method for paging a recordset... which
is exactly what the sql query window in enterprise manager uses.

I have used this method on the handheld navigating student enrollment
records (one had 70,000 entries) and the response time was quite good.

Rick Winscot
rickly@zyche dot com



"DEVELOPER" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> how can i access to 30.000 records , sort,search.....quiclky
>
>

I am curious, have you tried this without the caching?

We do a similar thing except without the cache, speed is quite fast. I
figure the cache doesn't really achieve anything but another overhead.
Access to a resultset (in our case via ADOCE or OLEDB) is all done in RAM
and retrieval of records is generally very quick (for both direct table and
query access). I am not sure about an SDF file on a compact flash card in
direct table mode, maybe this is where you see improvements using cache. Not
sure, but I would be keen to hear comparisons.

Regards,

David.


"Rick Winscot" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> There is a trick you can use...
>
> Obtain a total count of all the records on your device (30,000). Place a
> seperate scrollbar on your form (not attached to the data grid) and set
the
> max value of that scrollbar to the high limit you just retrived. Next, if
> your display window can show say... 20 records at a time - then load 200 -
> 300 records into an array. Use your scrollbar to navigate the record
array
> and if the user ever goes over the cached amount - load the next array.
For
> extra speedy response times... have a previous, current and next array of
> records.
>
> Essentially - this is a programatic method for paging a recordset... which
> is exactly what the sql query window in enterprise manager uses.
>
> I have used this method on the handheld navigating student enrollment
> records (one had 70,000 entries) and the response time was quite good.
>
> Rick Winscot
> rickly@zyche dot com
>
>
>
> "DEVELOPER" <(E-Mail Removed)> wrote in message
> news:#(E-Mail Removed)...
> > how can i access to 30.000 records , sort,search.....quiclky
> >
> >
>
>

David,

You are right. On the desktop, the cache is unnecessary. However, the
handheld is a different story. I initially tried this process without
caching blocks of records - and the speed was bearable. Caching improved
handheld performance - but there was a balance point in caching/non-caching.

Application response times improved dramatically when pulling smaller chunks
of data from a properly indexed table (primary key was numeric) but started
to slow down again when the ratio of db gets increased. Considering the
overhead per-query, this wasn't to surprising. Access from a flash card was
even more pronounced - as the access time to the flash card adds significant
overhead.

Rick Winscot
rickly@zyche dot com


"David Thornley" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I am curious, have you tried this without the caching?
>
> We do a similar thing except without the cache, speed is quite fast. I
> figure the cache doesn't really achieve anything but another overhead.
> Access to a resultset (in our case via ADOCE or OLEDB) is all done in RAM
> and retrieval of records is generally very quick (for both direct table
and
> query access). I am not sure about an SDF file on a compact flash card in
> direct table mode, maybe this is where you see improvements using cache.
Not
> sure, but I would be keen to hear comparisons.
>
> Regards,
>
> David.
>
>
> "Rick Winscot" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > There is a trick you can use...
> >
> > Obtain a total count of all the records on your device (30,000). Place
a
> > seperate scrollbar on your form (not attached to the data grid) and set
> the
> > max value of that scrollbar to the high limit you just retrived. Next,
if
> > your display window can show say... 20 records at a time - then load
200 -
> > 300 records into an array. Use your scrollbar to navigate the record
> array
> > and if the user ever goes over the cached amount - load the next array.
> For
> > extra speedy response times... have a previous, current and next array
of
> > records.
> >
> > Essentially - this is a programatic method for paging a recordset...
which
> > is exactly what the sql query window in enterprise manager uses.
> >
> > I have used this method on the handheld navigating student enrollment
> > records (one had 70,000 entries) and the response time was quite good.
> >
> > Rick Winscot
> > rickly@zyche dot com
> >
> >
> >
> > "DEVELOPER" <(E-Mail Removed)> wrote in message
> > news:#(E-Mail Removed)...
> > > how can i access to 30.000 records , sort,search.....quiclky
> > >
> > >
> >
> >
>
>