10K Record Query Best Practice Question

[Braden]

We have an issue in which we are trying to come up with the best solution to a
problem. The problem is that we have up to 10,000 records that need to be
displayed in such a manner that they can be grouped and sub grouped much like a
file system, but with the ability to easily select an entire group or
individual line items and assign certain values to them.

One suggestion was use of search/filters and paging throuh results, another
was to use cfgrid, and lastly was to use grouping and sub grouping in a manner
that would expand and contract the items.

All of these have inherent problems as the perception is that this should be a
solution where all items can be displayed at once like on an Excel Spreadsheet.

Any ideas? Best Practices in this scenario?

[byron1021]

You could potentially use flash remoting to display the top level, and then
when expanding, have flash go back to the server and get the subset to display.
Other than that you have a lot of overhead just getting the query. So return
as few columns as possible to limit the dataset size. Using maxrows in
cffqurey does no good, it still gets the whole record set . There is also a
lot of overhead getting this much info to the end user, so using page forward
back may be a good solution unless this is an intranet solution, then it may
not be such an issue, and you can use a DHTML type of interface to pop open
groups when clicking on the top level. Another option would be to use a hidden
frame and some fancy JS to callbacks to the server to get subsets of data for
dispaly and show these, somewhat like flash remoting works, but by JS.
Overall the slickest solution is flash remoting. Also don't bother trying a
grid in a flash form, with mx 7. I've been told by MM engineers these only
work really well for smaller datasets. Which kind of makes me wonder how well
flash remoting would handle it and a swf file. Another optoin may be to just
do a tree view using dhtml and when a record title or id is clicked on another
html frame is called that will load the record details, by querying just that
record in a template. This way your initial large query is just returning
10000 id's and maybe a title or header, which shouldn't take too long.