Most of you don't use redbrick, so you might not care about
that part, but some of you do use oracle, so you might care about
these numbers. I rarely use mssql, db2 or sybase, and never use
mysql, access, postgesql, or other dbs you guys might use, so I
didn't test those db's.
The reason I mention redbrick is to emphasize the point that
this is db specific.
Answer - Query in Loop is faster in redbrick but Loop in
Query is faster in oracle.
I just ran the following code a few times. In all cases, the
Query in Loop took between 11,000 and 12,000 milliseconds. The Loop
in Query code took between 500 and 600 milliseconds when it ran
after the Query in Loop, and between 200 and 300 milliseconds when
it ran first.
q1 had 4901 records.
Code
<cfquery name="q1" datasource="dw">
select residence_code r, country c
from location
</cfquery>
<cfset t3 = gettickcount()>
<cfquery name="z" datasource="burns">
insert into dan_test
<cfloop query="q1">
select #r#, '#c#'
from dual
<cfif currentrow lt recordcount>union</cfif>
</cfloop>
</cfquery>
<cfset t4 = gettickcount()>
<cfquery name="w" datasource="burns">
delete from dan_test
</cfquery>
<cfset t1 = gettickcount()>
<cfloop query="q1">
<cfquery name="x" datasource="burns">
insert into dan_test values (#r#, '#c#')
</cfquery>
</cfloop>
<cfset t2 = gettickcount()>
<cfquery name="y" datasource="burns">
delete from dan_test
</cfquery>
<cfdump var="query in loop #t2 - t1# ms loop in query #t4
- t3# ms">
