title: Spatial index slowing down query
body: <h2>Background</h2>
  
  <p>I have a table that contains POLYGONS/MULTIPOLYGONS which represent customer territories:</p>
  
  <ul>
  <li>The table contains roughly 8,000 rows</li>
  <li>Approximately 90% of the polygons are circles</li>
  <li>The remainder of the polygons represent one or more states, provinces, or other geographic regions. The raw polygon data for these shapes was imported from <a href="http://www.census.gov/geo/www/tiger/tgrshp2010/tgrshp2010.html" rel="nofollow noreferrer" title="2010 Census TIGER/Line Shapefiles">US census data</a>.</li>
  <li>The table has a spatial index and a clustered index on the primary key. No changes to the default SQL Server 2008 R2 settings were made. 16 cells per object, all levels medium.</li>
  </ul>
  
  <p>Here's a simplified query that will reproduce the issue that I'm experiencing:</p>
  
  <pre><code>DECLARE @point GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT (-76.992188 39.639538)', 4326)
  
  SELECT terr_offc_id
  FROM tbl_office_territories
  WHERE terr_territory.STIntersects(@point) = 1
  </code></pre>
  
  <p>What seems like a simple, straightforward query takes 12 or 13 seconds to execute, and has what seems like a very complex execution plan for such a simple query.</p>
  
  <p><img src="https://content.screencast.com/users/DavidBudiac.Inphinet/folders/Jing/media/7e426dd0-62ad-4b9c-990f-e7428daf330c/00000408.png" alt="Execution Plan"></p>
  
  <p>In my research, several sources have suggested adding an index hint to the query, to ensure that the query optimizer is properly using the spatial index. Adding <code>WITH(INDEX(idx_terr_territory))</code> has no effect, and it's clear from the execution plan that it is referencing my index regardless of the hint.</p>
  
  <h2>Reducing polygons</h2>
  
  <p>It seemed possible that the territory polygons imported from the US Census data are unnecessarily complex, so I created a second column, and tested reduced polygons (w/ <a href="http://msdn.microsoft.com/en-us/library/cc627410.aspx" rel="nofollow noreferrer">Reduce() method</a>) with varying degrees of tolerance. Running the same query as above against the new column produced the following results:</p>
  
  <ul>
  <li>No reduction: 12649ms</li>
  <li>Reduced by 10: 7194ms</li>
  <li>Reduced by 20: 6077ms</li>
  <li>Reduced by 30: 4793ms</li>
  <li>Reduced by 40: 4397ms</li>
  <li>Reduced by 50: 4290ms</li>
  </ul>
  
  <p>Clearly headed in the right direction, but dropping precision seems like an inelegant solution. Isn't this what indexes are supposed to be for? And the execution plan still seems strangly complex for such a basic query.</p>
  
  <h2>Spatial Index</h2>
  
  <p>Out of curiosity, I removed the spatial index, and was stunned by the results:</p>
  
  <ol>
  <li>Queries were faster WITHOUT an index (sub 3 sec w/ no reduction, sub 1 sec with reduction tolerance >= 30)</li>
  <li>The execution plan looked far, far simpler:</li>
  </ol>
  
  <p><img src="https://content.screencast.com/users/DavidBudiac.Inphinet/folders/Jing/media/8a6fc7b5-3f16-4890-917e-f79812f981bc/00000407.png" alt="Execution Plan w/o index"></p>
  
  <h2>My questions</h2>
  
  <ol>
  <li>Why is my spatial index slowing things down?</li>
  <li>Is reducing my polygon complexity really necessary in order to speed up my query? Dropping precision could cause problems down the road, and doesn't seem like it will scale very well.</li>
  </ol>
  
  <h2>Other Notes</h2>
  
  <ul>
  <li>SQL Server 2008 R2 Service Pack 1 has been applied</li>
  <li><a href="http://sqlskills.com/BLOGS/BOBB/post/How-to-ensure-your-spatial-index-is-being-used.aspx" rel="nofollow noreferrer">Further research suggested</a> running the query inside a stored procedure. Tried this and nothing appeared to change.</li>
  </ul>