Re: Needed: An Efficient, reliable and smart way to copy data from one DB to another
- From: "Robert Klemme" <bob.news@xxxxxxx>
- Date: Wed, 23 Nov 2005 13:53:50 +0100
Ted Byers wrote:
> Ok, I have a prototype running, or I should say crawling, to copy
> data from an MS Access database over to an MySQL database. I'd
> already set up the tables and indeces, and was using a Java program
> to select all of the contents from each table in the Access DB and
> insert each row from the resulset into the MySQL. The largest table
> has about 7700 rows and a couple dozen columns, It only takes a
> couple minutes to get the data from the Access DB, but it seems to
> take hours to insert it into the MySQL, undoubtedly because I've so
> far used the simplest approach: i.e. create a prepared statement, and
> iterate through the resultset, populating the parameters for the
> prepared statement and then executing it before proceeding to the
> next.
>
> I need a way to copy the data over that is much faster. One options
> I can see, but which appears to preclude using prepared statements,
> is to manually parse the resultset obtained from Access to construct
> a really long SQL statement (i.e. with each row consisting of comma
> separated values, enclosed in parantheses, and separated from the
> next row by a comma). There is a question of how many rows ought to
> be inserted in one SQL statement. A second option suggested itself
> when I noticed that prepared statements have an addBatch member
> function. It wasn't clear how to use it, though, since it doesn't
> appear to take any parameters, and yet the description says it adds
> parameter values to a batch of SQL statements.
Just set the parameters as usual then do addBatch() instead of execute()
and call execute() only every n records.
> I do not know if either of these options I'm considering would improve
> things. Are there any other options I ought to consider? I need to
> get this done since I will need to deploy my application on a linux
> box.
I'd start with using batch mode of the prepared statement. That's the
simplest change to your code, should improve things and is generally a
good idea for such an app.
Apart from that you probably first have to determine where time is spent.
Could be your network or a slow machine with the MySQL db on etc.
Kind regards
robert
.
- Follow-Ups:
- References:
- Prev by Date: Re: MySQL connection problem
- Next by Date: Re: Needed: An Efficient, reliable and smart way to copy data from one DB to another
- Previous by thread: Needed: An Efficient, reliable and smart way to copy data from one DB to another
- Next by thread: Re: Needed: An Efficient, reliable and smart way to copy data from one DB to another
- Index(es):
Relevant Pages
|
|
