Ian,
I'd like to add that you may want to control the size of the files you dump
(probably by limiting the rowset you dump to each file), making sure you
don't create a file that can't be properly accessed by the OS.
_/ Thunder Jon Parley
_/ Software Engineer
_/_/_/ _/_/_/ ISSO - Software Mastering
_/ _/ _/_/_/
_/ _/ _/
i n v e n t [EMAIL PROTECTED]
-----Original Message-----
From: Ronald J Kimball [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 22, 2002 2:08 PM
To: Ian Harisay
Cc: [EMAIL PROTECTED]
Subject: Re: Transferring from Access to MySQL
On Mon, Apr 22, 2002 at 02:55:38PM -0600, Ian Harisay wrote:
> The other question is how often will you do this? Is it a one time shot?
> If so, do what is easiest to implement. If you know how do it using
> inserts, then do it. Don't debate it. The amount of time you might have
> saved will be long gone if you don't just act now. Once again, if this
> is a one time shot. If it isn't and you really need to speed up a
> nightly process.........well, then I'll just shut up.
Although this advice is good in general, it may not apply in the specific
case of loading large amounts of data into a database.
The amount of time you will save depends strongly on the amount of data,
the indexes that have to be updated, and the constraints that have to be
checked. For example, we recently had to fill a new table in Oracle with
approximately 30 million rows, based on data from other tables. I had
initially written a very naive PL/SQL approach that selected from existing
tables and inserted the new rows one at a time. This ran for a couple
days, generated a large amount of redo logs, and still hadn't finished.
Then we came up with a much better approach: dump the results of a select
to a flat file, and load the file in with SQL*Loader. This approach took a
matter of hours, without filling up the disk with redo logs.
So, my point is, where you have a really huge data set, optimization even
for a single run can be a big win.
Ronald
