locked
Spatial Indexes .... Again. RRS feed

  • Question

  • I've read the BOL stuff on spatial indexes and have been playing with them, trying to optimize a rather simple "Intersects" type query. And, no matter what parameters I put in for the index variables (levels, cells in box, etc.) I always get a query time of 14 seconds with the index and 5 seconds without. My dataset is middling small (only 122,000 rows) and I'd expect much better performance than either number I'm seeing.

    Given the assumption that spatial indexes are supposed to help performance instead of hurt it, what guidelines are there for deciding whether to use 'High', 'Medium' or 'Low' for the grid levels and the number of 'cells_per_object'?

    Steve G.
    Tuesday, December 23, 2008 10:32 PM

Answers

  • Well, let's see here. Yes, I read about ensuring that the spatial index is being used, and have verified that it is in use.

    1) Yes, I'm using the Geometry data type. To create the index in the first place, I went into the data and figured out the overall envelope and have created the Bounding Box on the index with those coordinates.

    2) My original results were: run with no index = 5 seconds. Create an index with low, medium or high, any number for cells_per_object, etc and run = 14 seconds. Mystifying.

    2A) What I discovered was that I'd set the bounding box incorrectly (fat fingers, what can I say). Moral of the story, check your index parameters!

    Steve G.
    • Marked as answer by Isaac Kunen Tuesday, January 6, 2009 6:48 PM
    Wednesday, December 24, 2008 9:45 PM

All replies

  • Very little general advice exists on optimising spatial indexes, because it's so hugely dependent on the nature of the data which you are using.

    I'll assume you've already read and followed the advice about checking that your spatial index is being used (check the execution plan for a spatial seek), and making it be used (with hint). In which case, here's some ideas...

    1.) If you are using the geometry datatype, make sure that you are using the tightest bounding box required to cover the extent of the data you wish to be indexed. This will lead to the best resolution of grids covering that area. If using geography, you don't have this option since all geography indexes are assumed to cover the entire globe.

    2.) Start with no index, run a query to get a benchmark. Then add a grid using LOW at every level. Run the same query, test against benchmark. Increase the grid resolution gradually until you stop receiving performance enhancements. Go back to whichever grid resolution worked best for you.

    3.) CELLS_PER_OBJECT is intricately linked to the resolution of the grids you use - choosing higher resolution grids  leads to a greater number of smaller cells, so therefore the number of cells required to completely cover an object needs to be increased. If you set cells per object too low then you will get more false positives returned from the primary filter. Set it too high and each index entry will grow large and your index will be inefficient to query.

    4.) Try replacing Intersects() with Filter() if you don't need to apply the secondary filter.

    5.) Remember that you can add more than one spatial index to the same table, choosing different parameters for each one.

    6.) When you find a setup that works best for you, post it back here. We're all learning best practice together, so it will be helpful for others to know!

    Tuesday, December 23, 2008 10:46 PM
    Answerer
  • Well, let's see here. Yes, I read about ensuring that the spatial index is being used, and have verified that it is in use.

    1) Yes, I'm using the Geometry data type. To create the index in the first place, I went into the data and figured out the overall envelope and have created the Bounding Box on the index with those coordinates.

    2) My original results were: run with no index = 5 seconds. Create an index with low, medium or high, any number for cells_per_object, etc and run = 14 seconds. Mystifying.

    2A) What I discovered was that I'd set the bounding box incorrectly (fat fingers, what can I say). Moral of the story, check your index parameters!

    Steve G.
    • Marked as answer by Isaac Kunen Tuesday, January 6, 2009 6:48 PM
    Wednesday, December 24, 2008 9:45 PM