Solved

Java/JDBC utilizing memory properly w/o thrashing

Posted on 2002-06-25
6
181 Views
Last Modified: 2010-03-31
I've been given a very simple task of writing a process that will transfer data from one server/datbase to another server/database.  Seems easy.  The hard part that I'm having to deal with is the amount of data that I'm having to transfer (anywhere from a million to 50 million records).  I'm trying to design my process properly so that it takes into account memory resources when running, but I'm still getting a large amount of thrashing.  So my question is, how can I take this simple task and make it run optimally.

Here's a snippet of what I got so far.
..............
//grab 1st connection
Properties props = new java.util.Properties();
props.put("user", userid);
props.put("password", password);
props.put("block size", "512");
conn = DriverManager.getConnection(url, props);

conn.setTransactionIsolation(conn.TRANSACTION_NONE);
Statement st = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
ResultSet rs = st.executeQuery("...")
...

//grab 2nd connection
Properties props = new java.util.Properties();
props.put("user", userid);
props.put("password", password);
props.put("block size", "512");
conn = DriverManager.getConnection(url, props);

//populate
conn.setAutoCommit(false);
PreparedStatement ps = conn.prepareStatement("...");
while (rs.next())
{
 ...
 ps.addBatch();
}
ps.executeBatch();
conn.commit();
conn.setAutoCommit(false);

//close connections and done!
0
Comment
Question by:nixj14
6 Comments
 
LVL 16

Expert Comment

by:heyhey_
ID: 7109823
generally speaking memory consumption depends on JDBC driver implementation and you can't do anything to reduce it (except specially crafted SQL statements)
0
 
LVL 9

Expert Comment

by:Venci75
ID: 7109916
are you using BLOB fields ?
0
 
LVL 1

Author Comment

by:nixj14
ID: 7110318
sql statements are too simple to optimize, and no blob fields.
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 1

Author Comment

by:nixj14
ID: 7113566
oh, and i have no control in the JDBC driver.
0
 

Accepted Solution

by:
udaykumar22 earned 50 total points
ID: 7116020
Why don't you select specific chunks of the table and then pass it along.(You can lock the database activity for that amount of time if you want to).
The chunks of table could be based on the data on the tables. You can then schedule this transfer.
If possible, use a thin JDBC driver.

Regards,
Uday.
0
 
LVL 1

Author Comment

by:nixj14
ID: 7124530
Basically, I was looking for JDBC specifics on what the block sizes should be.  When/if I should executeBatch after a number of inserts have been batched up, and what that specific number should be.  I'm looking for performance tuning options.  
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question