CODEWITHSUNDEEP
javasqliteprepared-statement
Nehil Danış
Nehil Danış

Reputation: 79

prepared statement effect on performance in java

I know , this question has already asked. But I still didn't get why my prepare statement didn't affect on the execution time.

Connection conn=null;
Statement stmt=null;

public Database(){
    try {
        Class.forName("org.sqlite.JDBC");
        conn = DriverManager
                .getConnection("jdbc:sqlite:***");
    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);
    }
}

public void insert(String [] data){
    try {
        String query = "INSERT INTO tableName "
                + "VALUES ('"+data[0]+"',"+"'"+data[1]+"',"+Double.parseDouble(data[2])+","+ 
                Double.parseDouble(data[3])+","+Double.parseDouble(data[4])+","+
                Double.parseDouble(data[5])+")";

        stmt =conn.createStatement();
        stmt.executeUpdate(query);

    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);
    }
}

Firstly , I tried this method to execute the insert expression, then I learned prepare statement has a good effect on execution time. Then I tried the program by using below method:

Connection conn=null;
PreparedStatement preStmt=null;
private String queryTemplate = "insert into tableName values(?,?,?,?,?,?)";

public Database(){
    try {
        Class.forName("org.sqlite.JDBC");
        conn = DriverManager
                .getConnection("jdbc:sqlite:***");
        preStmt = conn.prepareStatement(queryTemplate);
    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);
    }
}

public void insert(String [] data){
    try {
        preStmt.setString(1,data[0]);
        preStmt.setString(2,data[1]);
        preStmt.setDouble(3,Double.parseDouble(data[2]));
        preStmt.setDouble(4,Double.parseDouble(data[3]));
        preStmt.setDouble(5,Double.parseDouble(data[4]));
        preStmt.setDouble(6,Double.parseDouble(data[5]));

        preStmt.addBatch();
    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);
    }
}

public void ExecuteBatch()
{
    try {
        preStmt.executeBatch();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

I am using sqlite database btw.I used executeBatch and addBatch methods to decrease the execution time. But it is still the same , even worse than the first one.Can somebody explain it to me ? Thanks:)

Upvotes: 0

Views: 902

Answers (2)

Nicolas Filotto
Nicolas Filotto

Reputation: 44965

Try to switch in transactional mode instead of auto commit mode as next:

conn = DriverManager.getConnection("jdbc:sqlite:***");
// Set auto-commit to false which enable the transactional mode
conn.setAutoCommit(false);
...
// Explicitly commit statements to apply changes
conn.commit();

Indeed the auto commit mode will execute each of your insert request in a dedicated transaction and commit it automatically which has a big cost in term of I/O and so performance when you have many requests to execute since it will fill up the transaction log of your database.

More details here

Upvotes: 1

Susannah Potts
Susannah Potts

Reputation: 827

So the main advantage of using prepared statements comes from the precompilation that occurs. This saves the most time at execution if the statement is being used multiple times. If it's only being used once it wouldn't save much time during code execution.
The other advantages of using prepared statement is prevention of sql injection and ease of adding Objects.

Upvotes: 0

Related Questions