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: [email protected] [mailto:[email protected]] Namens Andrea Aime
Verzonden: maandag 28 september 2015 18:47
Aan: Roel De Nijs <[email protected]>
CC: [email protected]
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
<[email protected]<mailto:[email protected]>> 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<https://www.facebook.com/AquafinNV> |
Twitter<https://twitter.com/aquafinnv> |
YouTube<http://www.youtube.com/channel/UCk_4P5BJ-MtEEDCkCsR_KqQ?feature=mhee> |
LinkedIN<http://www.linkedin.com/company/aquafin/products>
Disclaimer: zie www.aquafin.be<http://www.aquafin.be> P Denk aan het milieu.
Druk deze mail niet onnodig af.
------------------------------------------------------------------------------
_______________________________________________
Geoserver-users mailing list
[email protected]<mailto:[email protected]>
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-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
