--=====================_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&body=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&body=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.
