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

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



[quoted text, click to view]

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.


[quoted text, click to view]

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


[quoted text, click to view]

Rick,



No, the point I was making is that caching on the handheld (in the scrolling
example) would probably have no beneficial outcome. You will likely see
improvement on the desktop but not on a handheld device. On the desktop, you
have the benefit of retrieving multiple rows at once through OLEDB (unlike
on the device which retrieves them one at a time) but you must also factor
in issues of network latency and even simple bottlenecks like accessing a
data page from a harddisk. Low level hardware caching on the hard disk and
general caching in the OS would counteract most situations. Those are the
real delays which your described method of caching will definitely improve.



I figured that since those delays are not applicable on a handheld device
(other than like I mentioned on an SDF on a CF card or RDA) because the SDF
file is typically stored entirely in RAM. The only overhead I foresee is
access to the actual data in the sdf pages and the fact it has to come
through the engine and oledb abstraction layer to the application.



It is interesting to hear you did see significant benifit though. If I get a
chance I may try caching similar to what you have and determine if it
results in any real benefit.



Hypothetically, I get the feeling you'll see a quite significant delay if
you suddenly drag the scroll bar past the 200-300 record limit and it has to
pull in the whole 300 records (worst case) instead of just the displayed 20
like our method, then again I suppose that is your balance point and it
depends soley on how your users are viewing data. We never show the user a
list beyond say 200-500 records, and we often provide search functionality
which either highlights or filters appropriate records to justify showing so
many records in the first place. Browsing to find anything over 100 records
is ridiculous and for practicality, categorisation as a start would be a
must. Whenever we need to store something like 30 000 records, we tend to
use our own custom data structures like hashtables and binary files, being
able to manipulate the data at this level is a pain but achieves the
absolute best (and usually acceptable) performance.



On another note, have you noticed a dramatic drop in performance as a record
size reaches a certain threshold say 25 + average fields with a couple of
large nvarchar's? I imagine we have crossed a page limit in sqlserverce,
however the performance is not consistent across platforms and hardware
devices (particularly MIPS -> ARM). We have noticed certain odd behaviour
(inconsistent performance) and often come to the conclusion that we are just
pushing the engine too much (possibly related to the amount of RAM
available). I only wish we had some better tools to diagnose these types of
issues. Soon I am going to try out Sybase Anywhere 9.0 as they seem to have
improved their OLEDB interface a little, and see if there much difference
between the two engines.



Interesting to hear any comments and I will surely let you know if I find
any results to the contrary in regards to the caching.


Thanks for the info.

Regards,



David.




[quoted text, click to view]