hanageoservergeotools

How to add SQL HINTs to Geoserver layers?

We have Geoserver 2.21.1, Geotools 27.1 and SAP Hana plugin of Geotools. Data is in SAP Hana and we use quite a complex queries and views in the database.

We have found out that it takes a long time for Hana to optimize some of the queries when using (default) cost based optimization.

I have tried to find out how to use SQL Select hints with Geoserver but no success.

An example ... this is "kind of the sql" Geoserver produces into the database:

SELECT "ID","DISTRICT_ID","DISTRICT_NAME","GEOLOC".ST_AsBinary() as "GEOLOC" 
FROM 
(select ID,DISTRICT_ID,DISTRICT_NAME,GEOLOC FROM MG_SPATIAL.V_GIS_DISTRICT
) as "vtable" 
WHERE "GEOLOC".ST_IntersectsRectPlanar(ST_GeomFromWKB(?, 3067), ST_GeomFromWKB(?, 3067)) = 1 LIMIT 1000000 OFFSET 0

I would like to have Geoserver to use hint OPTIMIZATION_LEVEL (RULE_BASED) when executing this statement, like this:

SELECT "ID","DISTRICT_ID","DISTRICT_NAME","GEOLOC".ST_AsBinary() as "GEOLOC" 
FROM 
(select ID,DISTRICT_ID,DISTRICT_NAME,GEOLOC FROM MG_SPATIAL.V_GIS_DISTRICT
) as "vtable" 
WHERE "GEOLOC".ST_IntersectsRectPlanar(ST_GeomFromWKB(?, 3067), ST_GeomFromWKB(?, 3067)) = 1 LIMIT 1000000 OFFSET 0
WITH HINT OPTIMIZATION_LEVEL (RULE_BASED)

Is it possible to apply hints into SQL like that? If I add the hint into SQL statement in SQL view, the result is like this and it is not what I want:

SELECT "ID","DISTRICT_ID","DISTRICT_NAME","GEOLOC".ST_AsBinary() as "GEOLOC" 
FROM 
(select ID,DISTRICT_ID,DISTRICT_NAME,GEOLOC FROM MG_SPATIAL.V_GIS_DISTRICT
WITH HINT OPTIMIZATION_LEVEL (RULE_BASED) 
) as "vtable" 
WHERE "GEOLOC".ST_IntersectsRectPlanar(ST_GeomFromWKB(?, 3067), ST_GeomFromWKB(?, 3067)) = 1 LIMIT 1000000 OFFSET 0

Any tips anyone?

Solution

  • As Ian already pointed out, there seems to be no out-of-the-box functionality in GeoServer. Generally, I see following options:

    1. Implement a parameter as part of the HANA plug-in (as proposed by Ian): This would mean, that you can set a parameter on store level in GeoServer. All queries issued on this store would use the hint. If you want queries for other layers to not use this hint, you would need to setup a second store without the hint parameter and assign the respective layers to this second store.
    2. Implement a hint parameter as part of the layer: To my knowledge this cannot be achieved by patching the SAP HANA plug-in as the layer is an abstraction of the individual underlying stores.
    3. Set the hint on server side: SAP HANA provides the ability to apply a hint on database side for certain pre-defined queries. In this case, the individual client (GeoServer, in your case) does not need to provide the hint. Note, that this approach will not work when using SQL queries with parameters in the GeoServer layer as the parameters get injected into the sql string by GeoServer.

    To discuss option 1 further, you can also open an issue in the GeoTools Jira and assign the component hana as well as the developer of the HANA plug-in.