I have a program that trims items from a SQLite database based on a timestamp field. It seems when I run it on a large existing database that it takes quite a very long time and hogs up the resources on my already very low end embedded machine.
Can anyone suggest any ideas on how to improve my code to get it to run better/faster?
Call from main program:
query = SQLiteInterface.getInstance().delete(entry, "SampleInfo_source_timestamp < '" + timestamp + "'");
dbCall(query);
SQLiteInterface delete method:
/**
* This creates a SQLite formatted 'delete' statement that allows for where clauses
*
* @param table - The table that the items are being selected from
* @param filters - The list of where clauses
* @return - A SQLite formatted delete query
*/
public String delete(String table, String filters)
{
if (filters == null)
{
return "delete from " + table;
} else
{
return "delete from " + table + " where " + filters;
}
}
Code that actually sends the DB Command using JDBC
/**
*
*
* @param query - A SQLite formatted DB query
*/
public void dbCall(String query)
{
// Good practice to create a new statement and result set instead of reusing
Statement stmt = null;
Connection conn = null;
ResultSet rs = null;
try
{
// Fetch the query and the request out of the QueryRequest object
conn = cpds.getConnection();
conn.setAutoCommit(false);
// Make sure the query request isn't empty, if it is, there is no point in sending it to the DB
if (!query.isEmpty())
{
// Initialize the new statement
stmt = conn.createStatement();
stmt.executeUpdate(query);
}
conn.commit();
}
catch (SQLException e)
{
if (e.getMessage().contains("no such column"))
{
// This table is not one that needs to be cleaned up.
}
else
{
errorLog.error("", e);
// Table busy, try again.
dbCall(query);
}
}
catch (Exception e2)
{
errorLog.error("", e2);
}
finally
{
if (rs != null)
{
try
{
rs.close();
}
catch (SQLException e)
{
errorLog.error("Failed to close JDBC result set.");
}
}
if (stmt != null)
{
try
{
stmt.close();
}
catch (SQLException e)
{
errorLog.error("Failed to close JDBC statement.");
}
}
if (conn != null)
{
try
{
conn.close();
}
catch (SQLException e)
{
errorLog.error("Failed to close JDBC connection.");
}
}
}