SQL queries executing in a loop when reading all features using JdbcFeatureStore

[AndLvovSky]

trafficstars

When calling readAll() method of JdbcFeatureStore the following code executes a new query to get custom properties for each feature:

// Read custom properties for each feature
for (Feature f : mapFP.values()) {
    ps = sqlConn.prepareStatement(getQueryBuilder().getFeatureProperties());
    ps.setString(1, f.getUid());
    rs = ps.executeQuery();
    while (rs.next()) {
        f.addProperty(JDBC_PROPERTY_MAPPER.map(rs));
    }
    closeResultSet(rs);
    rs = null;
    closeStatement(ps);
    ps = null;
}

Therefore if we have 50 features there will be 50+ SQL queries executed, and depending on latency it can be very slow.

Ideally, custom properties retrieval should be done in a single query using a join.

[clun]

haaaa n+1 select issue. you are totally correct. Now readALL() will only be invoked in the administration console so the performance hit is limited. I will mark this as low priority.

It would required a custom mapper and a join both in JdbcFeatureStore and the FeatureStoreSpringJdbc (ff4j-store-springjdbc).