StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPerformance issue with WHERE and ORDER BY in HSQLDB
text
Body
copied!<p>I have a simple table called <code>history_point</code> which contains the following columns:</p> <ul> <li><code>id</code> - INTEGER PK</li> <li><code>device_id</code> - INTEGER</li> <li><code>registered</code> - TIMESTAMP</li> <li><code>double_value</code> - DOUBLE</li> <li><code>channel</code> - INTEGER</li> <li><code>type</code> - VARCHAR(100)</li> <li><code>int_value</code> - INTEGER</li> </ul> <p>The table also have index defined for the following column combinations:</p> <ul> <li><code>id</code></li> <li><code>device_id</code></li> <li><code>registered</code></li> <li><code>channel</code></li> <li><code>device_id</code>, <code>channel</code>, <code>registered</code></li> </ul> <p>The table contains approximately 200000 rows. I run queries using the Java code below.</p> <pre><code>import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class JdbcMain { public static void main(String[] args) throws Exception { Class.forName("org.hsqldb.jdbcDriver"); Connection con = DriverManager.getConnection("jdbc:hsqldb:file:db/homeki.db;ifexists=true"); Statement stmt = con.createStatement(); long start = System.currentTimeMillis(); ResultSet rs = stmt.executeQuery("<SQL query goes here>"); if (rs.next()) { System.out.println("Registered: " + rs.getDate("registered")); } long dur = System.currentTimeMillis() - start; System.out.println("Took " + dur + " ms."); stmt.execute("SHUTDOWN"); con.close(); } } </code></pre> <p>When I run the query <code>SELECT * FROM history_point WHERE device_id = 3 AND channel = 0 LIMIT 1</code>, it takes ~5 ms. If I run the query <code>SELECT * FROM history_point ORDER BY registered DESC LIMIT 1</code>, it also takes ~5 ms. <strong>But</strong>, if I run the query <code>SELECT * FROM history_point WHERE device_id = 3 AND channel = 0 ORDER BY registered DESC LIMIT 1</code>, it takes ~1000ms!</p> <p>I guess that could have been understandable, considering what they say at <a href="http://www.hsqldb.org/doc/1.8/guide/ch02.html#N1033B" rel="nofollow">http://www.hsqldb.org/doc/1.8/guide/ch02.html#N1033B</a>, "HSQLDB does not use indexes to improve sorting of query results" (which by the way sounds strange to me).</p> <p><strong>However</strong>, if I run the last query in Eclipse using the Eclipse Data Tools Platform's SQL Scrapbook, it executes in ~5 ms. Any query I throw at it using the SQL Scrapbook executes in ~5 ms. Why is that? They're both using the same JDBC driver and the same database.</p> <p>There are ~25000 rows in <code>history_point</code> with <code>device_id = 3</code> and <code>channel = 0</code>, if that matters.</p> <p>The connection string I use is <code>jdbc:hsqldb:file:db/mystorage.db</code>. The table was created as a <code>CACHED</code> table (all data is stored on disk).</p> <p>Can anyone explain this?</p> <p>Thanks in advance!</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