locked
spatial index not being used RRS feed

  • Question

  • Hi,

    I'm unable to get a query on a table to use the spatial index without a hint.

    The query is the following:
    SELECT "OID", "Version", "CAD_ID", "ERF_KEY", "SG_ID", "ERF_NUMBER", "PORTION", "METROTOWN", "ForeColor", "BackColor", "Size", "Style", "BorderForeColor", "BorderBackColor", "BorderSize", "BorderStyle", "Geometry".STAsBinary(), "Geometry".STSrid
    FROM "dbo"."CAD_0801_NAT_WGS84_Drawing" -
    WHERE Geometry:Tongue TiedTGeomFromText('POLYGON((18 -35,
        19 -35,
        19 -34,
        18 -34,
        18 -35))' , 4326).STIntersects([Geometry])=1

    The index is the following:
    CREATE SPATIAL INDEX [CAD_0801_NAT_WGS84_Drawing_idx] ON [dbo].[CAD_0801_NAT_WGS84_Drawing]
    (
        [Geometry]
    )USING  GEOMETRY_GRID
    WITH (
    BOUNDING_BOX =(14.8189491, -36.4759829, 34.5342159, -20.4830011), GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
    CELLS_PER_OBJECT = 16, PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    GO

    The database compatability level is SQL Server 2008 (100).

    With the hint it takes about 25 seconds to return with 300,000 rows. Without the hint it takes over 5 minutes (never waited for it to finish).

    My understanding is that the query optimizer doesn't like variables (which this doesn't have) and also likes the order of the STIntersects = 1 to be what I've got.

    There are no statistics for this index (can one have them for a spatial index?)

    Does anyone have any ideas how I can get a spatial index on this table to be taken into consideration when querying?

    thanks
    Paul

    Saturday, July 19, 2008 3:05 PM

Answers

  • A couple of thoughts:

     

    a.) remember that SQL Server is a cost-based optimizer. While the query might execute faster with the index, it may also have a much greater cost than not using it, thus the need for the hint. If you know how to get the query plan cost by reading the actual query plan, I'd suggest looking at the plans with and without the hint and seeing how they compare.

     

     

    b.) The amount of detail in your index might be hurting more than helping. My general rule about index construction is to start with the least detail in each level, test by comparing the query costs, then increase the level of the from the bottom up as long the cost delta is lower than the time delta.

     

    Does this help?

     

    Saturday, July 19, 2008 5:46 PM
    Answerer
  • Unfortunately, the Query Optimizer is not able to do cost-based optimization on spatial objects when a parse method is used either.  The recommended way to write your queries is with parameterized sql where you pass in the geometry object as a parameter directly rather than parsing it from text inline with the query.  I don't think this is clear in BOL, but it should be covered in a future spatial indexing whitepaper.

     

    While the spatial index can still be used if it is explicitly hinted, it is less likely that QO will pick the index plan for you in this case.  In some cases, even with parameterized queries the spatial index plan may still not be picked even if it is the better choice so I would not be afraid to hint the queries.

    Monday, July 21, 2008 6:11 AM

All replies

  • A couple of thoughts:

     

    a.) remember that SQL Server is a cost-based optimizer. While the query might execute faster with the index, it may also have a much greater cost than not using it, thus the need for the hint. If you know how to get the query plan cost by reading the actual query plan, I'd suggest looking at the plans with and without the hint and seeing how they compare.

     

     

    b.) The amount of detail in your index might be hurting more than helping. My general rule about index construction is to start with the least detail in each level, test by comparing the query costs, then increase the level of the from the bottom up as long the cost delta is lower than the time delta.

     

    Does this help?

     

    Saturday, July 19, 2008 5:46 PM
    Answerer
  • Unfortunately, the Query Optimizer is not able to do cost-based optimization on spatial objects when a parse method is used either.  The recommended way to write your queries is with parameterized sql where you pass in the geometry object as a parameter directly rather than parsing it from text inline with the query.  I don't think this is clear in BOL, but it should be covered in a future spatial indexing whitepaper.

     

    While the spatial index can still be used if it is explicitly hinted, it is less likely that QO will pick the index plan for you in this case.  In some cases, even with parameterized queries the spatial index plan may still not be picked even if it is the better choice so I would not be afraid to hint the queries.

    Monday, July 21, 2008 6:11 AM
  • Thanks for the responses.

    The parameterized query doesnt make a difference.

    Howver dropping the index and recreating with medium across all four levels allowed the qo to use the index.

    It isnt possible to use hints since this data needs to be queried by a gis application which doesn't use index hints.

    It still does seem dodgy that the index wasn't wasn't picked up by the qo.

    We're talking about a factor of 20 difference for the query. The index should definitely be used by the qo.

    Thanks for the help guys.
    Thursday, July 24, 2008 5:07 AM