One reason might be how the DB optmizes the query. If memory serves me
correctly, the keyword IN implies a subselect. Depending on your DB and
version, it may not sent to the server in the most efficient way.
As a test, try this code
SELECT *
FROM Products
WHERE PartNumber = 6956122
OR PartNumber = 6820898
OR PartNumber = 6802862
If that runs faster (and I suspect it will), try creating your WHERE clause
using a CF loop and substiture it into your query later. Example code,
assuming that your getting the list of part numbers from a query called
"getpartnumbers":
<CFSET where_clause="">
<CFOUTPUT query="getpartnumbers">
<cfif rowcount = 1>
<CFSET where_clause="where PartNumber = #getpartnumbers#">
<cfelse>
<CFSET where_clause=where_clause + " OR PartNumber = #getpartnumbers#">
</cfif>
</CFOUTPUT>
<CFQuery name="getprodleft">
SELECT *
FROM Products
#where_clause#
<CFQUERY>
-----Original Message-----
From: Tracy Bost [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 29, 2001 5:06 PM
To: CF-Talk
Subject: Why the long processing time on queries ?
I know this isn't SQL list but can someone please tell me why it could be
taking ColdFusion Server soooooo long to
process these two queries ?? ... Other queries to the same database server
seem to be processing just fine. But these two queries together are taking
almost 20 seconds for the CF server to process them
getprodleft (Records=3, Time=8612ms)
SQL =
SELECT *
FROM Products
WHERE PartNumber IN (6956122 ,6820898,6802862)
getprodright (Records=3, Time=8793ms)
SQL =
SELECT *
FROM Products
WHERE PartNumber IN (6820088 ,6390009,6849111)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
