Here is a great idea sent to me personally from a List Member that I wanted to post for everyone to see.




One thought that you may want to consider is caching x number of values at a time.  When the item changes, check the cache FIRST(in a hash based on the index number as the key),  and only if you do not find the cached data, then do the DB lookup.   I would do something like:


load listview and cache x number of values, 1->x (20 may be a good number)

when an item is clicked, check the cache first for existence of the index clicked on

if it is in the cache, show the data from the cache

else run your query to load x number of records into the cache either clearing or keeping all the existing data, depending on your expected number of items total.  This depends on memory,etc. Obviously, if you will have thousands of items in the listview, you may not want to keep all the data in memory for performance reasons. 


This a) keeps memory footprint small, and b) gives quick response in "most" cases, c) reduces total database round trips.  I would suggest using a temp variable to hold the NEW version of the cache until after your DB query lookup has returned.  This way, if the user clicks on an item, the item is not in the cache AND the DB look up fails, you still have all the existing cached data and can have the user try again without losing the current cached values.  


In General, if you or your users would access items sequentially in "most" cases, you could also set up seperate thread to purge/load the cache in the background., say  current index -20 through index +20  


Just a thought...