i'd go with the IN everytime. it wouldn't even occur to me to try the
looped method. I expect that the efficiency of an IN clause degrades
as the size of the list increases but I've neever tested.
why don't you run some tests and see which perfos better in your
situation
-Chris
On 22 Feb 2010, at 21:07, Matt C <[email protected]> wrote:
Hello,
I'm reading consistently that the "IN" statement is very inefficient
in SQL. In some prior Java code, I instead tried the following: open
a SQL connection, run several selects (average about 100 or so)
compiling a string of results, then close the connection, and this
process ran surprisingly fast. Now I'm converting said code to
ColdFusion pages (using OpenBD of course). With CF I don't manually
open and close connections anymore, so I figured it would be more
efficient to just use an "IN" statement rather than run 100
<cfquery>'s. However, I just read something in a prior post on this
mailing list that seemed to indicate using a <cftransaction> tag
around a block of queries maintains a persistent connection. So would
it be more efficient to use that?
<cftransaction ...>
<cfloop>
<cfquery>
simple select
</cfquery>
</cfloop>
</cftransaction>
versus
<cfquery>
select with "IN"
</cfquery>
--
Open BlueDragon Public Mailing List
http://www.openbluedragon.org/ http://twitter.com/OpenBlueDragon
mailing list - http://groups.google.com/group/openbd?hl=en
!! save a network - please trim replies before posting !!
--
Open BlueDragon Public Mailing List
http://www.openbluedragon.org/ http://twitter.com/OpenBlueDragon
mailing list - http://groups.google.com/group/openbd?hl=en
!! save a network - please trim replies before posting !!
