On Thu, Sep 5, 2013 at 7:26 PM, Bullock, Tony <tony.bull...@probuild.com>wrote:
>
> 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.
>
On a workstation, with PRAGMA synchronous=OFF, you should be getting about
300,000 rows per *second*. Performance on your mobile computer will be
less, of course, but it shouldn't be 18,000 times less.
Do you have any profiling tools available to see what it taking so long?
>
> 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
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
