[Geoserver-users] GetMap-request with CQL_FILTER too slow

Roel_De_Nijs · September 28, 2015, 4:35pm

Hi list,

We have a SQL Server 2008 R2 database with a spatial table for every feature type (node, pipe, storagearea,…). Our application has two type of users: the normal user and the geo-limited user. The first one can view all the infrastructure, the latter one is restricted and can only see the infrastructure within a polygon (representing a city or some other area).

To implement this restriction we add a CQL_FILTER to the GetMap-requests. But these requests are much slower than the non-limited requests. I collected some reference data. Because these data is quite verbose (certainly for the geo-limited user), I share just the details of one tile.

So for a normal user, the geoserver request takes 660ms.

···

finished[660] parameters[[FORMAT=image/png, TRANSPARENT=TRUE, SERVICE=WMS, VERSION=1.1.1, REQUEST=GetMap, STYLES=knoopEigenaar,leidingEigenaar,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaatEigenaar,uitlaatEigenaar,knelpuntStatus,reservoirEigenaar,projectToestand, LAYERS=knoop,leiding,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaat,uitlaat,knelpunt,reservoir,project, SRS=EPSG:31370, BBOX=116547.44384766,188202.28271484,116813.06835938,188467.90722656, WIDTH=512, HEIGHT=512]]

But fort he geo-limited user, the geoserver request is 5x slower (3063ms).

finished[3063] parameters[[FORMAT=image/png, TRANSPARENT=TRUE, SERVICE=WMS, VERSION=1.1.1, REQUEST=GetMap, STYLES=knoopEigenaar,leidingEigenaar,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaatEigenaar,uitlaatEigenaar,knelpuntStatus,reservoirEigenaar,projectToestand, LAYERS=knoop,leiding,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaat,uitlaat,knelpunt,reservoir,project, SRS=EPSG:31370, BBOX=116547.44384766,188202.28271484,116813.06835938,188467.90722656, WIDTH=512, HEIGHT=512, CQL_FILTER=INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)))]]

I want to know what could be the cause(s) of this performance issue. And if I can make some changes to improve performance. Any pointers, hints,… are more than welcome. I take anything I can get And if it is a really specific issue, I’ll try debugging some geoserver code.

Kind regards,

Roel De Nijs

Senior Java Developer

Andrea_Aime4 · September 28, 2015, 4:46pm

Hi,
have you tried forcing the spatial index usage, it’s a flag in the store configuration (if you have
a GeoServer recent enough… 2.6.x or 2.7.x, cannot remember when exactly we added those)

Cheers
Andrea

···

On Mon, Sep 28, 2015 at 6:35 PM, Roel De Nijs <roel.denijs@anonymised.com> wrote:

Hi list,

We have a SQL Server 2008 R2 database with a spatial table for every feature type (node, pipe, storagearea,…). Our application has two type of users: the normal user and the geo-limited user. The first one can view all the infrastructure, the latter one is restricted and can only see the infrastructure within a polygon (representing a city or some other area).

To implement this restriction we add a CQL_FILTER to the GetMap-requests. But these requests are much slower than the non-limited requests. I collected some reference data. Because these data is quite verbose (certainly for the geo-limited user), I share just the details of one tile.

So for a normal user, the geoserver request takes 660ms.
finished[660] parameters[[FORMAT=image/png, TRANSPARENT=TRUE, SERVICE=WMS, VERSION=1.1.1, REQUEST=GetMap, STYLES=knoopEigenaar,leidingEigenaar,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaatEigenaar,uitlaatEigenaar,knelpuntStatus,reservoirEigenaar,projectToestand, LAYERS=knoop,leiding,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaat,uitlaat,knelpunt,reservoir,project, SRS=EPSG:31370, BBOX=116547.44384766,188202.28271484,116813.06835938,188467.90722656, WIDTH=512, HEIGHT=512]]
But fort he geo-limited user, the geoserver request is 5x slower (3063ms).

finished[3063] parameters[[FORMAT=image/png, TRANSPARENT=TRUE, SERVICE=WMS, VERSION=1.1.1, REQUEST=GetMap, STYLES=knoopEigenaar,leidingEigenaar,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaatEigenaar,uitlaatEigenaar,knelpuntStatus,reservoirEigenaar,projectToestand, LAYERS=knoop,leiding,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaat,uitlaat,knelpunt,reservoir,project, SRS=EPSG:31370, BBOX=116547.44384766,188202.28271484,116813.06835938,188467.90722656, WIDTH=512, HEIGHT=512, CQL_FILTER=INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)))]]

I want to know what could be the cause(s) of this performance issue. And if I can make some changes to improve performance. Any pointers, hints,… are more than welcome. I take anything I can get And if it is a really specific issue, I’ll try debugging some geoserver code.

Kind regards,

Roel De Nijs

Senior Java Developer

Volg Aquafin op Facebook | Twitter | YouTube | LinkedIN

Disclaimer: zie www.aquafin.be P Denk aan het milieu. Druk deze mail niet onnodig af.

Geoserver-users mailing list
Geoserver-users@anonymised.comsts.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

–

==
GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V for more information.

Ing. Andrea Aime

@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

Roel_De_Nijs · September 29, 2015, 5:23pm

In development we are currently using GeoServer 2.7.2 so I turned on the flag to enforce the usage of the spatial index. I checked our Geoserver 2.6.2 instance in UA and the flag exists there as well.

