Richard, Thanks for the reply. I will look into the profiling tools. The only diagnostic I have is the CPU utilization of the java process which is very high (90%) for the database update.
-Tony -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, September 05, 2013 4:51 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQL insert performance on Windows Mobile 6.5 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 ________________________________ 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
