can I get some definitive suggestions please on how to create working script
to achieve the paging query results to simulate similar to what you see on
search engines
----- Original Message -----
From: Peter Theobald <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Saturday, August 05, 2000 5:45 AM
Subject: RE: Paging query results question
| --=====================_20677026==_.ALT
| Content-Type: text/plain; charset="us-ascii"
|
| On the mailing list I *CONSTANTLY* hear people mention facts that are not
in the Cold Fusion documentation.
| In this example, how did you know there is a limit of 100 cached queries?
The Cold Fusion Language Reference doesn't say that in the description of
CFQUERY.
|
| Where is everybody getting this information.
| I must say that, although I love working in Cold Fusion, the Cold Fusion
documentation is among the WORST language documentation I have ever had the
misfortune of struggling with.
|
| Even the so-called Reference manual is little more than a brief
description of each TAG.
| Where is the definitive definition of exactly how each element behaves?
|
| How can they say <CFSCRIPT> is "like" Javascript, but not exactly?! Where
is the rigorous description of every element?
| When I write CFSCRIPT programs that are "like" Javascript, I find out by
trial and error just how "like" it is and isn't.
|
| At 07:17 AM 8/4/00 -0700, paul smith wrote:
|
| >I've done something similar, except I cache the big query for 5 minutes.
| >
| >In addition, I store the long list of IDs as a client variable (in a
| >database). The cache thus trades the need to create sublists for a
| >database read AND speeds up the paging queries (since the cache is in
| >memory). Works well, but on a busy site I'm concerned I'll run up
against
| >the limit of 100 cached queries.
| >
| >I also wonder about the speed of lists, both the time it takes to create
| >them, and the resultant use of WHERE ID IN (#ID_List#), compared to using
a
| >JOIN on a table that contains the IDs as a column.
| >
| >Anyone?
| >
| >best, paul
| >
| >At 10:14 AM 8/4/00 +0100, you wrote:
| >>Ok here's my take on the problem, and actually what I use.
| >>
| >>Notes, we are always reloading the the same page results.cfm (ie. next
| >>& prev links link right back to results.cfm), there are three
| >>variables which will get passed, startRow, maxRows & resultIDList, all
| >>of which I assume are undefined when I first enter the page. startRow
| >>& maxRows are passed via the URL, resultIDList is passed in some other
| >>fashion; client.resultIDList, or whatever takes your fancy (passing
| >>all three in a hidden form is quite nice).
| >>
| >>Enter page.
| >>if startRow & maxRows are undefined, give them some value.
| >>if resultIDList is undefined then...
| >> Do the QUERY, but return only the ID values:
| >> SELECT id FROM somewhere WHERE somethingComplex
| >> create resultIDList as an empty list, and then loop round the query
| >>populating the list
| >> resultIDList = ''
| >> <loop query="aboveQuery">resultIDList =
| >>listAppend(resultIDList,#id#)</loop>
| >>endif
| >>
| >>What we have done here is checked to see if a list already existed, if
| >>it didn't we run the query to get all the records which match our
| >>search out, and only return the ID value.
| >>Next time we enter this page, the list will already have been defined,
| >>therefore the query will not run again (handy for those 200 record
| >>returns)
| >>
| >>Now you create a subset of that list using the startRow and maxRow,
| >>I'll leave that exercise up to the cunning of the reader. (subsetList
| >>= from startRow to startRow+maxRows, do listGetAt etc. etc. etc. etc.)
| >>
| >>Finally we can pull all we really need from the database using our
| >>subList;
| >>SELECT * FROM somewhere WHERE id IN (#sublist#)
| >>
| >>So what happened here? Well we hit the database once for the big-hit
| >>of 200 records or so, and then again (say 20 times) for our display of
| >>results for this page. But once we move onto the next page, we don't
| >>run that big assed query again, just the smaller 20 hit one.
| >>
| >>Of course we could cache the first query <CFQUERY NAME="bigassedQ"
| >>DATASOURCE="myData" CACHEWITHING="#CreateTimeSpan(0,6,0,0)#">, so
| >>rerunning it on each page didn't really give us a big database
| >>slowdown, but, here's the pay off...
| >>
| >>You can use the above method to run a number of queries the first time
| >>to enter the page, and append the results to the list. For example 3
| >>queries...
| >>
| >>SELECT id FROM table WHERE a=x AND b=y AND c=z (exact match)
| >> returns records 6 & 7
| >> add results to subList
| >>
| >>SELECT id FROM table WHERE a=x AND (b=y OR c=z)
| >> AND id NOT IN (#subList#) (more relaxed)
| >> returns records 2,3 & 12
| >> add results to subList
| >>
| >>SELECT id FROM table WHERE a=x OR b=y OR c=z
| >> AND id NOT IN (#subList#) (very relaxed)
| >> returns records 1,4,5 & 9
| >> add results to list
| >>
| >>I end up with the list subList = 6,7,2,3,12,1,4,5,9
| >>
| >>Notice the "AND id NOT IN (#subList#)", in the above case, any records
| >>found in the first query would also be found in the second one, unless
| >>we tell it not to find the records we already have IDs for.
| >>
| >>I can now use this list to base the rest of my search on.
| >>SELECT * FROM table WHERE id IN (#subList#)
| >>
| >>The records will be returned in the order that the IDs appear in the
| >>list, that way the closest matches are returned first going to loose
| >>matches at the end of the list.
| >>
| >>I suspect there are certain issues which may need looking at. The
| >>exact way you pass the large results list from page to page will
| >>depend on your situation, and total number of records you're dealer
| >>with. If you're using strings instead of ints for your key IDs,
| >>you'll need to address some single quote issues.
| >>
| >>I also suspect you can gain something from using an array instead of a
| >>list, as this will possibly speed up the creation of a sub list. But
| >>I haven't even looked into it.
| >>
| >>Hope this is of help to someone.
| >>
| >>Dan.
| >>
| >>
| >>
| >>This message is intended only for the use of the person(s) ("the
intended
| >>recipient(s)") to whom it is addressed.
| >>
| >>It may contain information which is privileged and confidential within
the
| >>meaning of the applicable law. If you are not the intended recipient,
| >>please contact the sender as soon as possible.The views expressed in
this
| >>communication may not necessarily be the views held by Live Information
| >>Systems Limited.
| >>
| >>
|
>>--------------------------------------------------------------------------
----
| >>Archives: http://www.mail-archive.com/[email protected]/
| >>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.mail-archive.com/[email protected]/
| >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.
|
|
| --------------------------------------------------------------------------
-
| Peter Theobald, Chief Technology Officer
| LiquidStreaming http://www.liquidstreaming.com
| [EMAIL PROTECTED]
| Phone 1.212.545.1232 Fax 1.212.679.8032
|
| --=====================_20677026==_.ALT
| Content-Type: text/html; charset="us-ascii"
|
| <html>
| <font size=3>On the mailing list I *CONSTANTLY* hear people mention
| facts that are not in the Cold Fusion documentation.<br>
| In this example, how did you know there is a limit of 100 cached queries?
| The Cold Fusion Language Reference doesn't say that in the description of
| CFQUERY.<br>
| <br>
| Where is everybody getting this information.<br>
| I must say that, although I love working in Cold Fusion, the Cold Fusion
| documentation is among the WORST language documentation I have ever had
| the misfortune of struggling with.<br>
| <br>
| Even the so-called Reference manual is little more than a brief
| description of each TAG.<br>
| Where is the definitive definition of exactly how each element
| behaves?<br>
| <br>
| How can they say <CFSCRIPT> is "like" Javascript, but not
| exactly?! Where is the rigorous description of every element?<br>
| When I write CFSCRIPT programs that are "like" Javascript, I
| find out by trial and error just how "like" it is and
| isn't.<br>
| <br>
| At 07:17 AM 8/4/00 -0700, paul smith wrote:<br>
| <br>
| <blockquote type=cite cite>I've done something similar, except I cache
| the big query for 5 minutes.<br>
| <br>
| In addition, I store the long list of IDs as a client variable (in a
| <br>
| database). The cache thus trades the need to create sublists for a
| <br>
| database read AND speeds up the paging queries (since the cache is in
| <br>
| memory). Works well, but on a busy site I'm concerned I'll run up
| against <br>
| the limit of 100 cached queries.<br>
| <br>
| I also wonder about the speed of lists, both the time it takes to create
| <br>
| them, and the resultant use of WHERE ID IN (#ID_List#), compared to using
| a <br>
| JOIN on a table that contains the IDs as a column.<br>
| <br>
| Anyone?<br>
| <br>
| best, paul<br>
| <br>
| At 10:14 AM 8/4/00 +0100, you wrote:<br>
| >Ok here's my take on the problem, and actually what I use.<br>
| ><br>
| >Notes, we are always reloading the the same page results.cfm (ie.
| next<br>
| >& prev links link right back to results.cfm), there are
| three<br>
| >variables which will get passed, startRow, maxRows &
| resultIDList, all<br>
| >of which I assume are undefined when I first enter the page.
| startRow<br>
| >& maxRows are passed via the URL, resultIDList is passed in some
| other<br>
| >fashion; client.resultIDList, or whatever takes your fancy
| (passing<br>
| >all three in a hidden form is quite nice).<br>
| ><br>
| >Enter page.<br>
| >if startRow & maxRows are undefined, give them some value.<br>
| >if resultIDList is undefined then...<br>
| > Do the QUERY, but return only the ID values:<br>
| > SELECT id FROM somewhere WHERE
| somethingComplex<br>
| > create resultIDList as an empty list, and then loop
| round the query<br>
| >populating the list<br>
| > resultIDList = ''<br>
| > <loop query="aboveQuery">resultIDList
| =<br>
| >listAppend(resultIDList,#id#)</loop><br>
| >endif<br>
| ><br>
| >What we have done here is checked to see if a list already existed,
| if<br>
| >it didn't we run the query to get all the records which match
| our<br>
| >search out, and only return the ID value.<br>
| >Next time we enter this page, the list will already have been
| defined,<br>
| >therefore the query will not run again (handy for those 200
| record<br>
| >returns)<br>
| ><br>
| >Now you create a subset of that list using the startRow and
| maxRow,<br>
| >I'll leave that exercise up to the cunning of the reader.
| (subsetList<br>
| >= from startRow to startRow+maxRows, do listGetAt etc. etc. etc.
| etc.)<br>
| ><br>
| >Finally we can pull all we really need from the database using
| our<br>
| >subList;<br>
| >SELECT * FROM somewhere WHERE id IN (#sublist#)<br>
| ><br>
| >So what happened here? Well we hit the database once for the
| big-hit<br>
| >of 200 records or so, and then again (say 20 times) for our display
| of<br>
| >results for this page. But once we move onto the next page, we
| don't<br>
| >run that big assed query again, just the smaller 20 hit one.<br>
| ><br>
| >Of course we could cache the first query <CFQUERY
| NAME="bigassedQ"<br>
| >DATASOURCE="myData"
| CACHEWITHING="#CreateTimeSpan(0,6,0,0)#">, so<br>
| >rerunning it on each page didn't really give us a big database<br>
| >slowdown, but, here's the pay off...<br>
| ><br>
| >You can use the above method to run a number of queries the first
| time<br>
| >to enter the page, and append the results to the list. For
| example 3<br>
| >queries...<br>
| ><br>
| >SELECT id FROM table WHERE a=x AND b=y AND c=z (exact
| match)<br>
| > returns records 6 & 7<br>
| > add results to subList<br>
| ><br>
| >SELECT id FROM table WHERE a=x AND (b=y OR c=z)<br>
| > AND id NOT IN
|
(#subList#)  
;
| (more relaxed)<br>
| > returns records 2,3 & 12<br>
| > add results to subList<br>
| ><br>
| >SELECT id FROM table WHERE a=x OR b=y OR c=z<br>
| > AND id NOT IN
|
(#subList#)  
;
| (very relaxed)<br>
| > returns records 1,4,5 & 9<br>
| > add results to list<br>
| ><br>
| >I end up with the list subList = 6,7,2,3,12,1,4,5,9<br>
| ><br>
| >Notice the "AND id NOT IN (#subList#)", in the above case,
| any records<br>
| >found in the first query would also be found in the second one,
| unless<br>
| >we tell it not to find the records we already have IDs for.<br>
| ><br>
| >I can now use this list to base the rest of my search on.<br>
| >SELECT * FROM table WHERE id IN (#subList#)<br>
| ><br>
| >The records will be returned in the order that the IDs appear in
| the<br>
| >list, that way the closest matches are returned first going to
| loose<br>
| >matches at the end of the list.<br>
| ><br>
| >I suspect there are certain issues which may need looking at.
| The<br>
| >exact way you pass the large results list from page to page
| will<br>
| >depend on your situation, and total number of records you're
| dealer<br>
| >with. If you're using strings instead of ints for your key
| IDs,<br>
| >you'll need to address some single quote issues.<br>
| ><br>
| >I also suspect you can gain something from using an array instead of
| a<br>
| >list, as this will possibly speed up the creation of a sub
| list. But<br>
| >I haven't even looked into it.<br>
| ><br>
| >Hope this is of help to someone.<br>
| ><br>
| >Dan.<br>
| ><br>
| ><br>
| ><br>
| >This message is intended only for the use of the person(s) ("the
| intended <br>
| >recipient(s)") to whom it is addressed.<br>
| ><br>
| >It may contain information which is privileged and confidential
| within the <br>
| >meaning of the applicable law. If you are not the intended recipient,
| <br>
| >please contact the sender as soon as possible.The views expressed in
| this <br>
| >communication may not necessarily be the views held by Live
| Information <br>
| >Systems Limited.<br>
| ><br>
| ><br>
|
>------------------------------------------------------------------------
------<br>
| >Archives:
| <a href="http://www.mail-archive.com/[email protected]/"
eudora="autourl">http://www.mail-archive.com/[email protected]/</a><
br>
| >To Unsubscribe visit <br>
| ><a
href="http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf
_talk"
eudora="autourl">http://www.houseoffusion.com/index.cfm?sidebar=lists&bo
dy=lists/cf_talk</a>
| or <br>
| >send a message to [EMAIL PROTECTED] with
| 'unsubscribe' in <br>
| >the body.<br>
| <br>
| --------------------------------------------------------------------------
----<br>
| Archives:
| <a href="http://www.mail-archive.com/[email protected]/"
eudora="autourl">http://www.mail-archive.com/[email protected]/</a><
br>
| To Unsubscribe visit
| <a
href="http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf
_talk"
eudora="autourl">http://www.houseoffusion.com/index.cfm?sidebar=lists&bo
dy=lists/cf_talk</a>
| or send a message to [EMAIL PROTECTED] with 'unsubscribe'
in the body. </font></blockquote><br>
|
| <font size=2><b><br>
| --------------------------------------------------------------------------
-<br>
| Peter Theobald, </b>Chief Technology Officer<br>
| </font><font size=3 color="#0000FF"><b>LiquidStreaming </b></font><a
href="http://www.liquidstreaming.com/" eudora="autourl"><font size=2
color="#0000FF"><u>http://www.liquidstreaming.com</a><br>
| </u></font><font size=2>[EMAIL PROTECTED]<br>
| <b>Phone</b> 1.212.545.1232 <b>Fax</b> 1.212.679.8032<br>
| </font></html>
|
| --=====================_20677026==_.ALT--
|
| --------------------------------------------------------------------------
----
| Archives: http://www.mail-archive.com/[email protected]/
| 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.mail-archive.com/[email protected]/
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.
Re: Paging query results question
AustralianAccommodation.com Pty. Ltd. Fri, 04 Aug 2000 12:58:03 -0700
- RE: Paging query results Anthony Geoghegan
- RE: Paging query results Peter Theobald
- RE: Paging query results Philip Arnold - ASP
- Re: Paging query result... David Green
- Re: Paging query re... Peter Theobald
- RE: Paging que... Daniel Kemp
- RE: Paging... paul smith
- OT: Select... Brandon Behrens
- Re: OT: Se... Jennifer
- RE: Paging... Peter Theobald
- Re: Paging... AustralianAccommodation.com Pty. Ltd.
- Re: Paging... Peter Theobald
- RE: Paging... Jeremy Allen
- RE: Paging... Peter Theobald
- RE: Paging... paul smith
- RE: Paging... Peter Theobald
- RE: Paging... Peter Theobald
- Problems w... Paul Sinclair
- RE: Proble... David Gassner
- RE: Paging query result... paul smith
- RE: Paging query results Scott Weikert