After changing the flag, I restarted the Tomcat server (just to be sure), but it didn’t make any difference. I monitored the executed queries (using SQL Server Profiler) and I didn’t notice any hints being set to enfource spatial index usage. So turning this flag on or off doesn’t seem to make any difference. But if I check the execution plan of those queries (even before setting the “enforce spatial index” to true), I notice the spatial index is being used.

Here’s the data for the same request with the “enforcing spatial index” flag set to true. So it still takes 3110ms (which is similar with the flag set to false).

finished[3110] parameters[[FORMAT=image/png, TRANSPARENT=TRUE, SERVICE=WMS, VERSION=1.1.1, REQUEST=GetMap, STYLES=knoopEigenaar,leidingEigenaar,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaatEigenaar,uitlaatEigenaar,knelpuntStatus,reservoirEigenaar,projectToestand, LAYERS=knoop,leiding,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaat,uitlaat,knelpunt,reservoir,project, SRS=EPSG:31370, BBOX=116547.44384766,188202.28271484,116813.06835938,188467.90722656, WIDTH=512, HEIGHT=512, CQL_FILTER=INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)))]]

Kind regards,

Roel De Nijs

Senior Java Developer

Verzonden: maandag 28 september 2015 18:47

···

Hi,

have you tried forcing the spatial index usage, it’s a flag in the store configuration (if you have

a GeoServer recent enough… 2.6.x or 2.7.x, cannot remember when exactly we added those)

Cheers

Andrea

On Mon, Sep 28, 2015 at 6:35 PM, Roel De Nijs <roel.denijs@…5173…> wrote:

Hi list,

We have a SQL Server 2008 R2 database with a spatial table for every feature type (node, pipe, storagearea,…). Our application has two type of users: the normal user and the geo-limited user. The first one can view all the infrastructure, the latter one is restricted and can only see the infrastructure within a polygon (representing a city or some other area).

To implement this restriction we add a CQL_FILTER to the GetMap-requests. But these requests are much slower than the non-limited requests. I collected some reference data. Because these data is quite verbose (certainly for the geo-limited user), I share just the details of one tile.

So for a normal user, the geoserver request takes 660ms.
finished[660] parameters[[FORMAT=image/png, TRANSPARENT=TRUE, SERVICE=WMS, VERSION=1.1.1, REQUEST=GetMap, STYLES=knoopEigenaar,leidingEigenaar,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaatEigenaar,uitlaatEigenaar,knelpuntStatus,reservoirEigenaar,projectToestand, LAYERS=knoop,leiding,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaat,uitlaat,knelpunt,reservoir,project, SRS=EPSG:31370, BBOX=116547.44384766,188202.28271484,116813.06835938,188467.90722656, WIDTH=512, HEIGHT=512]]
But fort he geo-limited user, the geoserver request is 5x slower (3063ms).

finished[3063] parameters[[FORMAT=image/png, TRANSPARENT=TRUE, SERVICE=WMS, VERSION=1.1.1, REQUEST=GetMap, STYLES=knoopEigenaar,leidingEigenaar,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaatEigenaar,uitlaatEigenaar,knelpuntStatus,reservoirEigenaar,projectToestand, LAYERS=knoop,leiding,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaat,uitlaat,knelpunt,reservoir,project, SRS=EPSG:31370, BBOX=116547.44384766,188202.28271484,116813.06835938,188467.90722656, WIDTH=512, HEIGHT=512, CQL_FILTER=INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)))]]

I want to know what could be the cause(s) of this performance issue. And if I can make some changes to improve performance. Any pointers, hints,… are more than welcome. I take anything I can get And if it is a really specific issue, I’ll try debugging some geoserver code.

Kind regards,

Roel De Nijs

Senior Java Developer

Volg Aquafin op Facebook | Twitter | YouTube | LinkedIN

Disclaimer: zie www.aquafin.be P Denk aan het milieu. Druk deze mail niet onnodig af.

Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

–

==

GeoServer Professional Services from the experts! Visit

http://goo.gl/it488V for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.

Via Poggio alle Viti 1187

55054 Massarosa (LU)

Italy

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

Andrea_Aime4 · September 29, 2015, 5:31pm

Hi Roel,
this does not sound right, we have tests in the build checking that the hints are added, when activated they should look something like

… FROM schema.road WITH(INDEX(_road_geometry_index)) …

I don’t have a SQL Server handy right now to make an interactive test though.

Cheers
Andrea

···

On Tue, Sep 29, 2015 at 7:23 PM, Roel De Nijs <roel.denijs@anonymised.com> wrote:

In development we are currently using GeoServer 2.7.2 so I turned on the flag to enforce the usage of the spatial index. I checked our Geoserver 2.6.2 instance in UA and the flag exists there as well.

After changing the flag, I restarted the Tomcat server (just to be sure), but it didn’t make any difference. I monitored the executed queries (using SQL Server Profiler) and I didn’t notice any hints being set to enfource spatial index usage. So turning this flag on or off doesn’t seem to make any difference. But if I check the execution plan of those queries (even before setting the “enforce spatial index” to true), I notice the spatial index is being used.

Here’s the data for the same request with the “enforcing spatial index” flag set to true. So it still takes 3110ms (which is similar with the flag set to false).

