JDBC tweaks to enable streaming result sets

In order to be able to serve large-scale deployments, deegree 3 has a full streaming architecture. This allows deegree 3 WFS to handle GetFeature requests that return Gigabytes or Terabytes of GML – without hogging the server. For example on a deegree 3-based INSPIRE Download Service, it’s no problem request all Cadastral Parcels of a member state. Another aspect is the streaming renderer used in deegree 3 WMS which can draw millions of features without running into memory problems.

Technically, deegree 3 uses iterators to realize this. If a query is performed on a feature store that is backed by an SQL database, the JDBC ResultSet is encapsulated in a FeatureInputStream. The Feature instances are only produced on demand (whenever the next instance is accessed).

Unfortunately, it’s the pecularities of the database vendors and their JDBC drivers that can put a spoke in one’s wheel here. Obviously, one should call Statement#setFetchSize(int) in order to give the DB/JDBC driver a hint on the number of result set rows to fetch in a single batch. However, it turns out that this may not be enough to prevent the collecting of all result rows in memory or the driver waiting until all results have been collected by the DB.

PostgreSQL

For PostgreSQL, it’s additionally required to call setAutoCommit(false) on the Connection object. The reason is that PostgreSQL only enables cursors inside a transaction. See here or here for more details.

Microsoft SQL Server

On SQL Server, the trick is to add the option selectMode=cursor to the JDBC URL, e.g. jdbc:microsoft:sqlserver://myHost:1433;selectMethod=cursor;databaseName=myDB. Some info can be found here. This may also be interesting in this regard.

Oracle

Didn’t really investigate this on Oracle yet. Does anybody have to share some insights?

Advertisements

About Markus Schneider

Geospatial software developer, CEO of Occam Labs
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s