Ajas,
From what I have seen, very few times are people willing to look at 100
records or more. Paging is definitely the way to go. (and I agree the
50-100 records per page is usually the max I would use on a single page.)
The syntax to use depends on the database. MySql uses a simple LIMIT
statement which allows you to use an offset and max records, Oracle is a
bit more archaic needing to use subqueries and filtering out records
based on the row number. I have not tried this in SQL Server lately, but
the last time I did it was similar to Oracle using subqueries in order
to accomplish this. Even though MySQL has easier syntax, Oracle and SQL
server both have windowing functions which will allow you to get an
overall record count without requiring a second SQL statement.
If you can not change the existing SQL/SP returning the entire dataset,
I would suggest using CF9+ caching to temporarily hang on the the
results and only process the 50/100 needed per page.
As for jQuery, the last jQuery paging system I had to use took the
entire resultset at once and stored it in javascript. So every time a
user landed on the page, the entire query and resultset where returned
in the request with all the overhead. (i.e. all 8,000 or 80,000 records
were returned and processed into javascript.) The page took a long for
the initial load time even though paging was relatively quick
afterwards. With large datasets you are ALWAYS better off handling one
page of data at a time. If you used jQuery with this dataset, make sure
you look for a module that only retrieves 1 page of data at a time.
Every situation is different... a few years ago, I had html paging that
would request 1 page at a time and clicking on the button would submit
form or url variables to get the next page and re-render it. I've also
done it with Iframes where buttons on the parent frame cause reloads of
data on the iframe, and in Ajax. Whatever you are familiar with can work.
I do want to stress again, you really do want the server to handle the
paging, not the client. The larger the resultset the more important it
is to page. I recently added paging to one area that had 9000 records
with about 6 columns of data. Dropping down from 9000 records to 100 per
page lead to 95% decrease in server processing and bandwidth time. (This
was measured through the dev tools in Chrome, the network tab can will
tell you where the time was spent for each resource.) Actual time went
from over 6 seconds, to under 1/4 second. It does not sound like much,
but these were small records, it did not include any client side time
(which of course will vary depending on the system), and when you
multiply this out by the number of page views, it does represent a
significant amount of server load. With larger records, or 10x the
volume, the savings adds up much quicker... (I actually expect to do
the same to a different webpage with roughly 5x the number of records in
the next week or two.)
--Frank
On 05/27/2013 11:40 PM, Ajas Mohammed wrote:
Hello everyone,
Can you share your experience in creating Report Design for HTML
reports returning 8000 or more than 8000 records from a DB (query or
stored proc). 8000 is just a number I picked. usually our reports
return from 10, 100, 1000, 10000 or even more records.
I came across Datatables (jQuery) but wanted to know if that is good
architecture for displaying large resultset returned by DB call with
probably 50/100 records per page.
Do you use anything(external tools/jQuery etc) like that or just
display html tables/divs with pagination?
<Ajas Mohammed />
iUseDropbox(http://db.tt/63Lvone9)
http://ajashadi.blogspot.com
We cannot become what we need to be, remaining what we are.
No matter what, find a way. Because thats what winners do.
You can't improve what you don't measure.
Quality is never an accident; it is always the result of high
intention, sincere effort, intelligent direction and skillful
execution; it represents the wise choice of many alternatives.
-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by http://www.fusionlink.com
-------------------------------------------------------------
