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?