[Geotools-devel] [jira] (GEOT-4188) Opmitize JDBCDataStore code to retrieve column metadata

[Milton Jonathan (JIRA)]

Milton Jonathan created GEOT-4188 Opmitize JDBCDataStore code to retrieve column metadata Issue Type: Improvement Affects Versions: 2.7.5 Assignee: Unassigned Components: jdbc Created: 28/Jun/12 4:04 PM Description: It is known that calling the JDBC method Connection.getMetadata().getColumns() to retrieve table/column metadata can be extremely slow, especially for large databases. There are some suggestions to improve this, such as the ones listed here: http://www.theserverside.com/news/1365579/Using-Database-MetaData-methods-appropriately Personal experience on a different project was that just by following the first suggestion and adding Catalog information (available through Connection.getCatalog), our calls started to perform 9x faster! That is, instead of calling: columns = metaData.getColumns(null, databaseSchema, tableName, columnName); You would call something like: columns = metaData.getColumns(cx.getCatalog(), databaseSchema, tableName, columnName); Afterwards, we also followed the final suggestion on the above site, and started using a PreparedStatement (without executing it) as follows: PreparedStatement ps = new PreparedStatement("SELECT * FROM my_table WHERE 1 = 0"); ResultSetMetaData metadata = ps.getMetaData(); for (int i = 1; i <= metadata.getColumnCount(); i++) { String name = metadata.getColumnName(i); String nativeTypeName = metadata.getColumnTypeName(i); int dataType = metadata.getColumnType(i); ... } This made our calls further improve by a factor of 2.5x! No tests were performed using GeoTools, but I would be optimistic about implementing and benchmarking these alternatives Project: GeoTools Priority: Minor Reporter: Milton Jonathan

This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators. For more information on JIRA, see: http://www.atlassian.com/software/jira

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and 
threat landscape has changed and how IT managers can respond. Discussions 
will include endpoint security, mobile security and the latest in malware 
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
GeoTools-Devel mailing list
GeoTools-Devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geotools-devel