• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC Performance Win vs. AS/400

 
Michael Rosenthal
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Within an application I am working on I am seeing significant performance differences between Windows and AS/400. Running code locally on my machine to connect to a database and do 2500 inserts in batch mode takes 2 or 3 seconds. Running the same code on the AS/400 is taking upwards of 20 seconds. Does anyone have any performance tips to get the AS/400 machine's times closer to Windows?
The code is:
private static int NUM = 2500;

public static void main(String[] args)
{
Connection conn = null;
String sql = "INSERT INTO COFFEES VALUES (?, ?, ?, ?, ?)";
int counter = 0;

double param3 = 7.99;
int param4 = 0;
int param5 = 0;

try
{
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
conn = DriverManager.getConnection(url, id, pwd);
PreparedStatement ps = conn.prepareStatement(sql);

conn.setAutoCommit(false);

for(counter = 0; counter<NUM; counter++)
{
ps.setString(1, "Columbian");
ps.setInt(2, counter);
ps.setDouble(3, param3);
ps.setInt(4, param4);
ps.setInt(5, param5);

ps.addBatch();
}
ps.executeBatch();
conn.commit();

ps.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Michael,
Is anything else running on the AS/400 machine that uses the database? If so, you may have transaction locking issues.
You also may want to try using smaller batches. For example, 25 batches of 100. It's possible that the one large batch uses so much memory/bandwidth/... that it is paging. I saw this effect when I was trying to use batches with prepared statements. After a while it got so large that it took longer per unit.
 
Michael Rosenthal
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Nothing else on the box is hitting the database. There is another application that is hitting a different db2 instance. I am modifying the code to chunk the insert statements into blocks of 100. I will let you know how that goes.
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1. there are AS/400 optimizers during compilation. In fact, you can compile it right down to AS/400 machine code( I believe the AS/400 compile command is jvapgm 40 where 40 represents the highest level of compilation ( 10 is lowest/most portable ).
2. hardware. If you're running on a kick ass stallion of a machine and your AS/400 is an old pig, then this could account for such differnces.
3. different JRE. Make sure your AS/400 is running the JRE as your PC ( 1.4 ) and it is specified in the AS/400 properties file.
4. different JDBC drivers. Make sure your AS/400 is using the latest JDBC drivers and not pointing to some ancient driver that came with it in 1995!
Lastly, when you timed it, did you just take the time it took to create connection, insert and finish or did you include the time to load the JVM as well? I noticed it is significantly slower to start up on the AS/400 than on windows.
Jamie
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic