If you are doing large number of inserts into the same table one of
the following syntax might work for you
Example 1 * see note below for limits.
( I know works for SQL server not sure about Oricle)
insert into footable ( bar1, bar2)
select '#bar1[1]#', '#bar2[1]'
union all
select '#bar1[2]#', '#bar2[2]'
union all
select '#bar1[3]#', '#bar2[3]'
.......
Example 2 ( I believe this is the ANSI way to write the above
statement but SQL6.5-2k does not support Oracle might)
insert into footable( bar1,bar2)
values('#bar1[1]#', '#bar2[1]'),
('#bar1[1]#', '#bar2[1]'),
('#bar1[1]#', '#bar2[1]'),
.....
The first example basically creates a derived table of all your
values and then has them inserted into the DB. I have used this to allow
large numbers of records to be inserted into the DB at once. Though there
are limits on how many records you can do like this at once in SQL Server
Note Example 1
There is a limit to the number of items this can do in
different versions of MS SQL Server
SQL 7 has a limit of 256 Union statement in a single derived query.
I get around this by doing batches of 250 at a time. Still huge speed
increase over doing individual insert called for each.
SQL 2K this limit varies a little depending on where you are using
the unions. But for insert this works for ~15000 items if I remember the
test results correctly. Not doing anything that large in any of our
software currently. As with most thing your mileage may vary but SQL 2k
does allow a much larger number of unions in most places.
As far as speed increase I have seen with this technique. From
doing large number of <cfquery> insert combos to one cfquery with multiple
inserts in it to one insert with multiple unions. CFquery looped over took
forever on large > 100 sets. Single cfquery with multiple insert statements
would process in our dev environment 200 or so a min (still way to slow).
Using example 1 above dropped 1500 inserts to under 10 seconds with out
indexing the table being inserted into. Adding an index improved the speed
even more. So I definitely recommend if Oracle will support something like
example 1 or 2 above that you try them and see how they work for you.
Daniel D.
-----Original Message-----
From: Jon Gunnip [mailto:[EMAIL PROTECTED]
Sent: Monday, May 10, 2004 11:34 AM
To: [EMAIL PROTECTED]
Subject: Re: [CFCDev] OT: Using JDBC instead of <cfquery> in CFC's
>>> [EMAIL PROTECTED] 5/10/2004 9:31:20 AM >>>
>> You could always put multiple SQL statements in a single cfquery.
I wish it were that simple. I don't believe that works for <cfquery>
with Oracle, as the semi-colon that would separate statements is an
illegal character.
I've heard it works with SQL Server.
Jon
----------------------------------------------------------
You are subscribed to cfcdev. To unsubscribe, send an email
to [EMAIL PROTECTED] with the words 'unsubscribe cfcdev'
in the message of the email.
CFCDev is run by CFCZone (www.cfczone.org) and supported
by Mindtool, Corporation (www.mindtool.com).
An archive of the CFCDev list is available at
www.mail-archive.com/[EMAIL PROTECTED]
----------------------------------------------------------
You are subscribed to cfcdev. To unsubscribe, send an email
to [EMAIL PROTECTED] with the words 'unsubscribe cfcdev'
in the message of the email.
CFCDev is run by CFCZone (www.cfczone.org) and supported
by Mindtool, Corporation (www.mindtool.com).
An archive of the CFCDev list is available at www.mail-archive.com/[EMAIL PROTECTED]
