Interesting. Seems worth trying out! Can you open a jira for this Milton?
Thanks.
On Tue, Jun 26, 2012 at 7:05 AM, Milton Jonathan
<[email protected]>wrote:
> Hey there
>
> Just dropping by to share something I found out recently. A while ago we
> implemented some JDBC code to extract column information, which is quite
> similar to what you guys implemented in GeoTools' JDBCDataStore: namely
> to call Connection.getMetadata().getColumns()
>
> Well, we recently had some performance problems with some larger
> databases using SQLServer, and we found out that this JDBC call is known
> to be quite slow, especially if not used with care.
>
> Looking around, I saw some suggestions to improve this, such as the ones
> listed here:
>
> http://www.theserverside.com/news/1365579/Using-Database-MetaData-methods-appropriately
>
> It ended up that just by following the first suggestion and adding
> Catalog information (available through Connection.getCatalog), our calls
> started to perform 9x faster for our large database!
>
> 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);
>
> Yes, it sounds quite absurd but for us, using SQLServer 2005, it was a
> big improvement. I also did not test this in GeoTools, but I thought you
> guys would like to know about it.
>
> By the way, in the webpage listed above, there are other suggestions
> that (according to them) perform even faster. Their final suggestion is
> to use a PreparedStatement.
>
> Hope this helps
> Milton
>
> --
>
> Milton Jonathan
> Grupo GIS e Meio Ambiente
> Tecgraf/PUC-Rio
> Tel: +55-21-3527-2502
>
>
>
> ------------------------------------------------------------------------------
> 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
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/geotools-devel
>
--
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.
------------------------------------------------------------------------------
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
[email protected]
https://lists.sourceforge.net/lists/listinfo/geotools-devel
