Solved

Java/JDBC utilizing memory properly w/o thrashing

Posted on 2002-06-25
6
182 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
In this post we will learn different types of Android Layout and some basics of an Android App.
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…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Suggested Courses

752 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