finished[3110] parameters[[FORMAT=image/png, TRANSPARENT=TRUE, SERVICE=WMS, VERSION=1.1.1, REQUEST=GetMap, STYLES=knoopEigenaar,leidingEigenaar,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaatEigenaar,uitlaatEigenaar,knelpuntStatus,reservoirEigenaar,projectToestand, LAYERS=knoop,leiding,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaat,uitlaat,knelpunt,reservoir,project, SRS=EPSG:31370, BBOX=116547.44384766,188202.28271484,116813.06835938,188467.90722656, WIDTH=512, HEIGHT=512, CQL_FILTER=INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)))]]

Kind regards,

Roel De Nijs

Senior Java Developer

Van: andrea.aime@anonymised.com… [mailto:andrea.aime@anonymised.com] Namens Andrea Aime
Verzonden: maandag 28 september 2015 18:47
Aan: Roel De Nijs <roel.denijs@anonymised.com>
CC: geoserver-users@lists.sourceforge.net
Onderwerp: Re: [Geoserver-users] GetMap-request with CQL_FILTER too slow

Hi,

have you tried forcing the spatial index usage, it’s a flag in the store configuration (if you have

a GeoServer recent enough… 2.6.x or 2.7.x, cannot remember when exactly we added those)

Cheers

Andrea

On Mon, Sep 28, 2015 at 6:35 PM, Roel De Nijs <roel.denijs@anonymised.com73…> wrote:
Hi list,

We have a SQL Server 2008 R2 database with a spatial table for every feature type (node, pipe, storagearea,…). Our application has two type of users: the normal user and the geo-limited user. The first one can view all the infrastructure, the latter one is restricted and can only see the infrastructure within a polygon (representing a city or some other area).

To implement this restriction we add a CQL_FILTER to the GetMap-requests. But these requests are much slower than the non-limited requests. I collected some reference data. Because these data is quite verbose (certainly for the geo-limited user), I share just the details of one tile.

So for a normal user, the geoserver request takes 660ms.
finished[660] parameters[[FORMAT=image/png, TRANSPARENT=TRUE, SERVICE=WMS, VERSION=1.1.1, REQUEST=GetMap, STYLES=knoopEigenaar,leidingEigenaar,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaatEigenaar,uitlaatEigenaar,knelpuntStatus,reservoirEigenaar,projectToestand, LAYERS=knoop,leiding,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaat,uitlaat,knelpunt,reservoir,project, SRS=EPSG:31370, BBOX=116547.44384766,188202.28271484,116813.06835938,188467.90722656, WIDTH=512, HEIGHT=512]]
But fort he geo-limited user, the geoserver request is 5x slower (3063ms).

finished[3063] parameters[[FORMAT=image/png, TRANSPARENT=TRUE, SERVICE=WMS, VERSION=1.1.1, REQUEST=GetMap, STYLES=knoopEigenaar,leidingEigenaar,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaatEigenaar,uitlaatEigenaar,knelpuntStatus,reservoirEigenaar,projectToestand, LAYERS=knoop,leiding,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaat,uitlaat,knelpunt,reservoir,project, SRS=EPSG:31370, BBOX=116547.44384766,188202.28271484,116813.06835938,188467.90722656, WIDTH=512, HEIGHT=512, CQL_FILTER=INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)))]]

I want to know what could be the cause(s) of this performance issue. And if I can make some changes to improve performance. Any pointers, hints,… are more than welcome. I take anything I can get And if it is a really specific issue, I’ll try debugging some geoserver code.

Kind regards,

Roel De Nijs

Senior Java Developer

Volg Aquafin op Facebook | Twitter | YouTube | LinkedIN

Disclaimer: zie www.aquafin.be P Denk aan het milieu. Druk deze mail niet onnodig af.

Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
–

==

GeoServer Professional Services from the experts! Visit

http://goo.gl/it488V for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.

Via Poggio alle Viti 1187

55054 Massarosa (LU)

Italy

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

–

==
GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V for more information.

Ing. Andrea Aime

@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

Roel_De_Nijs · September 29, 2015, 5:50pm

Hi Andrea,

Is this hint added for every spatial query send to the database for every version of sql server? We are using sql server 2008 R2 and the queries sent by geoserver have a Filter and STIntersects clause.

If the hint should be added to these queries on this sql server version, I’ll do some further investigation tomorrow. But now it’s Champions League time

Kind regards,

Roel

Verzonden: dinsdag 29 september 2015 19:31

···

Hi Roel,

this does not sound right, we have tests in the build checking that the hints are added, when activated they should look something like

… FROM schema.road WITH(INDEX(_road_geometry_index)) …

I don’t have a SQL Server handy right now to make an interactive test though.

Cheers

Andrea

On Tue, Sep 29, 2015 at 7:23 PM, Roel De Nijs <roel.denijs@…5173…> wrote:

In development we are currently using GeoServer 2.7.2 so I turned on the flag to enforce the usage of the spatial index. I checked our Geoserver 2.6.2 instance in UA and the flag exists there as well.

After changing the flag, I restarted the Tomcat server (just to be sure), but it didn’t make any difference. I monitored the executed queries (using SQL Server Profiler) and I didn’t notice any hints being set to enfource spatial index usage. So turning this flag on or off doesn’t seem to make any difference. But if I check the execution plan of those queries (even before setting the “enforce spatial index” to true), I notice the spatial index is being used.

Here’s the data for the same request with the “enforcing spatial index” flag set to true. So it still takes 3110ms (which is similar with the flag set to false).

