Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tpcolson
Starting Member
5 Posts |
 Posted - 2012-05-07 : 20:34:34
|
Using the followingSELECT * FROM dbo.GRSM_WETLAND_POLY CROSS APPLY (SELECT TOP 1 Name, shape FROM GRSM.dbo.GRSM_Trails --WITH(index(S319_idx)) WHERE GRSM_Trails.Shape.STDistance(dbo.GRSM_WETLAND_POLY.Shape) IS NOT NULL ORDER BY GRSM_Trails.Shape.STDistance(dbo.GRSM_WETLAND_POLY.Shape) ASC) fnc runs very slow on 134 rows (56 seconds), however, with the index hint uncommented, it returnsMsg 8635, Level 16, State 4, Line 3The query processor could not produce a query plan for a query with a spatial index hint. Reason: Spatial indexes do not support the comparator supplied in the predicate. Try removing the index hints or removing SET FORCEPLAN.Execution plan shows the filter cost at 98%, it's querying against 1400 rows in the other table, so the total cost is 134 * 1400 individual seeks, which is where the delay is. On their own, the spatial indexes in each table perform great, with no fragmentation, 99% page fulness, and use medium for all 4 grid levels with 16 cells per object. Changing the spatial index properties on either table had no effect on performance. Documentation suggests that spatial index hints can only be used in queries in SQL Server 2012, but surely there's a work around for this |
|
|
|
|
|
|
|
Spatial index hints don't work in SQL Server 2008?