Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Java/JDBC utilizing memory properly w/o thrashing

Posted on 2002-06-25
6
Medium Priority
?
188 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

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

Accepted Solution

by:
udaykumar22 earned 150 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
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.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

877 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