geowolf wrote > On Wed, Mar 18, 2015 at 5:38 PM, Roel De Nijs < > roel.denijs@
> > > wrote: > > Primary and secondary filter... the first one uses the spatial index, the > second does the in memory filtering. > Or has SQL Server learned to use the spatial index when just seeing > STIntersects? > > Even if it does, we'd might have to keep the current syntax for backwards > compatibility with older versions > > That said, SQLServer optimizer is really poor and expects some complicated > setup in order to better optimize the queries (basically, writing the > query > in more than one bit, setting variables first in a few statements, then > using them in the query) which is difficult to support with our current > shared codebase for all databases... it's not impossible though, we are > basically waiting for people to sponsor this work, as it's not small > (won't > fit in anybody's spare time). 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) 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 I executed every query using the SQL Server Management Studio and before each query I removed the query plans. 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/ Kind regards, Roel De Nijs Senior Java Developer -- View this message in context: http://osgeo-org.1560.x6.nabble.com/Getting-features-intersecting-with-a-polygon-tp5194135p5195806.html Sent from the GeoServer - User mailing list archive at Nabble.com. ------------------------------------------------------------------------------ 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
