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 <[email protected]> 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 :) > > <entry key="Force spatial index usage via hints">true</entry> > > > > Kind regards, > > Roel De Nijs > > Senior Java Developer > > > > > > > > *Van:* [email protected] [mailto:[email protected]] *Namens *Andrea > Aime > *Verzonden:* dinsdag 29 september 2015 20:16 > *Aan:* Roel De Nijs <[email protected]> > *CC:* [email protected] > *Onderwerp:* Re: [Geoserver-users] GetMap-request with CQL_FILTER too slow > > > > On Tue, Sep 29, 2015 at 7:50 PM, Roel De Nijs <[email protected]> > 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 <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 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
