Obtaining interactive performance from a spatial database can be challenging. The fundamental goal of the database components in LuciadLightspeed is to be able to work with arbitrarily large databases. A database model will therefore not contain copies of all of its elements on the client side. Rather, they are retrieved from the database whenever they are needed. This implies unavoidable communication between the database and the client, which is generally far slower than just working with locally stored elements. There is an overhead due to sending and executing spatial SQL queries, and retrieving and decoding the resulting geometries and their features.

LuciadLightspeed attempts to reduce the effects of the overhead by taking a two-step approach, internally. Model elements are typically requested for a given rectangular area, by means of the method ILcd2DBoundsIndexedModel.applyOnInteract2DBounds.

  • In a first step, the database model retrieves the element identifiers (primary features) of the relevant elements in the database. The identifiers are requested with a spatial query.

  • In a second step, the database model retrieves the actual geometries and features that are not cached. They are requested with a query based on the primary feature of the spatial table. Retrieved and decoded model elements are cached whenever they are retrieved.

Database indexes

The most important factor in database performance is having the proper indexes. In the context of the database LuciadLightspeed component, two indexes are essential:

  • The spatial index on the geometry column of the spatial table.

  • The index on the primary key of the spatial table.

Cache size

You can set the cache size on the client side:

  • Call setMaxCacheSize on the database model decoder

  • For the model decoders which work with properties, use the maxCacheSize property.

The cache size is expressed as a number of model elements. Memory permitting, it should be as large as the number of objects in the spatial database.

If the cache is sufficiently large, it may already contain all required elements after only a few queries. The second step in the two-step approach then is not necessary and the database model skips it. This maximally reduces the amount of geometries and features that have to be read from disk.

On the other hand, it is also possible to set the cache size to 0. In that case, all relevant geometries and features have to be read from disk for every query. The first step in the two-step approach then is not necessary, and the database model skips it. This maximally reduces the number of queries that have to be made to the database, which is useful if the round-trip time is high compared to the bandwidth.

At the other end of the spectrum, if all the elements of the database model fit in memory, it is possible to just create a new local model, like TLcd2DBoundsIndexedModel and copy all database model elements over. This results in a single large query upfront. The database model can then be disposed off, not requiring any further queries while accessing the copied model. It is the fastest approach, provided that the spatial database is sufficiently small.

Connection pooling

If your application contains several different database models originating from the same database, it is generally worthwhile to use a connection pooling JDBC driver. Such a driver acts as a proxy for the normal DB2 JDBC driver. It maintains a pool of one or more connections. These connections are reused as much as possible. This avoids the overhead of maintaining different connections for the database models, saving both communication round-trips and database resources.

In order for the database model to work efficiently with a connection pooling driver, it is necessary to set the property alwaysCloseConnection in the properties file to true. This ensures that a connection is returned to the connection pool, by closing it after each query. Note that this would be extremely inefficient without connection pooling.

An example of a a connection pooling JDBC driver is the open-source Proxool driver. It is simply activated by specifying the driver and then prepending the JDBC URL with a Hikari prefix (Program: Specifying the Hikari sample driver in a database properties file.).

Program: Specifying the Hikari sample driver in a database properties file.
driver = samples.format.database.hikari.HikariDriver
url    = hikari.example:com.ibm.db2.jcc.DB2Driver:jdbc:........

alwaysCloseConnection = true

The Hikari sample driver can be found in the Lightspeed samples