finished[3110] parameters[[FORMAT=image/png, TRANSPARENT=TRUE, SERVICE=WMS, VERSION=1.1.1, REQUEST=GetMap, STYLES=knoopEigenaar,leidingEigenaar,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaatEigenaar,uitlaatEigenaar,knelpuntStatus,reservoirEigenaar,projectToestand, LAYERS=knoop,leiding,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaat,uitlaat,knelpunt,reservoir,project, SRS=EPSG:31370, BBOX=116547.44384766,188202.28271484,116813.06835938,188467.90722656, WIDTH=512, HEIGHT=512, CQL_FILTER=INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)))]]

Kind regards,

Roel De Nijs

Senior Java Developer

Van: andrea.aime@…84… [mailto:andrea.aime@…84…] Namens Andrea Aime
Verzonden: maandag 28 september 2015 18:47
Aan: Roel De Nijs <roel.denijs@…5173…>
CC: geoserver-users@lists.sourceforge.net
Onderwerp: Re: [Geoserver-users] GetMap-request with CQL_FILTER too slow

Hi,

have you tried forcing the spatial index usage, it’s a flag in the store configuration (if you have

a GeoServer recent enough… 2.6.x or 2.7.x, cannot remember when exactly we added those)

Cheers

Andrea

On Mon, Sep 28, 2015 at 6:35 PM, Roel De Nijs <roel.denijs@…5173…> wrote:
Hi list,

We have a SQL Server 2008 R2 database with a spatial table for every feature type (node, pipe, storagearea,…). Our application has two type of users: the normal user and the geo-limited user. The first one can view all the infrastructure, the latter one is restricted and can only see the infrastructure within a polygon (representing a city or some other area).

To implement this restriction we add a CQL_FILTER to the GetMap-requests. But these requests are much slower than the non-limited requests. I collected some reference data. Because these data is quite verbose (certainly for the geo-limited user), I share just the details of one tile.

So for a normal user, the geoserver request takes 660ms.
finished[660] parameters[[FORMAT=image/png, TRANSPARENT=TRUE, SERVICE=WMS, VERSION=1.1.1, REQUEST=GetMap, STYLES=knoopEigenaar,leidingEigenaar,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaatEigenaar,uitlaatEigenaar,knelpuntStatus,reservoirEigenaar,projectToestand, LAYERS=knoop,leiding,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaat,uitlaat,knelpunt,reservoir,project, SRS=EPSG:31370, BBOX=116547.44384766,188202.28271484,116813.06835938,188467.90722656, WIDTH=512, HEIGHT=512]]
But fort he geo-limited user, the geoserver request is 5x slower (3063ms).

finished[3063] parameters[[FORMAT=image/png, TRANSPARENT=TRUE, SERVICE=WMS, VERSION=1.1.1, REQUEST=GetMap, STYLES=knoopEigenaar,leidingEigenaar,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaatEigenaar,uitlaatEigenaar,knelpuntStatus,reservoirEigenaar,projectToestand, LAYERS=knoop,leiding,terugslagklep,overstort,afsluiter,knijpconstructie,pomp,wervelventiel,inlaat,uitlaat,knelpunt,reservoir,project, SRS=EPSG:31370, BBOX=116547.44384766,188202.28271484,116813.06835938,188467.90722656, WIDTH=512, HEIGHT=512, CQL_FILTER=INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)));INTERSECTS(“geom”, POLYGON ((116547.44384766 188202.28271484, 116547.44384766 188467.90722656, 116813.06835938 188467.90722656, 116813.06835938 188202.28271484, 116547.44384766 188202.28271484)))]]

I want to know what could be the cause(s) of this performance issue. And if I can make some changes to improve performance. Any pointers, hints,… are more than welcome. I take anything I can get And if it is a really specific issue, I’ll try debugging some geoserver code.

Kind regards,

Roel De Nijs

Senior Java Developer

Volg Aquafin op Facebook | Twitter | YouTube | LinkedIN

Disclaimer: zie www.aquafin.be P Denk aan het milieu. Druk deze mail niet onnodig af.

Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
–

==

GeoServer Professional Services from the experts! Visit

http://goo.gl/it488V for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.

Via Poggio alle Viti 1187

55054 Massarosa (LU)

Italy

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

–

==

GeoServer Professional Services from the experts! Visit

http://goo.gl/it488V for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.

Via Poggio alle Viti 1187

55054 Massarosa (LU)

Italy

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

Andrea_Aime4 · September 29, 2015, 6:15pm

On Tue, Sep 29, 2015 at 7:50 PM, Roel De Nijs <roel.denijs@anonymised.com>
wrote:

Hi Andrea,

Is this hint added for every spatial query send to the database for every
version of sql server? We are using sql server 2008 R2 and the queries sent
by geoserver have a Filter and STIntersects clause.

It cannot be applied against sql views, but on real tables it should work.
Can you share the actual sql query? The log above was not sql

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

Roel_De_Nijs · September 30, 2015, 2:03pm

All our layers in this application use real tables. And from the SQL Server Profiler here is one of the queries being executed

SELECT “uid”,“node_type”,“owner”,“geom”.STAsBinary() as “geom”

FROM “dbo”.“spatial_node”

