Greetings,
There is the notable performance issue in version 1.3.172 and newer for
requests like:
+-----------------------------------------+------------------------------------+
| STATUS is NOT indexed | STATUS is indexed |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+-----------------+-----------------+
|
SQL
| 1.3.171 | 1.3.173 | 1.3.171 | 1.3.173 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+-----------------+-----------------+
| SELECT DISTINCT NAME FROM T1 WHERE STATUS = 0 ORDER BY NAME ASC LIMIT 400
OFFSET 1200 | ~70 ms | ~3200 ms | ~3700 ms
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+-----------------+-----------------+
| SELECT NAME FROM T1 WHERE STATUS = 0 ORDER BY NAME ASC LIMIT 400 OFFSET
1200 | ~50 ms | ~570 ms | ~800
ms |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+-----------------+-----------------+
The degradation is related to the non-indexed column condition with sorting
and limits.
- If the STATUS column is not indexed 1.3.171 works very fast comparable to
1.3.173
- If we remove sorting both versions regardless of whether the column is
indexed or not work equally fast
- If we remove limit-offset both versions regardless of whether the column
is indexed or not work equally slow
The attached file contains java source code that reproduces the issue.
It creates the table with 250.000 rows. The 'STATUS' column is 0 for all
rows. The usage of 'SELECTIVITY 1' for STATUS column doesn't make any
difference. The 'NAME' is indexed and unique.
Connection URL:
jdbc:h2:/tmp/h2_test/test;LOG=1;MVCC=TRUE;PAGE_SIZE=16384;CACHE_TYPE=TQ;ALIAS_COLUMN_NAME=TRUE;IGNORECASE=TRUE;DB_CLOSE_DELAY=0;CACHE_SIZE=400000;MAX_OPERATION_MEMORY=75000000
Thank you!
Regards,
Max Sidnin
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
package h2;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class H2SpeedTest {
private Connection conn;
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Class.forName("org.h2.Driver");
H2SpeedTest test = new H2SpeedTest();
test.printVersion();
test.initDB();
test.doRequest();
test.closeConnection();
}
public H2SpeedTest() throws ClassNotFoundException, SQLException {
this.conn = getConnection();
}
private void initDB() throws ClassNotFoundException, SQLException {
System.out.println("Create table...");
executeUpdate("DROP TABLE IF EXISTS T1");
executeUpdate("CREATE TABLE T1 (ID BIGINT, STATUS BIGINT, NAME VARCHAR, PRIMARY KEY (ID))");
PreparedStatement st = conn.prepareStatement("INSERT INTO T1 (ID, STATUS, NAME) VALUES (?, ?, ?)");
System.out.println("Write data to table...");
for (int row = 1; row <= 250000; row++) {
st.setLong(1, row);
st.setLong(2, 0);
st.setString(3, "Name #" + row);
st.addBatch();
}
st.executeBatch();
st.close();
System.out.println("Create indicies...");
executeUpdate("CREATE INDEX IDX_T1_NAME ON T1(NAME);");
// executeUpdate("CREATE INDEX IDX_T1_STATUS ON T1(STATUS);"); //uncomment this line to slow request on any h2 version
}
private void doRequest() throws ClassNotFoundException, SQLException {
String sql = "SELECT DISTINCT NAME FROM T1 WHERE STATUS = 0 ORDER BY NAME ASC LIMIT 400 OFFSET 1200";
System.out.println(">> " + sql);
PreparedStatement st = conn.prepareStatement(sql);
long startTime = System.currentTimeMillis();
ResultSet rs = st.executeQuery();
long endTime = System.currentTimeMillis();
System.out.println("Total request time: " + (endTime - startTime) + " ms.");
rs.close();
}
private void closeConnection() throws SQLException {
conn.close();
}
private Connection getConnection() throws SQLException, ClassNotFoundException {
return DriverManager.getConnection(
"jdbc:h2:/tmp/h2_test/test;LOG=1;MVCC=TRUE;PAGE_SIZE=16384;CACHE_TYPE=TQ;ALIAS_COLUMN_NAME=TRUE;IGNORECASE=TRUE;DB_CLOSE_DELAY=0;CACHE_SIZE=400000;MAX_OPERATION_MEMORY=75000000");
}
private void executeUpdate(String sql) throws SQLException {
PreparedStatement st = conn.prepareStatement(sql);
st.executeUpdate();
st.close();
}
private void printVersion() {
try {
PreparedStatement st = conn.prepareStatement("SELECT H2VERSION()");
ResultSet rs = st.executeQuery();
if (rs.next()) {
System.out.println("H2 Version: " + rs.getString(1));
}
rs.close();
st.close();
}
catch (Throwable ex) {
System.out.println("H2 Version: 1.3.171");
}
}
}
