Changeset: 97008566d6c5 for monetdb-java
URL: https://dev.monetdb.org/hg/monetdb-java/rev/97008566d6c5
Modified Files:
src/main/java/org/monetdb/jdbc/MonetResultSet.java
tests/JDBC_API_Tester.java
Branch: default
Log Message:
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.
diffs (197 lines):
diff --git a/src/main/java/org/monetdb/jdbc/MonetResultSet.java
b/src/main/java/org/monetdb/jdbc/MonetResultSet.java
--- 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));
diff --git a/tests/JDBC_API_Tester.java b/tests/JDBC_API_Tester.java
--- 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
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
