> The first idea was pretty dumb (although I prefer to call it 'Rapid
> Prototyping'), which was to run the query on each page, store the results
in
> an array, and then print out the appropriate 10 with <cfloop>.
First of all, there are a number of custom tags in the Allaire tag gallery
that let you do this. You might want to look there before you roll your own
code.
Secondly, you don't really need to do the array step. You can use a normal
CFOUTPUT QUERY="qryQuery" block with two additional parameters to print out
10 records at a time. The two additional parameters are STARTROW and
MAXROWS. STARTROW tells CF which row to start at, and MAXROWS is how many to
display.
So, to output the first ten you would do
<CFOUTPUT QUERY="qryQuery" STARTROW="1" MAXROWS="10">
to do 11-20 you would do
<CFOUTPUT QUERY="qryQuery" STARTROW="11" MAXROWS="10">
It wouldn't take much work to pass the STARTROW value around through form or
URL parameters, thus letting your users navigate through the recordset.
> this was slow (the page was sometimes taking 1 or 2 secords just for the
> queries), so I came up with the idea of using the SELECT TOP x qualifier,
> and increment x or decrement x by 10 as the user clicked on 'next 10' or
> 'prev 10', which helped alleviate the speed a little, but it was still
slow
> as the user clicked through the results. (And re-running the same query
> over and over just felt ugly and wrong)
Re-running the same query over and over is ugly and wrong <g>, at least in
this case. You have two solutions to this problem. One of them, like you
discovered, is to store the query in a session value. You are correct,
however, in that this could quickly eat up gobs and gobs of memory because
each user will have their own copy of this query in memory.
The other solution is to use the CACHEDWITHIN parameter of the CFQUERY tag
to let CF cache the query for you. Here's how it works:
<!--- create the timespan for how long you want the query to be cached. Data
that
changes often should be cached for smaller amounts of time, static data
can
be cached for longer amounts. In this example we'll use 15 minutes --->
<CFSET cache_time = CreateTimeSpan(0, 15, 0, 0)>
<CFQUERY NAME="qryQuery" CACHEDWITHIN="#cache_time#">
<!--- your SQL goes here --->
</CFQUERY>
The really cool thing is that CF does this caching based upon the SQL
statement itself, not the name of the query. So as long as the user is
navigating through the one result set (i.e. the SQL statement isn't
changing), CF will return the cached data. As soon as the user changes
their search criteria CF will re-query the DB.
Query caching is a bit beyond the scope of this email (at least given my
schedule today <g>). You can probably find tons of information on this
topic by searching the Allaire forums, on www.defusion.com or
www.cfnewbie.com, and as always, in Ben Forta's CF Bibles.
Hope this helps,
Seth Petry-Johnson
Argo Enterprise and Associates
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
