Improve DISTINCT usage performance
Some useful tricks I found in order to improve SELECT DISTINCT construction while using JDBC driver (for SQLite and not only).
This approach two times outperforms simple code:
SELECT DISTINCT GroupID from Jobs
One may use the following “alias” construction (from java point if view it looks like):
st.executeUpdate("CREATE TEMP TABLE IF NOT EXISTS d(c UNIQUE);");
st.executeUpdate("INSERT OR IGNORE INTO d(c) SELECT GroupID FROM Jobs;");
ResultSet rs = st.executeQuery("SELECT c FROM temp.d ORDER BY c ASC;");
while (rs.next()) {
listModel.addElement(rs.getString(1));
}
st.executeUpdate("DROP TABLE IF EXISTS d;");
The source of the solution is at http://www.pubbs.net/200910/sqlite/13734-sqlite-improving-select-distinct-performance.html
| Posted in SQL | No Comments »