Hi,
I have a performance issue with SQLitejdbc with Java on Windows Mobile 6.5
running on a MC9190-G mobile computer. I am using sqlitejdbc-v056.jar with the
NSIcom CrE-ME 4.12 JVM.
Performance of the insert is very low. I am getting about 1000 records added
a minute. Any ideas the on how to improve the performance would be appreciated.
Code overview:
1. Query an iSeries database with the JTOpen package to pull 56,000
records. This is fairly quick, less than 10 seconds to complete.
2. For each record returned, create an insert batch statement.
3. When 1000 records are added to the batch, execute the batch update and
commit the transaction (autocommit is disabled)
4. Two pragma statements are also used when the connection is created.
a. PRAGMA journal_mode=OFF;
b. PRAGMA synchronous = OFF;
The key sections of code:
/*
* PopulateItems.java
*
* Inserts items from the iSeries ERP into the PDA database item file
*
*/
package com.probuild.pda;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
*
*/
public class PopulateItems {
private Connection iSeriesConn;
private Connection sqliteConn;
public final int DB_COMMIT_COUNT = 5000;
/**
* Creates a new instance of PopulateItems
*/
public PopulateItems() {
// Establish connections to iSeries and SQLite DBs
try {
Class.forName( "com.ibm.as400.access.AS400JDBCDriver" );
String url
="jdbc:as400://as400.dxlbr.com;S10036FD;naming=sql;errors=full;";
iSeriesConn = DriverManager.getConnection( url, "SATCP", "SATCP" );
Class.forName( "org.sqlite.JDBC" );
sqliteConn = DriverManager.getConnection("jdbc:sqlite:\\pda.db");
Statement statement = sqliteConn.createStatement();
// turn journaling off
String sql = "PRAGMA journal_mode=OFF;";
statement.execute(sql);
// turn journaling off
sql = "PRAGMA synchronous = OFF;";
statement.execute(sql);
statement.close();
sqliteConn.setAutoCommit(false);
}
catch (Exception e) {
System.out.println ( e.getMessage() );
}
}
public void closeConnections ( ) {
try {
iSeriesConn.close();
sqliteConn.close();
}
catch ( Exception e ) {
System.out.println ( e.getMessage() );
}
}
public boolean buildItemTable (String locCode) {
Statement iStmt = null;
PreparedStatement pStmt = null;
ResultSet iResult = null;
boolean success = false;
System.out.println( "Building Item Table..." );
try {
pStmt = sqliteConn.prepareStatement(
"INSERT INTO item VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ? )" );
// SKU, UPC, Alt Item, Item Desc, List Unit Of Measure, Dept Code,
Mfg Item#
// Vnd Item#, Min Quantity, Max Quantity, Net Item (Y/N), RISBCD (P
or C)
String sql =
" SELECT RISKU#, RIUPC#, RIAITM, RIDESC, RILSTU, RIDEPT,
RIMNF#, " +
" RIVITM, RIMINQ, RIMAXQ, RINETI, RISBCD" +
" FROM DRMS.DRITEMFL " +
" WHERE RILOC# = " + locCode + " AND RIRLC# = 0 AND RISKU# >
50000 " +
" ORDER BY RISKU#";
iStmt = iSeriesConn.createStatement();
iResult = iStmt.executeQuery( sql );
System.out.println( "iSeries select form dritemfl complete: ");
int count = 0;
while ( iResult.next ( ) ) {
int sku = iResult.getInt( "RISKU#" );
long upc = iResult.getLong( "RIUPC#" );
//String sku = iResult.getString( "RISKU#" );
//String upc = iResult.getString( "RIUPC#" );
String alt = iResult.getString( "RIAITM" );
String desc = iResult.getString( "RIDESC" );
String uom = iResult.getString( "RILSTU" );
String dept = iResult.getString( "RIDEPT" );
String mnf = iResult.getString( "RIMNF#" );
String vendorItem = iResult.getString( "RIVITM" );
int minQ = iResult.getInt( "RIMINQ" );
int maxQ = iResult.getInt( "RIMAXQ" );
String netItem = iResult.getString( "RINETI" );
String subItemCode = iResult.getString( "RISBCD" );
pStmt.setInt( 1, sku );
pStmt.setLong( 2, upc );
pStmt.setString(3, alt.trim() );
pStmt.setString( 4, desc.replace( '\'', ' ').trim() );
pStmt.setString( 5, uom );
pStmt.setString( 6, dept );
pStmt.setString( 7, mnf.replace( '\'', ' ' ).trim() );
pStmt.setString( 8, ckDigitUPCE ( Integer.toString( sku ) ) );
pStmt.setString( 9, vendorItem );
pStmt.setInt( 10, minQ );
pStmt.setInt( 11, maxQ );
pStmt.setString( 12, netItem );
pStmt.setString( 13, subItemCode );
pStmt.addBatch();
count++;
if ( count % 1000 == 0 ) {
System.out.println ( " 1000 records added to item table! ("
+ count + ")" );
}
if ( count % DB_COMMIT_COUNT == 0 ) {
pStmt.executeBatch();
System.out.println ( DB_COMMIT_COUNT + " records added to
item table! (" + count + ")" );
}
}
if ( count % DB_COMMIT_COUNT != 0 ) {
pStmt.executeBatch();
}
System.out.println ( count + " records added to item table!");
}
catch ( SQLException e ) {
System.out.println ( e.getMessage() );
}
catch (Exception e ) {
System.out.println ( e.getMessage() );
e.printStackTrace();
}
finally {
try {
pStmt.close();
iResult.close();
iStmt.close();
}
catch (Exception e) {
System.out.println("Close connection failed: " +
e.getMessage());
}
}
return ( success );
}
}
________________________________
CONFIDENTIALITY NOTICE: This email may contain confidential and privileged
material for the sole use of the intended recipient(s). Any review or use by
others is strictly prohibited. Any distribution or disclosure by or to others
is strictly prohibited. If you have received this communication in error,
please notify the sender immediately by e-mail and delete the message and any
file attachments from your computer.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
