StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>When it comes to select statement queries in JDBC, they can be very painfully slow if they're not utilized correctly. A few points:</p> <ol> <li>Make sure you index the proper columns in your table. A simple line such as: </li> </ol> <p><code>Statement stat = connection.createStatement();<br> stat.executeUpdate("create index {index_name} on orders({column_name});");<br> stat.close();</code></p> <p>Creating an index: <a href="http://www.w3schools.com/sql/sql_create_index.asp" rel="nofollow">http://www.w3schools.com/sql/sql_create_index.asp</a></p> <ol> <li><p>Insertion takes longer with indices, since each previous index needs to be updated as new records are inserted. Creating an index is best done after all the insert statements have been executed (better performance). Indexed columns take a hit on insertion performance but have significantly faster selection performance.</p></li> <li><p>Changing the JDBC driver may help slightly but overall should not be the underlying issue. Also make sure you're running in native mode. Pure-java mode is significantly slower, at least from what I've noticed. The following code segment will tell you which mode you're running, assuming you're using SQLite JDBC.</p></li> </ol> <p><code>System.out.println(String.format("%s mode", SQLiteJDBCLoader.isNativeMode() ? "native" : "pure-java"));</code></p> <p>I experienced the same issue with slow selections in a database with more than 500K records. The run time of my application would have been 9.9 days if I had not indexed. Now it is a blazing fast 2 minutes to do the exact same thing. SQLite is very fast when proper and optimized sql is used.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload