locked
index on geography data type and STIntersects RRS feed

  • Question

  •  

    Hi,

     

    I am playing with spatial data in SQL 2008 November CTP.

     

    In my first scenario I have table with 100.000 point. Querying this table with STDistance(myPoint) work without an index about 5s, with a spatial index it is aprox. 30ms. So index gives a big improvement.

     

    Second scenario I have a table with 100.000 zones (each is a triangle). Querying this table for zone in which my given point is inside (STIntersects(myPoint)=1) works without an index 7s, with a spatial index also 7s.

     

    Looks like index is not used by a query optimizer.

     

    Any help?

     

     

    Thank you.

    Przemo

    Sunday, December 2, 2007 7:27 PM

Answers

  • Hi Przemo,

    It does seem like the index is not being used by the QO for your query.  Can you look at the query plan (using 'set showplan_xml on' or the toolbar button in SSMS) to confirm? In some cases, it may be necessary to hint your queries with the name of your spatial index to provide optimal performance.  You can force the query to use the spatial index (if possible) by specifying the hint ' with (index(spatial_index_name_or_id))' after the table that has the spatial index defined in the FROM clause.

    Steven
    Monday, December 3, 2007 7:45 AM

All replies

  • Hi Przemo,

    It does seem like the index is not being used by the QO for your query.  Can you look at the query plan (using 'set showplan_xml on' or the toolbar button in SSMS) to confirm? In some cases, it may be necessary to hint your queries with the name of your spatial index to provide optimal performance.  You can force the query to use the spatial index (if possible) by specifying the hint ' with (index(spatial_index_name_or_id))' after the table that has the spatial index defined in the FROM clause.

    Steven
    Monday, December 3, 2007 7:45 AM
  • Hi Steven,

     

    thank you for replay. It proofs that you treat developers seriously.

    I am planning to start a big project (few GB of data and up to 300 transactions/s) based on spatial data. We are looking for a software company but would like to let them use Microsoft.NET platform cause I have good experiences with it.

    So that is why we test Katmai on spatial queries in detail (our software subcontractor would like to use Oracle).

     

    Coming back to indexes. I have forces query with hint. Plan has changed, now is much more complicated, but still execution time is the same. I have more observations. Looks like what server shows in plans and index usage is not true.

     

    I will gather it all, prepare a sample database, 2 queries and a test procedure I made, to help you to do it same as I do.

    Are you ok with it?

     

    Przemek

    Monday, December 3, 2007 9:55 AM
  • Hi Przemek,

    I have questions for you.

    Because Katmai is OpenGis compatible, does it mean that geometry data should be kept in OpenGis structures? So to have a gis layer with geometry data we need to provide GEOMETRY_COLUMNS, SPATIAL_REFERENCE_SYSTEMS, TABLE_GEOMETRY_BINARY tables described in OpenGIS spec. Or we should treat it like Oracle spatial, where we have a single table with geometry column with spatial index and attributes. And there is some view where we can register such table. Can you provide any information how to organize data so any new viewer will be able to read it. Or maybe it's not specified and it's free to use how anyone wants?

    Another question is if there is a funtion like FilterQuery, where you can filter all geometry records that meets given extent criteria? In OpenGis you just had extent columns for each geometry, but Katmai has spatial index so how can it be used? I suppose not by topological operators like Intersect.

    Artur
    Monday, December 3, 2007 12:47 PM
  • Hi Przemek,

     

    There are several possible reasons to explain why you're not seeing a performance improvement when using the indexed plan.  If you share the same db and the queries with me, I will take a look.  A link here or emailed to me at stevehem at microsoft dot com would be fine.

     

    Steven

    Monday, December 3, 2007 6:29 PM