Martin Davis created GEOT-4007:
----------------------------------
Summary: Improve efficiency of PreparedStatement caching by using
parameters for Limit/Offset values
Key: GEOT-4007
URL: https://jira.codehaus.org/browse/GEOT-4007
Project: GeoTools
Issue Type: Improvement
Components: jdbc
Reporter: Martin Davis
Priority: Minor
Experience with a production GeoServer against an Oracle DB shows that more
PreparedStatements are created than are strictly needed. This is because the
Limit value is not provided as a parameter to the PreparedStatement but is
hard-coded. This results in many almost-identical statements being created in
the cache.
PreparedStatements in Oracle are very expensive, since they can use several MB
each, and also hold cursors open in the DB. It would be more efficient to
parameterize the Limit and Offset values. This would reduce the number of
unique statements created, and reduce memory and DB resources consumed.
This will involve a change to the JDBCDataStore class, and possibly the
SQLDialect(s) as well. There is already a standard pattern in the code for
providing values as PreparedStatement parameters, which hopefully will extend
to the Limit/Offset values as well.
Example of prepared statement with limiting is:
SELECT * FROM (SELECT OBJECTID,SITENAME,STATUS,GI_LOCATION,SHAPE as
SHAPE,SOIL_BORING_NAME,PHOTO1_NAME FROM DEP_GREEN.V_RAINWATER_REUSE_SYSTEM
WHERE SDO_RELATE(SHAPE, ?, 'mask=anyinteract querytype=WINDOW') = 'TRUE' )
WHERE ROWNUM <= 30000
summary
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://jira.codehaus.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Ridiculously easy VDI. With Citrix VDI-in-a-Box, you don't need a complex
infrastructure or vast IT resources to deliver seamless, secure access to
virtual desktops. With this all-in-one solution, easily deploy virtual
desktops for less than the cost of PCs and save 60% on VDI infrastructure
costs. Try it free! http://p.sf.net/sfu/Citrix-VDIinabox
_______________________________________________
Geotools-devel mailing list
Geotools-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geotools-devel
