Mercurial > hg > monetdb-java
changeset 694:97008566d6c5
Optimize number of SQL queries sent to server when ResultSetMetaData info is fetched from highest column number to first column 1.
Now it computes the range of columns to fetch in one go in groups of max 80 columns, reducing the number of queries sent to the server.
author | Martin van Dinther <martin.van.dinther@monetdbsolutions.com> |
---|---|
date | Wed, 23 Nov 2022 19:07:30 +0100 (2022-11-23) |
parents | 3442d331cad0 |
children | a5b840eebf77 |
files | src/main/java/org/monetdb/jdbc/MonetResultSet.java tests/JDBC_API_Tester.java |
diffstat | 2 files changed, 124 insertions(+), 10 deletions(-) [+] |
line wrap: on
line diff
--- a/src/main/java/org/monetdb/jdbc/MonetResultSet.java +++ b/src/main/java/org/monetdb/jdbc/MonetResultSet.java @@ -1265,6 +1265,7 @@ public class MonetResultSet private final boolean[] _is_fetched = new boolean[array_size]; private final int[] _isNullable = new int[array_size]; private final boolean[] _isAutoincrement = new boolean[array_size]; + private int nextUpperbound = array_size; /** * A private utility method to check validity of column index number @@ -1283,14 +1284,9 @@ public class MonetResultSet private final void fetchColumnInfo(final int column) throws SQLException { // for debug: System.out.println("fetchColumnInfo(" + column + ")"); checkColumnIndexValidity(column); - if (_is_fetched[column] != true) { - // fetch column info for multiple columns combined in one go, starting at 1 - fetchManyColumnsInfo(1); - if (_is_fetched[column] != true) { - // fetch info for column x if it was not fetched by the previous call - fetchManyColumnsInfo(column); - } + // fetch column info for multiple columns combined in one go + fetchManyColumnsInfo(column); } if (_is_fetched[column]) @@ -1318,7 +1314,22 @@ public class MonetResultSet // So 80 is a good balance between speedup (up to 79x) and size of generated query sent to server final int MAX_COLUMNS_PER_QUERY = 80; - final StringBuilder query = new StringBuilder(600 + (MAX_COLUMNS_PER_QUERY * 150)); + // Determine the optimal startcol to make use of fetching up to 80 columns in one query. + int startcol = column; + if ((startcol > 1) && (startcol + MAX_COLUMNS_PER_QUERY >= nextUpperbound)) { + // we can fetch info from more columns in one query if we start with a lower startcol + startcol = nextUpperbound - MAX_COLUMNS_PER_QUERY; + if (startcol < 1) { + startcol = 1; + } else + if (startcol > column) { + startcol = column; + } + nextUpperbound = startcol; // next time this nextUpperbound value will be used + // for debug: System.out.println("fetchManyColumnsInfo(" + column + ")" + (startcol != column ? " changed into startcol: " + startcol : "") + " nextUpperbound: " + nextUpperbound); + } + + final StringBuilder query = new StringBuilder(410 + (MAX_COLUMNS_PER_QUERY * 150)); /* next SQL query is a simplified version of query in MonetDatabaseMetaData.getColumns(), to fetch only the needed attributes of a column */ query.append("SELECT " + "s.\"name\" AS schnm, " + @@ -1339,7 +1350,7 @@ public class MonetResultSet String tblName = null; String colName = null; int queriedcolcount = 0; - for (int col = column; col < array_size && queriedcolcount < MAX_COLUMNS_PER_QUERY; col++) { + for (int col = startcol; col < array_size && queriedcolcount < MAX_COLUMNS_PER_QUERY; col++) { if (_is_fetched[col] != true) { if (_is_queried[col] != true) { _isNullable[col] = columnNullableUnknown; @@ -1416,7 +1427,7 @@ public class MonetResultSet if (queriedcolcount != 0) { // not all queried columns have resulted in a returned data row. // make sure we do not match those columns again next run - for (int col = column; col < array_size; col++) { + for (int col = startcol; col < array_size; col++) { if (_is_fetched[col] != true && _is_queried[col]) { _is_fetched[col] = true; // for debug: System.out.println("Found NO match at [" + col + "] for " + getSchemaName(col) + "." + getTableName(col) + "." + getColumnName(col));
--- a/tests/JDBC_API_Tester.java +++ b/tests/JDBC_API_Tester.java @@ -85,6 +85,7 @@ final public class JDBC_API_Tester { jt.Test_CallableStmt(); jt.Test_Rbooleans(); jt.Test_Rmetadata(); + jt.Test_RfetchManyColumnsInfo(); jt.Test_Rpositioning(); jt.Test_Rsqldata(); jt.Test_Rtimedate(); @@ -108,6 +109,7 @@ final public class JDBC_API_Tester { jt.BugSetQueryTimeout_Bug_3357(); jt.SQLcopyinto(); jt.DecimalPrecisionAndScale(); + /* run next long running test (11 minutes) only before a new release */ /* jt.Test_PSlargeamount(); */ @@ -3487,6 +3489,107 @@ final public class JDBC_API_Tester { "0. true true\n"); } + private void Test_RfetchManyColumnsInfo() { + sb.setLength(0); // clear the output log buffer + + Statement stmt = null; + ResultSet rs = null; + try { + final int NR_COLUMNS = 180; + final StringBuilder sql = new StringBuilder(50 + (NR_COLUMNS * 12)); + + sql.append("CREATE TABLE Test_RfetchManyColumnsInfo ("); + for (int col = 1; col <= NR_COLUMNS; col++) { + sql.append("col").append(col).append(" int"); + sql.append((col < NR_COLUMNS) ? ", " : ")"); + } + + stmt = con.createStatement(); + stmt.executeUpdate(sql.toString()); + + // add 1 row (all NULLs) + int inserted = stmt.executeUpdate("INSERT INTO Test_RfetchManyColumnsInfo (col1) VALUES (1)"); + if (inserted != 1) + sb.append("Expected 1 row inserted, but got: ").append(inserted).append("\n"); + + rs = stmt.executeQuery("SELECT * FROM Test_RfetchManyColumnsInfo"); + rs.next(); + ResultSetMetaData rsmd = rs.getMetaData(); + sb.append(rsmd.getColumnCount()).append(" columns start at columnCount\n"); + // do pulling of the metadata info in reverse order to test optimizing logic + // in ResultSetMetaData.fetchManyColumnsInfo() to choose a lower start_col iteratively + for (int col = rsmd.getColumnCount(); col >= 1; col--) { + // sb.append(col).append(","); + rsmd.getColumnClassName(col); + rsmd.getColumnDisplaySize(col); + rsmd.getColumnLabel(col); + rsmd.getColumnName(col); + rsmd.getColumnType(col); + rsmd.getColumnTypeName(col); + rsmd.getPrecision(col); + rsmd.getScale(col); + rsmd.getCatalogName(col); + rsmd.getSchemaName(col); + rsmd.getTableName(col); + rsmd.isAutoIncrement(col); + rsmd.isCaseSensitive(col); + rsmd.isCurrency(col); + rsmd.isDefinitelyWritable(col); + if (rsmd.isNullable(col) != ResultSetMetaData.columnNullable) + sb.append(col).append(" wrong isNullable()").append(rsmd.isNullable(col)).append("\n"); + rsmd.isReadOnly(col); + rsmd.isSearchable(col); + rsmd.isSigned(col); + rsmd.isWritable(col); + } + rs.close(); + + rs = stmt.executeQuery("SELECT * FROM Test_RfetchManyColumnsInfo"); + rs.next(); + rsmd = rs.getMetaData(); + sb.append(rsmd.getColumnCount()).append(" columns start at 1\n"); + for (int col = 1; col <= rsmd.getColumnCount(); col++) { + // sb.append(col).append(","); + rsmd.getColumnClassName(col); + rsmd.getColumnDisplaySize(col); + rsmd.getColumnLabel(col); + rsmd.getColumnName(col); + rsmd.getColumnType(col); + rsmd.getColumnTypeName(col); + rsmd.getPrecision(col); + rsmd.getScale(col); + rsmd.getCatalogName(col); + rsmd.getSchemaName(col); + rsmd.getTableName(col); + rsmd.isAutoIncrement(col); + rsmd.isCaseSensitive(col); + rsmd.isCurrency(col); + rsmd.isDefinitelyWritable(col); + if (rsmd.isNullable(col) != ResultSetMetaData.columnNullable) + sb.append(col).append(" wrong isNullable()").append(rsmd.isNullable(col)).append("\n"); + rsmd.isReadOnly(col); + rsmd.isSearchable(col); + rsmd.isSigned(col); + rsmd.isWritable(col); + } + rs.close(); + } catch (SQLException e) { + sb.append("FAILED: ").append(e.getMessage()).append("\n"); + } + + // cleanup table + try { + stmt.executeUpdate("DROP TABLE IF EXISTS Test_RfetchManyColumnsInfo;"); + } catch (SQLException e) { + sb.append("FAILED to drop: ").append(e.getMessage()).append("\n"); + } + closeStmtResSet(stmt, rs); + + compareExpectedOutput("Test_RfetchManyColumnsInfo", + "180 columns start at columnCount\n" + + "180 columns start at 1\n"); + } + private void Test_Rpositioning() { sb.setLength(0); // clear the output log buffer