Java update SQL database performance

Question

I am using a Java GUI program to update a SQlite database. My Gui options are mostly toggle buttons with on/off values. Due to the nature of the Gui, performance in updating the database needs to be fast so control can be returned to the Gui calling function quickly.

I have the following code, which runs every time a toggle button is selected:

public static void updateDCStable(String item, int value) { try { long start = System.currentTimeMillis(); Class.forName("org.sqlite.JDBC"); String url = DATABASE_FILEPATH; Connection conn = DriverManager.getConnection(url); Statement update = conn.createStatement(); update.execute("UPDATE DCS " + "SET " + item + "='" + value + "';"); update.close(); conn.close(); long time_elapsed = System.currentTimeMillis() - start; System.out.println("Changed DCS date item " + item + " to " + value + " in " + time_elapsed + "(ms)"); } catch (SQLException ex) { /* Error handling */ } catch (ClassNotFoundException ex) { /* Error handling */ } }

My SQL table is basically configuration info with several columns and just one row, so the function takes the column and the update value and updates the item accordingly. The function works correctly, its just really slow....

Using the time elapsed, my average update time is running about 550 ms or so with a min time of 294 ms and a max time of 986 ms. Is there any way that I can speed up this process?

Andrzej Bobak · Accepted Answer · 2012-07-20 15:46:31Z

3

It would be faster if you used a connection from a connection pool. You wouldn't have to establish and close the connection each time you access the database, which takes a lot of the time.

PreparedStatements mentioned by Pierre will also help, but the majority of the time spent on your database jobs is the estabilishing/releasing the database connection issues.

edited Jul 20, 2012 at 15:46

answered Jul 20, 2012 at 12:46

Andrzej Bobak

2,1303 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

T Vernon Over a year ago

Thanks for your helpful answer. Yes, I realized that the connection was the most expensive part computationally. So I implemented a BoneCP Java database connection pool using the example from this site. Now my helper code try uses conn = ConnectionManager.getConnection() to set up the connection. Problem is that my performance is basically the same with an average time now of 752(ms), min 262(ms) and max 2919(ms). Is the example code correct, or am I missing something?

Andrzej Bobak Over a year ago

Run a few more tests. First execution of the code will take longer (establishing connection and additing it to the connection pool) but latter executions should take less time. Also implement the prepared statement. It will also help.

T Vernon Over a year ago

That's doing the trick, thanks! Average dropping towards 500 ms which is closer to acceptable. What kind of times would you expect for this type of operation with Java, Sqlite, and BoneCP together with the type of writes that I am doing? Just curious.

Andrzej Bobak Over a year ago

Really hard to say. It all depends on how your app is built.

Pierre · Accepted Answer · 2012-07-20 12:46:54Z

Use one main connection (initialized once) and a PreparedStatement:

A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.

void updateDCStable(Connection conn,String item, int value) {(...) PreparedStatement ps = conn.prepareStatement("UPDATE DCS set "+ item +"= ?"); ps.setInt(1,value);

Collectives™ on Stack Overflow

Java update SQL database performance

2 Answers 2

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Related