WHERE (“geom”.Filter(geometry::STGeomFromText(‘POLYGON ((79625.63671875 202014.75732422, 79625.63671875 202280.38183594, 79891.261230469 202280.38183594, 79891.261230469 202014.75732422, 79625.63671875 202014.75732422))’, 31370)) = 1

AND “geom”.STIntersects(geometry::STGeomFromText(‘POLYGON ((79625.63671875 202014.75732422, 79625.63671875 202280.38183594, 79891.261230469 202280.38183594, 79891.261230469 202014.75732422, 79625.63671875 202014.75732422))’, 31370)) = 1

AND “geom”.Filter(geometry::STGeomFromText(‘POLYGON ((79619.92994213104 202009.05054760101, 79619.92994213104 202286.088612559, 79896.96800708795 202286.088612559, 79896.96800708795 202009.05054760101, 79619.92994213104 202009.05054760101))’, 31370)) = 1)

And to be absolutely sure, I verified the spatial index flag of the store again and it’s definitely true

true

Kind regards,

Roel De Nijs

Senior Java Developer

Verzonden: dinsdag 29 september 2015 20:16

···

On Tue, Sep 29, 2015 at 7:50 PM, Roel De Nijs <roel.denijs@…5173…> wrote:

Hi Andrea,

Is this hint added for every spatial query send to the database for every version of sql server? We are using sql server 2008 R2 and the queries sent by geoserver have a Filter and STIntersects clause.

It cannot be applied against sql views, but on real tables it should work.

Can you share the actual sql query? The log above was not sql

Cheers

Andrea

–

==

GeoServer Professional Services from the experts! Visit

http://goo.gl/it488V for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.

Via Poggio alle Viti 1187

55054 Massarosa (LU)

Italy

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

Andrea_Aime4 · September 30, 2015, 4:16pm

Hi Roel,
I see in another thread that you might be in a hurry… but I have no spare time to dedicate to this in the short term.
I’d suggest you have a look at the code in the sql server store, test the latest versions of the software to make
sure the issue is still there, and prepare a pull request.
Bonus points if you can also check a Sql server 2012 installation and verify that changing the query speeds up
(or at least, does not damage) that version too.

The classes of interest to you should all be in this package:
https://github.com/geotools/geotools/tree/master/modules/plugin/jdbc/jdbc-sqlserver/src/main/java/org/geotools/data/sqlserver

Cheers
Andrea

···

On Wed, Sep 30, 2015 at 4:03 PM, Roel De Nijs <roel.denijs@anonymised.com> wrote:

All our layers in this application use real tables. And from the SQL Server Profiler here is one of the queries being executed

SELECT “uid”,“node_type”,“owner”,“geom”.STAsBinary() as “geom”

FROM “dbo”.“spatial_node”

WHERE (“geom”.Filter(geometry::STGeomFromText(‘POLYGON ((79625.63671875 202014.75732422, 79625.63671875 202280.38183594, 79891.261230469 202280.38183594, 79891.261230469 202014.75732422, 79625.63671875 202014.75732422))’, 31370)) = 1

AND “geom”.STIntersects(geometry::STGeomFromText(‘POLYGON ((79625.63671875 202014.75732422, 79625.63671875 202280.38183594, 79891.261230469 202280.38183594, 79891.261230469 202014.75732422, 79625.63671875 202014.75732422))’, 31370)) = 1

AND “geom”.Filter(geometry::STGeomFromText(‘POLYGON ((79619.92994213104 202009.05054760101, 79619.92994213104 202286.088612559, 79896.96800708795 202286.088612559, 79896.96800708795 202009.05054760101, 79619.92994213104 202009.05054760101))’, 31370)) = 1)

And to be absolutely sure, I verified the spatial index flag of the store again and it’s definitely true

true

Kind regards,

Roel De Nijs

Senior Java Developer

Van: andrea.aime@anonymised.com… [mailto:andrea.aime@anonymised.com] Namens Andrea Aime
Verzonden: dinsdag 29 september 2015 20:16
Aan: Roel De Nijs <roel.denijs@anonymised.com>
CC: geoserver-users@lists.sourceforge.net
Onderwerp: Re: [Geoserver-users] GetMap-request with CQL_FILTER too slow

On Tue, Sep 29, 2015 at 7:50 PM, Roel De Nijs <roel.denijs@anonymised.com73…> wrote:

Hi Andrea,

Is this hint added for every spatial query send to the database for every version of sql server? We are using sql server 2008 R2 and the queries sent by geoserver have a Filter and STIntersects clause.

It cannot be applied against sql views, but on real tables it should work.

Can you share the actual sql query? The log above was not sql

Cheers

Andrea

–

==

GeoServer Professional Services from the experts! Visit

http://goo.gl/it488V for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.

Via Poggio alle Viti 1187

55054 Massarosa (LU)

Italy

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

Volg Aquafin op Facebook | Twitter | YouTube | LinkedIN

Disclaimer: zie www.aquafin.be P Denk aan het milieu. Druk deze mail niet onnodig af.

–

==
GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V for more information.

Ing. Andrea Aime

@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

Roel_De_Nijs · September 30, 2015, 5:02pm

Hi Andrea,

Luckily this issue only affects a very minor group of users. That’s also why this issue could be left untouched for half a year. If the majority of our users would have been affected, that would never have been possible Now the new version of our application is in user-acceptance and I have a few days available to (hopefully) get this sorted out.

To be honest I don’t know if it’s actually the query which is causing this performance issue. Because from my impressions these queries aren’t running slow. When I look at the queries using the SQL Server profiler, execution time is most of the time not exceeding 200ms (which seems reasonable to me). And if I execute such a query manually (using SQL Server Management Studio), it’s always showing the results immediately, never have to wait for seconds.

