Re: Advice on inserting multiple rows into MySQL

[Walt Mankowski <waltman@pobox.com>]

On Fri, Aug 08, 2003 at 01:26:52PM -0700, Wilson, Douglas wrote:
> 
> I might also mention that I did once modify a script which was
> inserting into an Oracle database one record at at time to
> instead write to a flat file and use Oracle's bulk loading
> facility, and the time went from many hours to a couple of
> minutes. I didn't have to contend with a header/detail
> table relationship though.

Up to now I've been writing the data out to two tab-delimited files
and using mysql's bulk loader to do the inserts.  It's been taking
about 15 minutes to create the files and about 3 minutes or so to load
them into mysql.  It took over an hour to do the inserts directly from
DBI.

The only real problem with doing it the first way is that I don't have
one unique column in the first table to access the records in the
second.  I've been been resetting the record_id's for each file (which
represents one year of data) and doing the joins by record_id and
year.  That works, but I'm thinking it's going to become a problem as
I load more data in.

The approach you recommended in your first email sounds like it might
be a good compromise.  I'll have to give it a try and see how that
works.

Thanks.

Walt

Attachment: pgpkoicTNFX4D.pgp
Description: PGP signature