Background: I'm working with Java in Netbeans.
I'm reading rows from a CSV file and inserting them into a SQLite database. Very basic stuff I would think. From examples found at this site and elsewhere I've constructed the following code. It works but I'm surprised to see how slow it is. It takes 3.95 seconds to load 46 lines into the db! Using the Netbeans Profiler I can see it's the autoCommit() that's taking the bulk of the time, 3.7 seconds.
What am I doing wrong? Or, is this just the penalty for using SQLite instead of MySQL?
public static void LoadJCNAClassesTable(Connection aConn, String strPath) throws SQLException{
String [] nextLine;
String strDivision;
String strClass;
String strNotes;
String strDescription;
Statement stat = aConn.createStatement();
stat.executeUpdate("drop table if exists JCNAClasses;");
String q = "create table JCNAClasses ('ID' INTEGER PRIMARY KEY AUTOINCREMENT, 'division' TEXT NOT NULL, 'class' TEXT NOT NULL UNIQUE, 'description' TEXT NOT NULL, 'note' TEXT NOT NULL, 'node' INTEGER NOT NULL);";
stat.executeUpdate(q);
CSVReader reader;
int iLine;
String JCNAClassesCSV = strPath + "\\JCNAClassesCsv.txt" ;
try {
reader = new CSVReader(new FileReader(JCNAClassesCSV ));
iLine = 0;
while ((nextLine = reader.readNext()) != null) {
// nextLine[] is an array of values from the line
// System.out.println(nextLine[0] + nextLine[1] );
if (iLine > 0){
strDivision = nextLine[0];
strClass = nextLine[1];
strDescription= nextLine[2];
strNotes= nextLine[3];
PreparedStatement prep = aConn.prepareStatement( "insert into JCNAClasses ('division', 'class', 'description', 'note', 'node') values ( ?, ?, ?, ?, ?);");
prep.setString(1, strDivision); // note that the comma seems not to be a problem
prep.setString(2,strClass);
prep.setString(3,strDescription);
prep.setString(4,strNotes);
prep.setInt(5,iLine);
prep.addBatch();
aConn.setAutoCommit(false);
prep.executeBatch();
aConn.setAutoCommit(true);
}
iLine++;
}
} catch (FileNotFoundException ex) {
Logger.getLogger(Entries.class.getName()).log(Level.SEVERE, null, ex);
}
catch (IOException ex) {
Logger.getLogger(Entries.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
Logger.getLogger(LoadSQLiteConcoursDatabase.class.getName()).log(Level.SEVERE, null, ex);
}
}
public static void LoadConcoursEntriesTable(Connection aConn, String strPath) throws SQLException{
String [] nextLine;
String strEntryName;
String strClass;
Integer intYear;
String strDescription;
String strOwnerFirst;
String strOwnerLast;
Integer intJCNA;
String strColor;
String strPlate;
Integer intNode;
Long intPersonnel_id;
Long intJaguar_id;
ResultSet generatedKeys;
String strStatus;
int intPersonnelNode;
ResultSet r;
CSVReader reader;
String q;
int iLine;
PreparedStatement prep;
ResultSet rs;
Statement stat = aConn.createStatement();
//
// Concourse Personnel Table: Owners, Judges, & Interested parties
//
stat.executeUpdate("drop table if exists ConcoursPersonnel");
// status: Owner = "O"; "J" = Judge; "OJ" = Owner & Judge; "IP" = Interested party, e.g., spouse, restorer
q = "create table ConcoursPersonnel ('personnel_id' INTEGER PRIMARY KEY AUTOINCREMENT , 'ownerfirst' TEXT NOT NULL, 'ownerlast' TEXT NOT NULL, 'jcna' INTEGER NOT NULL UNIQUE ON CONFLICT IGNORE, 'status' TEXT NOT NULL, 'node' INTEGER NOT NULL UNIQUE)";
stat.executeUpdate(q);
//
// Concours Jaguar Table
//
stat.executeUpdate("drop table if exists ConcoursJaguars");
q = "create table ConcoursJaguars ('jaguar_id' INTEGER PRIMARY KEY AUTOINCREMENT , 'class' TEXT NOT NULL, 'year' TEXT NOT NULL, 'description' TEXT NOT NULL, 'Color' TEXT, 'plate' TEXT, 'node' INTEGER NOT NULL UNIQUE)";
stat.executeUpdate(q);
//
// Entry Table ( a Relationship or "link" between Personnel & Jaguars
//
stat.executeUpdate("drop table if exists ConcoursEntries");
q = "create table ConcoursEntries (entry_name TEXT NOT NULL, personnel_id INTEGER NOT NULL, jaguar_id INTEGER NOT NULL, UNIQUE (personnel_id, jaguar_id), FOREIGN KEY (personnel_id) REFERENCES ConcoursPersonnel (Personnel_ID), FOREIGN KEY (jaguar_id) REFERENCES ConcoursPersonnel (jaguar_id))";
stat.executeUpdate(q);
String EntriesCSV = strPath + "\\EntriesCsv.txt" ;
strStatus = "O"; // not in the CSV data so set to Owner
try {
reader = new CSVReader(new FileReader(EntriesCSV ));
iLine = 0;
while ((nextLine = reader.readNext()) != null) {
// nextLine[] is an array of values from the line
// System.out.println(nextLine[0] + nextLine[1] );
if (iLine > 0){
strEntryName = nextLine[0];
strClass = nextLine[1];
intYear= Integer.parseInt(nextLine[2]);
strDescription= nextLine[3];
strOwnerFirst= nextLine[4];
strOwnerLast= nextLine[5];
intJCNA = Integer.parseInt(nextLine[6]) ;
strColor= nextLine[7];
strPlate= nextLine[8];
intNode= Integer.parseInt(nextLine[9]); // Since Jaguars are 1-to-1 with Entries this is used as Node number for both. However, it can't be used for Personnel Node
//
// Load Owners into Personnel Table
//
Statement s = aConn.createStatement();
r = s.executeQuery("SELECT COUNT(*) AS rowcount FROM ConcoursPersonnel");
r.next();
intPersonnelNode = r.getInt("rowcount") +1 ; // Assignes Personnel node numbers as a continuous sequence
prep = aConn.prepareStatement( "insert into ConcoursPersonnel ('ownerfirst', 'ownerlast', 'jcna', 'status', 'node' ) values ( ?, ?, ?, ?, ?);");
//prep.setString(1, strEntryName); // note that the comma seems not to be a problem
prep.setString(1,strOwnerFirst);
prep.setString(2,strOwnerLast);
prep.setInt(3,intJCNA);
prep.setString(4,strStatus);
prep.setInt(5,intPersonnelNode);
prep.addBatch();
aConn.setAutoCommit(false); // starts transaction
prep.executeBatch();
aConn.setAutoCommit(true); // ends transaction
aConn.setAutoCommit(false); // starts transaction
stat = aConn.createStatement();
generatedKeys = stat.executeQuery("SELECT last_insert_rowid()");
intPersonnel_id = 0L; // won't be used
if (generatedKeys.next()) {
intPersonnel_id = generatedKeys.getLong(1);
}
else{
System.out.println("No Personnel ID found in LoadConcoursEntriesTable");
System.exit(-1);
}
aConn.setAutoCommit(true); // Commits transaction.
//
// Load Entry cars into the ConcoursJaguars table
//
prep = aConn.prepareStatement( "insert into ConcoursJaguars ('class', 'year', 'description', 'color', 'plate', 'node' ) values ( ?, ?, ?, ?, ?, ?);");
prep.setString(1,strClass);
prep.setInt(2,intYear);
prep.setString(3,strDescription);
prep.setString(4,strColor);
prep.setString(5,strPlate);
prep.setInt(6,intNode); //
prep.addBatch();
aConn.setAutoCommit(false);
prep.executeBatch();
aConn.setAutoCommit(true);
q = "select jaguar_id from ConcoursJaguars where node == " + intNode + ";";
rs = stat.executeQuery(q);
intJaguar_id = rs.getLong("jaguar_id");
prep = aConn.prepareStatement( "insert into ConcoursEntries (entry_name, personnel_id, jaguar_id) values ( ?, ?, ?);");
//prep.setString(1, strEntryName); // note that the comma seems not to be a problem
prep.setString(1,strEntryName);
prep.setLong(2,intPersonnel_id);
prep.setLong(3,intJaguar_id);
prep.addBatch();
aConn.setAutoCommit(false);
prep.executeBatch();
aConn.setAutoCommit(true);
}
iLine++;
}
} catch (FileNotFoundException ex) {
Logger.getLogger(Entries.class.getName()).log(Level.SEVERE, null, ex);
}
catch (IOException ex) {
Logger.getLogger(Entries.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
Logger.getLogger(LoadSQLiteConcoursDatabase.class.getName()).log(Level.SEVERE, null, ex);
}
}