So I did another test to gather some reference data.

1/ I send the request for just one tile and monitor execution time for the query and the actual GetMap-request. To generate one tile the data of 13 different layers is required. For a normal user all queries are executed in 60ms and the request takes 118ms. For a restricted user these numbers are similar with 87ms and 145ms respectively.

2/ I do the same, but now for the whole map. For one map 15 512x512 tiles need to be generated. Now for the normal user the queries are executed in ± 2 seconds. That’s the gross time (I didn’t make the effort to add the execution time of each query individually). And these are the execution times for the different GetMap-requests

finished[522]

finished[530]

finished[796]

finished[446]

finished[1049]

finished[1101]

finished[1112]

finished[894]

finished[612]

finished[529]

finished[932]

finished[753]

finished[816]

finished[469]

finished[399]

Then I did the same for the restricted user and the gross duration for all queries is almost 6 seconds (but as I said before, the execution times of each query is similar to the normal user, I don’t see any significant performance issue here). But if you then look at the execution times for the different GetMap-requests, the difference is significant

finished[230]

finished[3355]

finished[3368]

finished[3434]

finished[3537]

finished[3554]

finished[203]

finished[285]

finished[3667]

finished[405]

finished[343]

finished[2212]

finished[2224]

finished[2299]

So it seems that when you use a CQL_FILTER there is some kind of additional processing of the returned features from the database. And if no features are returned, then no additional processing is required (which would explain why some of the GetMap requests finish rather quickly). Do these thoughts make any sense at all?

Kind regards,

Roel De Nijs

Senior Java Developer

Verzonden: woensdag 30 september 2015 18:16

···

Hi Roel,

I see in another thread that you might be in a hurry… but I have no spare time to dedicate to this in the short term.

I’d suggest you have a look at the code in the sql server store, test the latest versions of the software to make

sure the issue is still there, and prepare a pull request.

Bonus points if you can also check a Sql server 2012 installation and verify that changing the query speeds up

(or at least, does not damage) that version too.

The classes of interest to you should all be in this package:

https://github.com/geotools/geotools/tree/master/modules/plugin/jdbc/jdbc-sqlserver/src/main/java/org/geotools/data/sqlserver

Cheers

Andrea

On Wed, Sep 30, 2015 at 4:03 PM, Roel De Nijs <roel.denijs@…5173…> wrote:

All our layers in this application use real tables. And from the SQL Server Profiler here is one of the queries being executed

SELECT “uid”,“node_type”,“owner”,“geom”.STAsBinary() as “geom”

FROM “dbo”.“spatial_node”

WHERE (“geom”.Filter(geometry::STGeomFromText(‘POLYGON ((79625.63671875 202014.75732422, 79625.63671875 202280.38183594, 79891.261230469 202280.38183594, 79891.261230469 202014.75732422, 79625.63671875 202014.75732422))’, 31370)) = 1

AND “geom”.STIntersects(geometry::STGeomFromText(‘POLYGON ((79625.63671875 202014.75732422, 79625.63671875 202280.38183594, 79891.261230469 202280.38183594, 79891.261230469 202014.75732422, 79625.63671875 202014.75732422))’, 31370)) = 1

AND “geom”.Filter(geometry::STGeomFromText(‘POLYGON ((79619.92994213104 202009.05054760101, 79619.92994213104 202286.088612559, 79896.96800708795 202286.088612559, 79896.96800708795 202009.05054760101, 79619.92994213104 202009.05054760101))’, 31370)) = 1)

And to be absolutely sure, I verified the spatial index flag of the store again and it’s definitely true

true

Kind regards,

Roel De Nijs

Senior Java Developer

Van: andrea.aime@…84… [mailto:andrea.aime@…84…] Namens Andrea Aime
Verzonden: dinsdag 29 september 2015 20:16
Aan: Roel De Nijs <roel.denijs@…5173…>
CC: geoserver-users@lists.sourceforge.net
Onderwerp: Re: [Geoserver-users] GetMap-request with CQL_FILTER too slow

On Tue, Sep 29, 2015 at 7:50 PM, Roel De Nijs <roel.denijs@…5173…> wrote:

Hi Andrea,

Is this hint added for every spatial query send to the database for every version of sql server? We are using sql server 2008 R2 and the queries sent by geoserver have a Filter and STIntersects clause.

It cannot be applied against sql views, but on real tables it should work.

Can you share the actual sql query? The log above was not sql

Cheers

Andrea

–

==

GeoServer Professional Services from the experts! Visit

http://goo.gl/it488V for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.

Via Poggio alle Viti 1187

55054 Massarosa (LU)

Italy

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

Volg Aquafin op Facebook | Twitter | YouTube | LinkedIN

Disclaimer: zie www.aquafin.be P Denk aan het milieu. Druk deze mail niet onnodig af.

–

==

GeoServer Professional Services from the experts! Visit

http://goo.gl/it488V for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.

Via Poggio alle Viti 1187

55054 Massarosa (LU)

Italy

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

Andrea_Aime4 · September 30, 2015, 5:08pm

On Wed, Sep 30, 2015 at 7:02 PM, Roel De Nijs <roel.denijs@anonymised.com>
wrote:

Hi Andrea,

Luckily this issue only affects a very minor group of users. That’s also
why this issue could be left untouched for half a year. If the majority of
our users would have been affected, that would never have been possible

