Re: bug report: slow getColumnTypeName

Luis Flores wrote:
> I've looked at the AbstractJdbc2ResultSetMetadata getColumnTypeName,
> for version 1000 and 802.
>
> The change is simple, there is an extra call to isAutoIncrement, to be
> able to return correct values in the serial and bigserial cases.
>
> The isAutoIncrement call is slow because it triggers
> fetchFieldMetadata, witch get all metada for all fields.
>
> One simple optimization is to change the current method to:
>
> public String getColumnTypeName(int column) throws SQLException
> {
> String type = getPGType(column);
> if ( ( "int4".equals(type) || "int8".equals(type) )&&
> isAutoIncrement(column)) {
> if ("int4".equals(type)) {
> return "serial";
> } else if ("int8".equals(type)) {
> return "bigserial";
> }
> }
>
> return type;
> }
>
> In this case, the isAutoIncrement is only called on int4 and int8
> columns, causing the performance for all the other column types to
> remain the same.
>
> May they are better options, but I think this change is good, it
> delays fetching metadata, and speeds up the method, without side
> effects.
>
> Luis Flores

isAutoIncrement() is not the only place that fetchFieldMetadata() is
called. isNullable(), getBaseColumnName(), getBaseSchemaName(), &
getBaseTableName(). Everyone of those calls is going to have the
same performance hit looks like the first time, the more table columns
the worst it gets. I know its nice to cache for all the table fields,
but when it hits performance like that you have to ask is it worth it.

ResultSetMetaData are usually limited creatures aren't they? Why would
you cache all your table fields info. Cache one by one maybe as called.

danap.