Changeset: 19a5583a7b5f for monetdb-java URL: https://dev.monetdb.org/hg/monetdb-java?cmd=changeset;node=19a5583a7b5f Modified Files: ChangeLog src/main/java/nl/cwi/monetdb/jdbc/MonetResultSet.java Branch: default Log Message:
Improved performance of ResultSetMetaData methods isAutoIncrement(), getPrecision() and getScale() significant for columns of specific data types as in some cases no costly meta data query is executed anymore. diffs (truncated from 323 to 300 lines): diff --git a/ChangeLog b/ChangeLog --- a/ChangeLog +++ b/ChangeLog @@ -2,6 +2,9 @@ # This file is updated with Maddlog * Thu Oct 8 2020 Martin van Dinther <martin.van.dint...@monetdbsolutions.com> +- Improved performance of ResultSetMetaData methods isAutoIncrement(), + getPrecision() and getScale() significant for columns of specific data + types as in some cases no costly meta data query is executed anymore. - The connection properties treat_clob_as_varchar and treat_blob_as_binary are now set to true by default within the JDBC driver. This is done as it results by default in less memory usage, (much) faster response diff --git a/src/main/java/nl/cwi/monetdb/jdbc/MonetResultSet.java b/src/main/java/nl/cwi/monetdb/jdbc/MonetResultSet.java --- a/src/main/java/nl/cwi/monetdb/jdbc/MonetResultSet.java +++ b/src/main/java/nl/cwi/monetdb/jdbc/MonetResultSet.java @@ -1324,21 +1324,35 @@ public class MonetResultSet /** * Indicates whether the designated column is automatically numbered. * + * This method is currently very expensive for BIGINT, + * INTEGER, SMALLINT and TINYINT result column types + * as it needs to retrieve the information from the + * database using an SQL meta data query. + * * @param column the first column is 1, the second is 2, ... * @return true if so; false otherwise * @throws SQLException if a database access error occurs */ @Override public boolean isAutoIncrement(final int column) throws SQLException { - checkColumnIndexValidity(column); - try { - if (_is_fetched[column] != true) { - fetchColumnInfo(column); - } - return _isAutoincrement[column]; - } catch (IndexOutOfBoundsException e) { - throw MonetResultSet.newSQLInvalidColumnIndexException(column); + // only few integer types can be auto incrementable in MonetDB + // see: https://www.monetdb.org/Documentation/SQLReference/DataTypes/SerialDatatypes + switch (getColumnType(column)) { + case Types.BIGINT: + case Types.INTEGER: + case Types.SMALLINT: + case Types.TINYINT: + try { + if (_is_fetched[column] != true) { + fetchColumnInfo(column); + } + return _isAutoincrement[column]; + } catch (IndexOutOfBoundsException e) { + throw MonetResultSet.newSQLInvalidColumnIndexException(column); + } } + + return false; } /** @@ -1414,14 +1428,14 @@ public class MonetResultSet public boolean isSigned(final int column) throws SQLException { // we can hardcode this, based on the colum type switch (getColumnType(column)) { - case Types.NUMERIC: - case Types.DECIMAL: case Types.TINYINT: case Types.SMALLINT: case Types.INTEGER: case Types.REAL: case Types.FLOAT: case Types.DOUBLE: + case Types.DECIMAL: + case Types.NUMERIC: return true; case Types.BIGINT: final String monettype = getColumnTypeName(column); @@ -1434,10 +1448,10 @@ public class MonetResultSet return true; // All other types should return false // case Types.BOOLEAN: - // case Types.DATE: - // case Types.TIME: + // case Types.DATE: // can year be negative? + // case Types.TIME: // can time be negative? // case Types.TIME_WITH_TIMEZONE: - // case Types.TIMESTAMP: + // case Types.TIMESTAMP: // can year be negative? // case Types.TIMESTAMP_WITH_TIMEZONE: default: return false; @@ -1516,10 +1530,20 @@ public class MonetResultSet } /** - * Get the designated column's number of decimal digits. - * This method is currently very expensive as it needs to - * retrieve the information from the database using an SQL - * query. + * Get the designated column's specified column size. + * For numeric data, this is the maximum precision. + * For character data, this is the length in characters. + * For datetime datatypes, this is the length in characters + * of the String representation (assuming the maximum + * allowed precision of the fractional seconds component). + * For binary data, this is the length in bytes. + * For the ROWID datatype, this is the length in bytes. + * 0 is returned for data types where the column size is not applicable. + * + * This method is currently very expensive for DECIMAL, NUMERIC + * CHAR, VARCHAR, CLOB, BLOB, VARBINARY and BINARY result + * column types as it needs to retrieve the information + * from the database using an SQL meta data query. * * @param column the first column is 1, the second is 2, ... * @return precision @@ -1527,73 +1551,76 @@ public class MonetResultSet */ @Override public int getPrecision(final int column) throws SQLException { - checkColumnIndexValidity(column); - try { - if (_is_fetched[column] != true) { - fetchColumnInfo(column); - } - if (_precision[column] == 0) { - // apparently no precision could be fetched - // use columnDisplaySize() value for variable length data types - switch (getColumnType(column)) { - case Types.CHAR: - case Types.VARCHAR: - case Types.LONGVARCHAR: // MonetDB doesn't use type LONGVARCHAR, it's here for completeness - case Types.CLOB: - case Types.BLOB: - case Types.NUMERIC: - case Types.DECIMAL: + final int tpe = getColumnType(column); + switch (tpe) { + case Types.BIGINT: + return 19; + case Types.INTEGER: + return 10; + case Types.SMALLINT: + return 5; + case Types.TINYINT: + return 3; + case Types.REAL: + return 7; + case Types.FLOAT: + case Types.DOUBLE: + return 15; + + case Types.DECIMAL: + case Types.NUMERIC: + // these data types do not have a fixed precision, max precision however is 38 + // we need to fetch the defined precision with an SQL query ! + case Types.CHAR: + case Types.VARCHAR: + case Types.LONGVARCHAR: // MonetDB doesn't use type LONGVARCHAR, it's here for completeness + case Types.CLOB: + case Types.BINARY: + case Types.VARBINARY: + case Types.BLOB: + // these data types also do not have a fixed length + try { + if (_is_fetched[column] != true) { + fetchColumnInfo(column); + } + if (_precision[column] == 0) { + // apparently no precision or max length could be fetched + // use columnDisplaySize() value as alternative _precision[column] = getColumnDisplaySize(column); - break; - case Types.TINYINT: - _precision[column] = 3; - break; - case Types.SMALLINT: - _precision[column] = 5; - break; - case Types.INTEGER: - _precision[column] = 10; - break; - case Types.BIGINT: - _precision[column] = 19; - break; - case Types.REAL: - _precision[column] = 7; - break; - case Types.FLOAT: - case Types.DOUBLE: - _precision[column] = 15; - break; - case Types.BOOLEAN: - _precision[column] = 5; - break; - case Types.DATE: - _precision[column] = 10; - break; - case Types.TIME: - case Types.TIME_WITH_TIMEZONE: - _precision[column] = 8; - break; - case Types.TIMESTAMP: - case Types.TIMESTAMP_WITH_TIMEZONE: - _precision[column] = 19; - break; - default: - _precision[column] = 30; - break; + if (tpe == Types.BLOB || tpe == Types.VARBINARY || tpe == Types.BINARY) + // These expect number of bytes, not number of hex chars + _precision[column] = (_precision[column] / 2) +1; + } + return _precision[column]; + } catch (IndexOutOfBoundsException e) { + throw MonetResultSet.newSQLInvalidColumnIndexException(column); } - } - return _precision[column]; - } catch (IndexOutOfBoundsException e) { - throw MonetResultSet.newSQLInvalidColumnIndexException(column); + case Types.DATE: + return 10; // 2020-10-08 + case Types.TIME: + return 15; // 21:51:34.399753 + case Types.TIME_WITH_TIMEZONE: + return 21; // 21:51:34.399753+02:00 + case Types.TIMESTAMP: + return 26; // 2020-10-08 21:51:34.399753 + case Types.TIMESTAMP_WITH_TIMEZONE: + return 32; // 2020-10-08 21:51:34.399753+02:00 + case Types.BOOLEAN: + return 1; + default: + // All other types should return 0 + return 0; } } /** * Gets the designated column's number of digits to right of - * the decimal point. This method is currently very - * expensive as it needs to retrieve the information from - * the database using an SQL query. + * the decimal point. + * 0 is returned for data types where the scale is not applicable. + * + * This method is currently very expensive for DECIMAL and NUMERIC + * result column types as it needs to retrieve the information + * from the database using an SQL meta data query. * * @param column the first column is 1, the second is 2, ... * @return scale @@ -1601,25 +1628,56 @@ public class MonetResultSet */ @Override public int getScale(final int column) throws SQLException { - checkColumnIndexValidity(column); - try { - if (_is_fetched[column] != true) { - fetchColumnInfo(column); - } - return _scale[column]; - } catch (IndexOutOfBoundsException e) { - throw MonetResultSet.newSQLInvalidColumnIndexException(column); + switch (getColumnType(column)) { + case Types.DECIMAL: + case Types.NUMERIC: + // these data types may have a scale, max scale is 38 + try { + if (_is_fetched[column] != true) { + fetchColumnInfo(column); + } + return _scale[column]; + } catch (IndexOutOfBoundsException e) { + throw MonetResultSet.newSQLInvalidColumnIndexException(column); + } + case Types.TIME: + case Types.TIME_WITH_TIMEZONE: + case Types.TIMESTAMP: + case Types.TIMESTAMP_WITH_TIMEZONE: + // support microseconds, so scale 6 + return 6; // 21:51:34.399753 + // All other types should return 0 + // case Types.BIGINT: + // case Types.INTEGER: + // case Types.SMALLINT: + // case Types.TINYINT: + // case Types.REAL: + // case Types.FLOAT: + // case Types.DOUBLE: + // case Types.CHAR: + // case Types.VARCHAR: + // case Types.LONGVARCHAR: // MonetDB doesn't use type LONGVARCHAR, it's here for completeness + // case Types.CLOB: + // case Types.BINARY: + // case Types.VARBINARY: + // case Types.BLOB: + // case Types.DATE: + // case Types.BOOLEAN: _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list