*leaps to ColdFusions defense.....*
This is not a flame but, being that ColdFusion is pretty new,
you cant expect it to be defined as strictly as something like
ECMAScript(Javascript..) or C, or C++. These are languages that
have been reviewed and studied and improved for 20 years or so.
Allaire has done an EXCELLENT job at documenting and keeping
up to date with technology if you ask me.
They DO have a knowledge base which has darn near every little
snippet of information ive seen passed around on this list.
If not all of it. No its not in the language reference and
yes ive caught mistakes here and there, but I will live with
it because given all of its flaws you wont develop a high
quality application without something like ColdFusion or
Php.
Allaire realizes their documentation can leave you in want
of more information so they do maintain a active community
prescence and they do maintain their website (sometimes..<G>)
with all the most current information.
Anyhow dats my 2 cents...
Jeremy Allen
[Insert cool title here]
[EMAIL PROTECTED]
-----Original Message-----
From: Peter Theobald [mailto:[EMAIL PROTECTED]]
Sent: Friday, August 04, 2000 3:46 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
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/cf-talk@houseoffusion.com/
>>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/cf-talk@houseoffusion.com/
>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/cf-talk@houseoffusion.com/"
eudora="autourl">http://www.mail-archive.com/cf-talk@houseoffusion.com/</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/cf-talk@houseoffusion.com/"
eudora="autourl">http://www.mail-archive.com/cf-talk@houseoffusion.com/</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/cf-talk@houseoffusion.com/
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/cf-talk@houseoffusion.com/
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.
