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