In an ideal world, that would be true... in practice an issue is fixed when
there is someone willing to spend time or funds on it, which does not
always correlate well with the number of users affected, believe me

So it seems that when you use a CQL_FILTER there is some kind of
additional processing of the returned features from the database. And if no
features are returned, then no additional processing is required (which
would explain why some of the GetMap requests finish rather quickly). Do
these thoughts make any sense at all?

I'm not aware of any extra processing. But beware, running queries manually
and via JDBC can sometimes lead to very different
results. Especially when done like GeoServer does, but setting a fetch size
to avoid getting millions of records on the client
side in a single shot and going OOM as a result

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

Roel_De_Nijs · October 1, 2015, 4:33pm

Today me and the SQL Server DataStore plugin spent some quality time together. And we definitely had some fun! Here’s an overview of my discoveries. All these tests are executed using a SQL Server 2008 R2 and the SQL Server DataStore 13.x version. I can’t perform similar tests against another version of SQL Server fort he simple reason I don’t have one available

First of all I looked why the “enforcing spatial index” hint isn’t added.to the query. The code verifies if spatial attributes are used in the filter. If it is, then the occurences for each spatial attribute in the filter are counted. Only if there’s one spatial attribute which occurs just once in the filter, the “enforcing spatial index” hint is added to the query. This is the appropriate code snippet from SQLServerDialect.getSpatialIndexes:

// we can only apply one index on one condition

if(attribute.getValue() > 1) {

continue;

}

Then I looked at the performance issue we are experiencing. So to render our (OpenLayers) map 15 GetMap-requests are sent to GeoServer and for every tile the data of 13 layers (tables) is required. Each request has a CQL_FILTER with an Intersects. I always used the same scenario to compare the results and it’s pretty simple: rendering a map on a few different zoom levels. I’ll only focus on the lowest zoom leve here, because performance is the worst at this zoom level. I used SQL Server Profiler to gather all sql query related information.

Using the original sql server plugin

Using the original sql server plugin, this kind of query is generated and executed:

SELECT “uid”,“node_type”,“owner”,“geom”.STAsBinary() as “geom”

FROM “dbo”.“spatial_node”

WHERE (“geom”.Filter(geometry::STGeomFromText(‘POLYGON ((155859.87158203 202811.63085938, 155859.87158203 203077.25537109, 156125.49609375 203077.25537109, 156125.49609375 202811.63085938, 155859.87158203 202811.63085938))’, 31370)) = 1

AND “geom”.STIntersects(geometry::STGeomFromText(‘POLYGON ((155859.87158203 202811.63085938, 155859.87158203 203077.25537109, 156125.49609375 203077.25537109, 156125.49609375 202811.63085938, 155859.87158203 202811.63085938))’, 31370)) = 1

AND “geom”.Filter(geometry::STGeomFromText(‘POLYGON ((155854.164805411 202805.92408276122, 155854.164805411 203082.96214770878, 156131.202870369 203082.96214770878, 156131.202870369 202805.92408276122, 155854.164805411 202805.92408276122))’, 31370)) = 1)

So in the WHERE clause you have 3 spatial functions: one Filter and one STIntersects (from the CQL_FILTER parameter); and another Filter (from the BBOX parameter). Each of these queries take up to 250-300ms. And the average time to execute a GetMap-request (at this zoom level) is 3450ms.

Only STIntersects (CQL_FILTER) and Filter (BBOX)

For this test, I changed the sql server plugin so that it only generates a STIntersects (from the CQL_FILTER parameter) and a Filter (from the BBOX parameter). The query that’s generated and executed looks like:

SELECT “uid”,“node_type”,“owner”,“geom”.STAsBinary() as “geom”

FROM “dbo”.“spatial_node”

WHERE (“geom”.STIntersects(geometry::STGeomFromText(‘POLYGON ((155859.87158203 202811.63085938, 155859.87158203 203077.25537109, 156125.49609375 203077.25537109, 156125.49609375 202811.63085938, 155859.87158203 202811.63085938))’, 31370)) = 1

AND “geom”.Filter(geometry::STGeomFromText(‘POLYGON ((155854.164805411 202805.92408276122, 155854.164805411 203082.96214770878, 156131.202870369 203082.96214770878, 156131.202870369 202805.92408276122, 155854.164805411 202805.92408276122))’, 31370)) = 1)

So in the WHERE clause you now have only 2 spatial functions. Each of these queries execute in 100-200ms! That’s already a huge performance boost. And that’s already noticable if you look at the average time to execute a GetMap-request (at this zoom level): it takes only 1900ms. So almost 50% faster.

Only STIntersects (CQL_FILTER)

At this zoom level the polygon in the CQL_FILTER parameter is almost the same as the BBOX polygon (that’s because our application generates the polygon in the CQL_FILTER by intersecting the user’s restricted geographical area with the BBOX parameter). So the Filter condition (from the BBOX parameter) becomes redundant in this case. For this test, I changed the sql server plugin so that it only generates a STIntersects (from the CQL_FILTER parameter). The query that’s generated and executed looks like:

SELECT “uid”,“node_type”,“owner”,“geom”.STAsBinary() as “geom”

FROM “dbo”.“spatial_node”

WHERE (“geom”.STIntersects(geometry::STGeomFromText(‘POLYGON ((152938.00195313 202811.63085938, 152938.00195313 203874.12890625, 154000.5 203874.12890625, 154000.5 202811.63085938, 152938.00195313 202811.63085938))’, 31370)) = 1

AND 1 = 1)

