Hi Roel, yes, adding a ticket would not hurt. Is there anybody around with a more recent SQL server release that can perform similar tests? It would be great to know if this finding is valid cross version
Cheers Andrea On Mon, Sep 28, 2015 at 5:59 PM, Roel De Nijs <[email protected]> wrote: > Finally after 6 months I have had some time to investigate this further. > Of course I didn’t know the polygon anymore from my initial simple test, so > I had to use another one. We are still using SQL Server 2008 R2. > > > > I did a very simple test using one of our queries with different > combinations of Filter and/or STIntersects in the where-clause: > > a) only Filter: 80836 records in 4215ms > > b) only STIntersects: 77729 records in 5017ms > > c) Filter and STIntersects: 77729 records in 11364ms > > I executed every query using the SQL Server Management Studio and before > each query I removed the query plans. > > > > Then I executed every query again using Microsoft JDBC Driver for SQL > Server (version 4.0.2206.100) and the time needed to execute the query is > pretty similar. Before executing each query, I removed the query plans as > well. > > a) only Filter: 80836 records in 4459ms > > b) only STIntersects: 77729 records in 5180ms > > c) Filter and STIntersects: 77729 records in 11604ms > > > > > > If it’s still useful, I will open a jira ticket in the next few days and > share these results. > > > > Kind regards, > > Roel De Nijs > > Senior Java Developer > > > > *Van:* [email protected] [mailto:[email protected]] *Namens *Andrea > Aime > *Verzonden:* vrijdag 27 maart 2015 21:40 > *Aan:* Roel De Nijs <[email protected]> > *CC:* GeoServer Mailing List List <[email protected]> > *Onderwerp:* Re: [Geoserver-users] Getting features intersecting with a > polygon > > > > On Fri, Mar 27, 2015 at 7:36 PM, Roel De Nijs <[email protected]> > wrote: > > We use a few custom-written queries which use only an STIntersects in the > where-clause and the spatial index is mentioned in the execution plan of > this query. So I think SQL Server has learned to use the spatial index :) > According to this blog post [1] it's very important to upgrade (if > possible) > to SQL Server 2008 SP1 if you want to ensure the spatial index is being > used. (We use SQL Server 2008 R2) > > > > Nah, we had no ends of problems with sql server 2012, that's why we got > the sponsoring > > to add the query hints in just this database. > > But it's nice that at least with simple queries, no parallel execution > options on, > > the optimizer can do its job as it should (I guess we just got spoiled by > > postgresql/postgis, there are no query hints, and at least in the last 8 > years, > > I had no need to use them anyways, Oracle and SQLServer are disappointing > > by comparison, but not "bad" in absolute terms). > > > > I did a very simple test using one of our queries with different > combinations of Filter and/or STIntersects in the where-clause: > a) only Filter: 76159 records in 9032ms > b) only STIntersects: 67490 records in 11472ms > c) Filter and STIntersects: 67490 records in 21472ms > > > > Interesting, that's good data. Could you open a ticket asking for an > improvement (mind, > > tomorrow we switch to a new jira server, so it's either tonight, or next > week). > > We need to determine if the above are valid on all versions, or else, need > to setup > > some version recognition and act accordingly > > > > I executed every query using the SQL Server Management Studio and before > each query I removed the query plans. > > > > Mind, the query performance from native sql server tools, and from jdbc, > sometimes > > vary a lot. > > > > I tried the 3rd point from the blog post [1] as well, but no significant > changes in query execution time, nor in query execution plans. > > [1] > > http://www.sqlskills.com/blogs/bobb/how-to-ensure-your-spatial-index-is-being-used/ > > > > Ok, this is a where postgis is vastly superior to both oracle and > sqlserver. > > In postgis you send a query with a bbox the bbox is parsed before the > query planner > > runs, and it can intelligently decide whether to use the index or a > sequential scan. > > The threshold to make index scans faster is surprisingly low, something > like extracting > > 5 to 7% of your data, this is because you have to jump a lot back and > forth in the > > index to collect the record ids, causing quite a bit of extra wait on > spinnnig disk storage. > > > > In SQL Server and Oracle, as far as I know, the query is planned before > the functions > > creating the reference geometries are called, so the optimizer normally > decides to use > > the spatial index (minus some patological cases, e.g., parallel query > execution) and > > that's just wrong, performance wise, when you > > are trying to access a significant portion of your data (which in mapping, > is not at all uncommon). > > > > Soo.. in order to give the optimizer a chance to figure out how much data > is actually > > going to be read, we should go though that @declare business... and this > is where we'd > > need funding. > > > > Cheers > > Andrea > > > > -- > > == > > GeoServer Professional Services from the experts! Visit > > http://goo.gl/NWWaa2 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
