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 :-D 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] 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 Van: [email protected] [mailto:[email protected]] Namens Andrea Aime Verzonden: woensdag 30 september 2015 18:16 Aan: Roel De Nijs <[email protected]> CC: [email protected] Onderwerp: Re: [Geoserver-users] GetMap-request with CQL_FILTER too slow 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]<mailto:[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]> [mailto:[email protected]<mailto:[email protected]>] Namens Andrea Aime Verzonden: dinsdag 29 september 2015 20:16 Aan: Roel De Nijs <[email protected]<mailto:[email protected]>> CC: [email protected]<mailto:[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]<mailto:[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<tel:%2B39%200584%20962313> fax: +39 0584 1660272<tel:%2B39%200584%201660272> mob: +39 339 8844549<tel:%2B39%20%C2%A0339%208844549> 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<http://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