So in the WHERE clause you now have only 1 spatial function. Each of these queries execute in 20-50ms! Which is again a huge performance improvement. Needless to say that the average time to execute a GetMap-request (at this zoom level) will be less as well: only 1400ms.

Conclusion: so it seems for SQL Server 2008 R2 it would be a good thing to get rid of the additional Filter condition if you have an Intersects in the CQL_FILTER (and probably for the other spatial functions as well).

Kind regards,

Roel De Nijs

Senior Java Developer

Verzonden: woensdag 30 september 2015 19:08

···

On Wed, Sep 30, 2015 at 7:02 PM, Roel De Nijs <roel.denijs@…5173…> wrote:

Hi Andrea,

Luckily this issue only affects a very minor group of users. That’s also why this issue could be left untouched for half a year. If the majority of our users would have been affected, that would never have been possible

In an ideal world, that would be true… in practice an issue is fixed when there is someone willing to spend time or funds on it, which does not always correlate well with the number of users affected, believe me

So it seems that when you use a CQL_FILTER there is some kind of additional processing of the returned features from the database. And if no features are returned, then no additional processing is required (which would explain why some of the GetMap requests finish rather quickly). Do these thoughts make any sense at all?

I’m not aware of any extra processing. But beware, running queries manually and via JDBC can sometimes lead to very different

results. Especially when done like GeoServer does, but setting a fetch size to avoid getting millions of records on the client

side in a single shot and going OOM as a result

Cheers

Andrea

–

==

GeoServer Professional Services from the experts! Visit

http://goo.gl/it488V for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.

Via Poggio alle Viti 1187

55054 Massarosa (LU)

Italy

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

Andrea_Aime4 · October 1, 2015, 7:03pm

On Thu, Oct 1, 2015 at 6:33 PM, Roel De Nijs <roel.denijs@anonymised.com> wrote:

*Conclusion:* so it seems for SQL Server 2008 R2 it would be a good
thing to get rid of the additional Filter condition if you have an
Intersects in the CQL_FILTER (and probably for the other spatial functions
as well).

Hum... a simple "forget the rest if there is a intersect" cannot be
applied, because of code genericity. In particular:
* Doing so at the WMS level would incur in quite a bit of overhead for more
intellingent databases, especially if the intersection is covering a larger
area
* Doing so at the store level would make WFS responses invalid, because all
filters need to be applied

There is however a way, if you want to venture in some logic filtering
simplification, which is to modify the
SimplifyingFilterVisitor (
https://github.com/geotools/geotools/blob/master/modules/library/main/src/main/java/org/geotools/filter/visitor/SimplifyingFilterVisitor.java
)
so that it recognizes the case of multiple spatial filters and-ed
toghether, and simplifies them into a single
equivalent spatial expression (e.g. you take all bboxes and polygons and
intersect them)

If you can make a pull request with that kind of change, plus tests in
SimplifyingFilterVisitorTest (
https://github.com/geotools/geotools/blob/master/modules/library/main/src/test/java/org/geotools/filter/visitor/SimplifyingFilterVisitorTest.java
)
to cover the new simplification paths, there is a good chance the pull
request is going to be accepted.

Also do check the contribution guidelines here:

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

Andrea_Aime4 · October 1, 2015, 7:04pm

On Thu, Oct 1, 2015 at 9:03 PM, Andrea Aime <andrea.aime@anonymised.com>
wrote:

Also do check the contribution guidelines here:

https://github.com/geotools/geotools/blob/master/CONTRIBUTING.md

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

Roel_De_Nijs · October 2, 2015, 1:49pm

I’ll see what I can do. Because using an excellent geospatial product like GeoServer with very limited geospatial knowledge is one thing, making actual changes to the code is something completely different

Verzonden: donderdag 1 oktober 2015 21:04

···

On Thu, Oct 1, 2015 at 6:33 PM, Roel De Nijs <roel.denijs@…5173…> wrote:

Conclusion: so it seems for SQL Server 2008 R2 it would be a good thing to get rid of the additional Filter condition if you have an Intersects in the CQL_FILTER (and probably for the other spatial functions as well).

Hum… a simple “forget the rest if there is a intersect” cannot be applied, because of code genericity. In particular:

Doing so at the WMS level would incur in quite a bit of overhead for more intellingent databases, especially if the intersection is covering a larger area
Doing so at the store level would make WFS responses invalid, because all filters need to be applied

There is however a way, if you want to venture in some logic filtering simplification, which is to modify the

SimplifyingFilterVisitor (https://github.com/geotools/geotools/blob/master/modules/library/main/src/main/java/org/geotools/filter/visitor/SimplifyingFilterVisitor.java)

so that it recognizes the case of multiple spatial filters and-ed toghether, and simplifies them into a single

equivalent spatial expression (e.g. you take all bboxes and polygons and intersect them)

If you can make a pull request with that kind of change, plus tests in SimplifyingFilterVisitorTest (https://github.com/geotools/geotools/blob/master/modules/library/main/src/test/java/org/geotools/filter/visitor/SimplifyingFilterVisitorTest.java)

to cover the new simplification paths, there is a good chance the pull request is going to be accepted.

Also do check the contribution guidelines here:

–

==

GeoServer Professional Services from the experts! Visit

http://goo.gl/it488V for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.

Via Poggio alle Viti 1187

55054 Massarosa (LU)

Italy

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.