It sounds like an acceptable solution for a limited number of users -- but
it could get to be resource intensive quickly; I don't know how CF handles
heavy memory requirements like this. One thought is that you'll almost
certainly want to use <CFLOCK></CFLOCK> to protect your session variables as
youl load them or you risk memory leaks during peak usage.
I'm not a SQL expert by any stretch but there should be a way to offload a
lot of that logic to the database server so that you're not storing huge
recordsets in memory on the Web server. Judging by your requirements you
could prequalify and execute a select statement with some other preferences
(stored as session variables) and seriously limit the recordset returned to
the user. This would speed up the initial hit significantly as well.
Try this:
select ID, Record from Table where ID > #iif(isDefined(session.lastID),
"session.LastID", "1")# and rownum < 10 order by ID
<!--- session.LastID is the unique identifier of the last record of the
previous select the result of this query should give you the next ten
records. note: I work with Oracle and don't know beans about Access or SQL
server, etc... --->
J.
> -----Original Message-----
> From: Dana Larose [mailto:[EMAIL PROTECTED]]
> Sent: Friday, June 30, 2000 11:20 AM
> To: [EMAIL PROTECTED]
> Subject: On storing query results in session variables
>
>
> Hello everyone, I need advice on whether I've come up with a
> good idea, or a
> horrible monstrosity :)
>
> The scenario is this: I have a search results page (it's for
> a tourism
> site) that can return, with the widest possible search
> criteria over 1000
> records, but the page is designed to display the results 10 at a time.
>
> 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>. Naturally,
> 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)
>
> The other day, I tried putting the query results in a 2d
> array and made that
> array a session variable, and that worked like a charm. The
> first time the
> page is opened, it is still slow, but after that, the pages
> are much faster
> now that I don't run the query and re-create the array over
> and over again.
>
> My boss was pleased because the pages go nice and fast, but I've been
> wondering if this is a poor solution. For instance, if multiple users
> connect to the site at the same time, the session variables
> for the searches
> could eat up gobs of memory on the server.
>
> Is there a better way of doing this? What other problems
> will there be with
> session variables that I haven't thought of?
>
> (BTW - sorry for the long post!)
>
> Dana Larose
> Database Design
> www.cdnwebdesign.com
>
>
> --------------------------------------------------------------
> ----------------
> 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.
------------------------------------------------------------------------------
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.
