Hi all,
I've got some H2 code that executes a query that returns possibly 100,000+
rows and 95 columns. I need to retrieve every column value by name, using
JDBC. Using H2's built-in profiling tool, StringUtils.toUpperEnglish seems
to be a bottleneck. It seems that JdbcResultSet.getColumnIndex(String
columnLabel) is part of the problem.
I can see a couple of ways I could improve this in H2:
* caching the conversion from column-name to uppercase column name
in JdbcResultSet.getColumnIndex(String columnLabel), on the assumption that
if code calls, eg, rs.getInt("foobar") once it is rather likely to call it
again.
* make StringUtils.toUpperEnglish cache recent invocations, on the
assumption that the same strings within H2 probably get converted to upper
case frequently.
* rework the logic in JdbcResultSet.getColumnIndex(String columnLabel) a
little - there is already some code that tries to minimize the
toUpperEnglish invocations, but I think it could be made smarter.
* I also suspect there is a Turkish-locale bug in in the case where the
column count is less than 3, but I'd want to test before stating this
incontrovertibly.
Of course, I could also implement a work-around in my code, but here is
perhaps a chance for a tiny but worthwhile performance improvement for all
users of H2.
Thoughts?
I'm using H2-1.3.176, but I've checked SVN and the relevant code seems
unchanged at the present.
Regards,
Steve
Contrived code to reproduce the problem follows:
import org.h2.util.Profiler;
import javax.swing.*;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class ScratchSpace extends JFrame {
public static void main(String[] args) throws SQLException,
ClassNotFoundException, IOException {
Class.forName("org.h2.Driver");
Connection conn = DriverManager.getConnection("jdbc:h2:~/test");
conn.prepareStatement("DROP TABLE IF EXISTS foobar").execute();
// create 100 column names, each of the form AA, AB, AC...etc.
System.out.println("Creating table with 100 columns");
String sql = "create table foobar (";
for (int i = 0; i < 100; i++) {
String col = getColumnName(i);
if (i > 0) {
sql += ", ";
}
sql += col + " INT";
}
sql += ")";
System.out.println("sql = " + sql);
conn.prepareStatement(sql).execute();
final int MAX_ROWS = 100000;
System.out.println("Inserting " + MAX_ROWS + " rows");
for (int j = 0; j < MAX_ROWS; j++) {
conn.prepareStatement("INSERT INTO foobar (Column_AA) VALUES
(1)").execute();
}
Profiler prof = new Profiler();
prof.startCollecting();
final ResultSet resultSet = conn.prepareStatement("SELECT * FROM
foobar").executeQuery();
int rowCount = 0;
while (resultSet.next()) {
rowCount++;
final int columnCount =
resultSet.getMetaData().getColumnCount();
for (int column = 1; column <= columnCount; column++) {
final String columnName =
resultSet.getMetaData().getColumnName(column);
final int anInt = resultSet.getInt(columnName);
}
}
System.out.println("Done");
prof.stopCollecting();
System.out.println(prof.getTop(3));
conn.close();
}
private static String getColumnName(int i) {
return "Column_" + (char) ((65 + i / 26)) + (char) (65 + i % 26);
}
}
Profiler output:
Profiler: top 3 stack trace(s) of of 2219 ms of 825 thread dumps:
108/825 (13%):
at org.h2.util.StringUtils.toUpperEnglish(StringUtils.java:93)
at org.h2.jdbc.JdbcResultSet.getColumnIndex(JdbcResultSet.java:3120)
at org.h2.jdbc.JdbcResultSet.get(JdbcResultSet.java:3210)
at org.h2.jdbc.JdbcResultSet.getInt(JdbcResultSet.java:340)
at com.barbarysoftware.pokercopilot.ScratchSpace.main(ScratchSpace.java:52)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)
72/825 (8%):
at org.h2.store.fs.FileDisk.read(FilePathDisk.java:451)
at org.h2.store.fs.FileUtils.readFully(FileUtils.java:345)
at org.h2.store.FileStore.readFully(FileStore.java:274)
at org.h2.result.ResultDiskBuffer.readRow(ResultDiskBuffer.java:198)
at org.h2.result.ResultDiskBuffer.nextUnsorted(ResultDiskBuffer.java:221)
at org.h2.result.ResultDiskBuffer.next(ResultDiskBuffer.java:214)
at org.h2.result.LocalResult.next(LocalResult.java:234)
at org.h2.jdbc.JdbcResultSet.nextRow(JdbcResultSet.java:3233)
at org.h2.jdbc.JdbcResultSet.next(JdbcResultSet.java:124)
at com.barbarysoftware.pokercopilot.ScratchSpace.main(ScratchSpace.java:47)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)
65/825 (7%):
at org.h2.engine.Session.isReconnectNeeded(Session.java:1336)
at org.h2.jdbc.JdbcConnection.checkClosed(JdbcConnection.java:1470)
at org.h2.jdbc.JdbcStatement.checkClosed(JdbcStatement.java:987)
at
org.h2.jdbc.JdbcPreparedStatement.checkClosed(JdbcPreparedStatement.java:1621)
at org.h2.jdbc.JdbcStatement.checkClosed(JdbcStatement.java:961)
at org.h2.jdbc.JdbcResultSet.checkClosed(JdbcResultSet.java:3179)
at
org.h2.jdbc.JdbcResultSetMetaData.checkClosed(JdbcResultSetMetaData.java:439)
at
org.h2.jdbc.JdbcResultSetMetaData.checkColumnIndex(JdbcResultSetMetaData.java:447)
at
org.h2.jdbc.JdbcResultSetMetaData.getColumnName(JdbcResultSetMetaData.java:87)
at com.barbarysoftware.pokercopilot.ScratchSpace.main(ScratchSpace.java:51)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)
summary:
26%: org.h2.engine
20%: org.h2.jdbc
17%: org.h2.store.fs
--
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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
