https://issues.qgis.org/https://issues.qgis.org/favicon.ico2014-07-24T14:08:03ZQGIS Issue TrackingQGIS Application - Feature request #10947: Use Filter() instead of STIntersects() for better performance in MSSQLhttps://issues.qgis.org/issues/10947?journal_id=546982014-07-24T14:08:03ZNathan Woodrow
<ul><li><strong>Assignee</strong> set to <i>Nathan Woodrow</i></li></ul> QGIS Application - Feature request #10947: Use Filter() instead of STIntersects() for better performance in MSSQLhttps://issues.qgis.org/issues/10947?journal_id=546992014-07-24T14:18:12ZNathan Woodrow
<ul></ul><p>I have thought that this was the case too however I can't seem to get better performance on SQL Server 2008. Could be my data and indexes, although I wouldn't suspect so.</p>
<p>Are you able to run a few on your database to see if you see any difference.</p> QGIS Application - Feature request #10947: Use Filter() instead of STIntersects() for better performance in MSSQLhttps://issues.qgis.org/issues/10947?journal_id=547192014-07-25T14:15:41ZMarkus Woehling
<ul></ul><p>I've done some performance tests with interesting results ;-)</p>
<p>I used "SELECT <id> FROM <table> WHERE ..." with four different conditions:<br /> 1) WHERE <geo>.STIntersects(geometry::Parse(<wkt>)) = 1<br /> 2) WHERE <geo>.STIntersects(@geometryParameter) = 1<br /> 3) WHERE <geo>.Filter(geometry::Parse(<wkt>)) = 1<br /> 4) WHERE <geo>.Filter(@geometryParameter) = 1</p>
<p>I used four different tables with 100,000 to 1,000,000 rows (three tables with points, one with linestrings).<br />The tables have quite different "fragmentation", because some of them have rows with "wrong" coordinates (e.g. points at with x=0, y=0).</p>
<p>The results on SQL 2008 are;<br /> Case 4) always gives us the best performance, case 1) always the worst (factor 3 to 10!).<br /> Case 2) and 3) are in the middle, sometimes 2) is better, sometimes 3).</p>
<p>The results on SQL 2012 quite the same, but in some cases 1) is even 100 times slower than 4), because it's 10 times slower than on SQL 2008. Maybe this depends on my hardware, SQL 2008 and SQL 2012 are running on different machines.</p>
<p>So I think you should always use Filter() and you should try to use a parameter for the geometry.</p>
<p>I also tried to pass the wkt-string as parameter [WHERE <geo>.STIntersects(geometry::Parse(@wkt)) = 1]: This makes no difference.</p>
<p>I used .NET for the tests, so this is the SQL statement for case 4):<br />declare @p3 sys.geometry<br />set @p3=convert(sys.geometry,0x0...binary.stuff...0)<br />exec sp_executesql N'SELECT <id> FROM <table> WHERE <geo>.Filter(@box) = 1',N'@box [geometry]',@box=@p3</p>
<p>Hope that helps.</p> QGIS Application - Feature request #10947: Use Filter() instead of STIntersects() for better performance in MSSQLhttps://issues.qgis.org/issues/10947?journal_id=547202014-07-25T16:42:30ZNathan Woodrow
<ul></ul><p>Thanks for the tests. I will implement 4) if it's the fastest.</p> QGIS Application - Feature request #10947: Use Filter() instead of STIntersects() for better performance in MSSQLhttps://issues.qgis.org/issues/10947?journal_id=712892016-06-24T20:20:37ZNathan Woodrow
<ul><li><strong>Priority</strong> changed from <i>Normal</i> to <i>High</i></li></ul> QGIS Application - Feature request #10947: Use Filter() instead of STIntersects() for better performance in MSSQLhttps://issues.qgis.org/issues/10947?journal_id=731882016-10-21T08:27:17ZMarkus Woehling
<ul></ul><p>I think 4) is more complex to implement than 3).<br />Because 3) would also improve performance a lot, I want to propose that implementation of 3) would be enough for now.<br />There are even some corner cases where the SQL query optimizer does NOT use a spatial index if STIntersects() is used instead of Filter().<br />In this cases 3) would help a lot (not using a spatial index degrades performance massively, of course).</p> QGIS Application - Feature request #10947: Use Filter() instead of STIntersects() for better performance in MSSQLhttps://issues.qgis.org/issues/10947?journal_id=766612017-04-30T22:47:47ZGiovanni Manghigiovanni.manghi@gmail.com
<ul><li><strong>Easy fix?</strong> set to <i>No</i></li></ul> QGIS Application - Feature request #10947: Use Filter() instead of STIntersects() for better performance in MSSQLhttps://issues.qgis.org/issues/10947?journal_id=802872017-06-03T06:03:11ZNyall Dawson
<ul><li><strong>% Done</strong> changed from <i>0</i> to <i>100</i></li><li><strong>Status</strong> changed from <i>Open</i> to <i>Closed</i></li></ul><p>Applied in changeset <a class="changeset" href="https://issues.qgis.org/projects/qgis/repository/revisions/62af54ecb2666d0bde0f7af102d663ded5cda97d" title="[mssql] Use Filter instead of STIntersects to improve query performance ...and refine validity t...">qgis|62af54ecb2666d0bde0f7af102d663ded5cda97d</a>.</p>