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]> 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 P Denk aan het milieu. Druk deze mail > niet onnodig af. > > > > ------------------------------------------------------------------------------ > > _______________________________________________ > Geoserver-users mailing list > [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
