Hi, Can you please let me know, I would like to retrieve 4000O records from a DB2 database. Using For Loop i retrieve from 5 different tables for 5 different columns and store it in an object and will need to be populated in arrayList of 40000 size. This will be itereated again and populted in the MS SQL Server database. Can you tell me what is the efficient ways of doing it. Currently, i am using executeQuery in a PreparedStatement with parameterised queries for each record, and the SELECT Statement in properties file. Now it is taking 2 hours for 1000 records.It is highly unimaginable to retrieve 40000 records. Thanks Gopal

1. You can try prefetching appropriate amount of data from DB2 using PreparedStatement.setPrefetchSize(int NoOfRows). This will reduce the number of round trips to your database. I believe that might be a major bottleneck in your scenario. You may have to come up with the appropriate number based on your tests. 2. Try updating the MYSQL database using batchUpdate. - Disable AutoCommit in your connection - Keep adding your SQL statements to the batch and invoke addBatch method - Execute executeBatch and check for any errors and the number of rows that are affected. - Invoke the commit after that. - This is at very high level. Hope you know to do it. 3. Not sure if this option can be done in your scenario. Why not have one thread keep populating the arraylist and another thread pull the data from this arrayList & populate the MYSQL database?. However, you need add appropriate error handling strategies. 4. Ensure that the network among the machines of client program and databases (MYSQL, DB2) are not becoming a bottleneck. ~Rajesh.B

Along with Rajeshs' comments, have you actually timed how long it takes to execute your select SQL? It may be the case that you need to look at tuning your SQL. Have you implemented a connection pool? If you are using a prepared statement, are you closing it after every call to executeQuery()? If you are, don't. Keep the prep'd statement open so that you can take advantage of db side compilation. What is the ping time from the server running the Java process to the DB2 server? A ping time of > 50ms really can impact the performance of something like this. You really want to reduce roundtrips so implement batching a Rajesh suggests. Would it be possible to run the process on the DB2 server? This would cut out the roundtrip journey and dramatically improve processing time. Have you explored any potential for multithreading? Can you post some of your code here so we can take a look at what approach you are taking. Rgds, Jay

Hey, why just populate arraylist??? Use the for loop you using to populate arraylist from db2...and in the same for loop insert in ms-sql database... Overhead of collection can be avoided... But transaction must be handled carefully here.... Sanket Raut

40,000 records to be populated to the Middle tier? I strongly believe, you should revisit your design aspect. I can imagine only one scenario in real life applications, wehre these manu huge data is needed in the middle tier. Do you want them as a search result and make them available to the UI? Otherwise, you can always, use a where clause predicate with some bind variables to restrict the no of result records. If its for the search result to be diaplyed as pagination to the users(i.e prev, 11->20, Next), then I should provide you a link to understand how this can be achieved(Atleast in Oracle). The same design can be implemented in any other database. http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:76812348057

Also, one more thing, if you are simply interseted in migrating data from DB2 to MSSQL, the I dont think going through Java would help you. You should stringly consider some migration utilities and should acomplish the migration in the DB server layer. One sample example is : Websphere Information Integegrator. It allows you to push/pull data through normal SQL from and to DB2.