Fastest Method for Mass Inserts

[PaulH *TMM*]

TheOriginalJman wrote:

> HELP, I've been using ColdFusion for years and keep running into the same wall
> on various projects, now the need is desperate. I need to insert between
> 60,000 and 100,000 records at a time into a database. CFMX7 with MySQL 5 as

not particular to mysql but doing it db to db is always better. having
no idea what mysql supports, see if this makes sense. if it isn't easily
accomplished natively (anything like sql server's DTS or ftp/bulk
insert?), dump the out as CSV or whatever mysql will swallow, copy that
between servers & use whatever myql's import tool is to re-import that
data. this is usually best done against a "transfer" table w/out any
indexes. the merging can be done after the data is in the transfer
table. does mysql have index types that ignore duplicates (ie when a
duplicate row is found during an insert, it's ignored & not inserted)?
if so, use those to do the merge. otherwise, does this version of mysql
support sub-queries? if so, use those to do the merge.

if none of this is possible, i suppose you might try the JDBC driver
instead of the ODBC one to see if that improves things & do the cfquery
transfer in small batches.

[TheOriginalJman]

I've tried the transfer server to server, but becasue of firewalls and other
concerns, I must use a queries only, and must allow CF to control the data
flow. The subquery Idea is interesting. I'm not sure of how to do that
though. The two tables are in different datasources making it a little more
difficult. Mysql does support suqueries, and I use them often, but have only
delt with tables in the same datasource.

The DB does support ignoring dupes, however, CF seems to through an error when
they occur, When I was using the raw dumps for transfering I had no problem
with dupes, but since I can only use CF, the dumps are not an option.

On the ODBC subject, I miss stated, I am using jdbc, as that is the only
connection to MYSQL 5 that CF 7 can use at the current time. Thanks so much
for these ideas. If you could give me an idea of using the subueries idea that
would welcomed indeed, especially if this can be done without using a <cfloop>
to loop through the query.

Thanks so much for the ideas so far.

-J

[PaulH *TMM*]

TheOriginalJman wrote:

> I've tried the transfer server to server, but becasue of firewalls and other
> concerns, I must use a queries only, and must allow CF to control the data
> flow. The subquery Idea is interesting. I'm not sure of how to do that

you should still use the CVS dump or whatever to get the data over to
the 2nd server, it will be quicker to manipulate it there. get the IDs
of existing rows in the 2nd server's table to use in getting the data
out of the 1st server. probably need to chunk this.

> though. The two tables are in different datasources making it a little more

shouldn't matter i think. does mysql support naming like
database.owner.table? can each server see the other?

> The DB does support ignoring dupes, however, CF seems to through an error when
> they occur, When I was using the raw dumps for transfering I had no problem

stick the code in a cftry block & ignore the duplicate errors.

> for these ideas. If you could give me an idea of using the subueries idea that
> would welcomed indeed, especially if this can be done without using a <cfloop>
> to loop through the query.

can mysql do insert/selects? does it know EXISTS?
INSERT secondTable (columnsList...)
SELECT columnsList...
FROM firstTable
WHERE someID NOT IN (SELECT someID FROM secondTable WHERE...)

or something like that.

[TheOriginalJman]

how woulc i code a select i sert where queryresult needs to be inserted into a table in a different datasource. it's the second datasource that trips me

[PaulH *TMM*]

TheOriginalJman wrote:

> how woulc i code a select i sert where queryresult needs to be
> inserted into a table in a different datasource. it's the second
> datasource that trips me

" does mysql support naming like database.owner.table? can each server
see the other? " and if the servers can communicate w/each other...does
mysql have anything like linked servers? does mysql have replication?
otherwise it's back to the CSV dump, query the 2nd server for existing
IDs, then use that info (valueList function) to filter the first table's
resultset to rows not in the 2nd table.

sorry i can't be more specific, i don't know mysql that well.

[TheOriginalJman]

as far as I know you can't specify datasource.tablein CF7 and the two MySQL
Servers could possibly see each other, and ther e is a replication feature in
MySQL, however the replications is master slave replication, and each slave
(which is where the data is being inserted into) can have only one master in my
situation I have many masters all needing to hav their data pulled and putinto
a single database. each server is scattered around the world, and each is
behid a firewall and I only have port 3306 available to me. I have looked at
utilities that would do the replication, but it casues major headaches in the
CF website app if CF doesn't maintain control of updates and such.

For these and many other reasns I am left with the option of pulling the data
one source at a time and then inserting it into the local datasource. I was
just hoping that there is a faster method of inserting the data into CF 7 then
using a <CFLOOP> to loop through all the records and insert them one at a time.

I see the benifits of

<cfquery name ="remote" datasource="remote"> select * from table1</cfquery>
<cfquery name ="local" datasource="local"> select * from table1</cfquery>
<cfquery name = "notexisting" dbtype="query"> select * from remote where
remote.keyfield not in (select keyfield from local)
<cfrloop> .... loop through notexisiting and insert records ... </cfloop>


any better ideas????

[PaulH *TMM*]

TheOriginalJman wrote:

> as far as I know you can't specify datasource.tablein CF7 and the two MySQL

missed the point, can you do it in mysql?

> utilities that would do the replication, but it casues major headaches in the
> CF website app if CF doesn't maintain control of updates and such.

i don't see how that can be. the db maintains the data, not cf.

> I see the benifits of

i don't. it will be slower than using native db functionality. sorry i
don't know mysql well enough to be specific but somebody does. perhaps
you can put this question to a mysql support list?

> any better ideas????

<cfquery name="existingData" datasource="local">
SELECT keyField
FROM table1
</cfquery>

<cfquery name="remote" datasource="remote">
SELECT *
FROM table1
WHERE keyField NOT IN (#valueList(existingData.keyField)#)
</cfquery>

[The ScareCrow]

One option would be to query the remote db and dump the result set into a CSV
Then use LOAD DATA INFILE on the db you want to import to
[url]http://dev.mysql.com/doc/refman/5.0/en/load-data.html[/url]

Ken