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
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.
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.
Didn’t really investigate this on Oracle yet. Does anybody have to share some insights?