yes, adding a ticket would not hurt. Is there anybody around with a more recent SQL server release that can perform
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: andrea.aime@…84… [mailto:andrea.aime@…84…] Namens Andrea Aime
Verzonden: vrijdag 27 maart 2015 21:40
Aan: Roel De Nijs <roel.denijs@…5173…>
CC: GeoServer Mailing List List <geoserver-users@lists.sourceforge.net>
Onderwerp: Re: [Geoserver-users] Getting features intersecting with a polygon
On Fri, Mar 27, 2015 at 7:36 PM, Roel De Nijs <roel.denijs@…5173…> 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 | Twitter | YouTube | LinkedIN
Disclaimer: zie www.aquafin.be P Denk aan het milieu. Druk deze mail niet onnodig af.
Ing. Andrea Aime
GeoSolutions S.A.S.
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.