RE: [KCFusion] Looping Over A Large Cached Query To Test Against Data
(BrainLock=TRUE) was absolutely the case I totally missed the cfbreak. Caching 13,000 rows? Wow... are you sure that's more efficient than a query with an index on my_field_to_watch? (or whatever it's really called) In this case, the query is over a matrix that attempts to match a total of 5 fields to pick up a code. Indexing does help, but had better than an 800% speed increase by caching. Yes, it is server memory---but I can spare it for this purpose (many hits per day) Running a query for each possible test was also way too slow... In the future, if you can, please use real snippets. If someone learns something from your snippet, that's the price you pay to learn the answer from the group ;-) I absolutely will do that when I can, just some sensitive code at times makes it so I cannot. --Daryl Thanks!! Doug Teetzen Bushnell Performance Optics - Original Message - From: Doug Teetzen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 09, 2001 5:45 PM Subject: [KCFusion] Looping Over A Large Cached Query To Test Against Data Here is my issue, I am caching a 13000 row query to test data against as part of a cfloop My result never seems to get a hit and set the new variable, even though I have validated the data. I know I am missing something, seems so simple?!?!? (BrainLock=TRUE) Sample: cfquery name=my_query datasource=my_dsn dbtype=ODBC cachedwithin=#CreateTimeSpan(0, 3, 0, 0)# Select * From my_file /cfquery cfoutput cfloop query=my_query#my_query.my_field#BR THIS IS JUST MY OUTPUT TO WATCH THE LOOP.. cfif variable_to_watch eq #my_query.my_field_to_watch# cfset another_variable = #my_query.field_from_query# /cfif /cfloop /cfoutput cfoutput#another_variable# /cfoutput Any ideas on what I might be missing would be appreciated!! Thanks, Doug Teetzen Bushnell Performance Optics __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED]
[KCFusion] Looping Over A Large Cached Query To Test Against Data
Here is my issue, I am caching a 13000 row query to test data against as part of a cfloop My result never seems to get a hit and set the new variable, even though I have validated the data. I know I am missing something, seems so simple?!?!? (BrainLock=TRUE) Sample: cfquery name=my_query datasource=my_dsn dbtype=ODBC cachedwithin=#CreateTimeSpan(0, 3, 0, 0)# Select * From my_file /cfquery cfoutput cfloop query=my_query#my_query.my_field#BR THIS IS JUST MY OUTPUT TO WATCH THE LOOP.. cfif variable_to_watch eq #my_query.my_field_to_watch# cfset another_variable = #my_query.field_from_query# /cfif /cfloop /cfoutput cfoutput#another_variable# /cfoutput Any ideas on what I might be missing would be appreciated!! Thanks, Doug Teetzen Bushnell Performance Optics __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED]
Re: [KCFusion] Looping Over A Large Cached Query To Test Against Data
Caching 13,000 rows? Wow... are you sure that's more efficient than a query with an index on my_field_to_watch? (or whatever it's really called) Anyway... most string inequalities of this sort are due to spaces being at the end of the data from the query (e.g., if it's a CHAR, not VARCHAR, field), so try cfif trim(variable_to_watch) eq trim(my_query.my_field_to_watch) for extra credit, do the trim on variable_to_watch before entering the loop. One other thing: if the variable names in the /actual/ code match, then you must specify variable_to_watch as variables.variable_to_watch, or you will be comparing the value in the current row to the value in the current row, and will always wind up with the last value from the table. So: cfset variable_to_watch = trim(variable_to_watch) cfloop query=my_query cfoutput#my_query.my_field#BR /cfoutput cfif variables.variable_to_watch eq my_query.my_field_to_watch cfset another_variable = my_query.field_from_query cfbreak !--- leave the loop as soon as we find the value --- /cfif /cfloop In the future, if you can, please use real snippets. If someone learns something from your snippet, that's the price you pay to learn the answer from the group ;-) --Daryl - Original Message - From: Doug Teetzen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 09, 2001 5:45 PM Subject: [KCFusion] Looping Over A Large Cached Query To Test Against Data Here is my issue, I am caching a 13000 row query to test data against as part of a cfloop My result never seems to get a hit and set the new variable, even though I have validated the data. I know I am missing something, seems so simple?!?!? (BrainLock=TRUE) Sample: cfquery name=my_query datasource=my_dsn dbtype=ODBC cachedwithin=#CreateTimeSpan(0, 3, 0, 0)# Select * From my_file /cfquery cfoutput cfloop query=my_query#my_query.my_field#BR THIS IS JUST MY OUTPUT TO WATCH THE LOOP.. cfif variable_to_watch eq #my_query.my_field_to_watch# cfset another_variable = #my_query.field_from_query# /cfif /cfloop /cfoutput cfoutput#another_variable# /cfoutput Any ideas on what I might be missing would be appreciated!! Thanks, Doug Teetzen Bushnell Performance Optics __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED]