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.
-------------------------------------------------------
------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
conversation now. http://goparallel.sourceforge.net/
_______________________________________________
Geoserver-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
