Geoserver parameterized SQL View layer vs. SQL View layer with WMS time dimension. Any differences in speed?

Question

I have two PostGIS tables, cities (city_code and geometry column) and precip (city_code, date, precip). The precip table can have more than one entry for each city since I have a precipitarion time series in it.

In order to display municipal precipitation using GeoServer, I though of 2 alternatives:

1. Use a parameterized SQL View layer, joining the two tables and setting date as a parameter for the precip data. Something like:

SELECT a.geocodigo, data_ini, chuva, wkb_geometry
FROM lm_municipio_a AS a
JOIN dado_semana_munic AS b
ON a.geocodigo = b.geocodigo
WHERE data_ini = '%my_date%'

2. Create a SQL View layer that has all the dates, precip and geometries and use WMS Time Dimension to filter the date. The SQL View would be something like

SELECT a.geocodigo as geocodigo, data_ini, chuva, wkb_geometry
FROM dado_semana_munic AS a 
LEFT JOIN lm_municipio_a AS b
ON a.geocodigo = b.geocodigo

Right now we are using the second option (SQL View + WMS Time dimension) and Leaflet TimeDimension plugin in order to create some animated precip maps. But I wondered if using a parameterized SQL View layer would be better. I ask this because in the first option, the SQLView is a smaller table with nrows = the number of municipalities. The second option has a larger table, with nrows = the number of observations (I have 20 years of weekly data).

Answer 1

I did some benchmarks using an R script to retrieve an image from a GeoServer. I tried two layers, as described in the question:

1. Parameterized SQLView layer with the date as a parameter in the SQL query
2. SQLView layer that contained all the dates and used the WMS Time dimension

My municipalities table (lm_municipio_a) had 5570 polygons and the precip table contained 15 years of weekly data. Thus dado_semana_munic had a bit over 4 millions rows.

Results shows that Parameterized SQLView layer has an advantage of ~ 40 milliseconds.

Despite the advantage, since we've already setup our site to use Time Dimension, I'm not sure we will make the change at this point. AFAIK, using Time Dimensions has some advantages such as:

1. Easily round dates the the closest match given a time period (I have weekly data. If user gives a date in the middle of the week, I'd like to show the data for the beginning of the week)
2. No need to worry about validating regex for the SQL parameter.
3. Easily link with the Leaflet time dimension plugin