locked
Spatial Index Tuning RRS feed

  • Question

  • I have table with 500k record and it contains a GEOGRAPHY field and it has spatial index as well.

    I had to write a simple SPPROC in which a coordinate will be supplied and I need to return set of records within 10 meters of radius of supplied coordinates. 

    I used STDistance, STWithin but I don't see much improvement on the execution time or CPU usage.

    Everytime this SQL is execute is consumes huge CPU power. When I looked at the Query Plan 95% cost is on filter on GEOGRAPHY column.

    It is just a simple SQL to get list of records within certain radius of provided point! It should have executed it in few ms with not much CPU power.

    Can you please advise?



    • Edited by Varde Friday, October 5, 2012 12:53 PM
    Friday, October 5, 2012 12:45 PM

Answers

All replies

  • I observed a very odd behavior. I copied the production database to my local system and ran the query and I see totally different query plan! On my local system it runs within no time! On my local system I see the index seek on spatial index and on server that does not happend.

    Following is the plan that works great:

    Good Plan

    Following plan hurts a lot


    Query is simple as follows:

    DECLARE @CurrentPosition geography = geography::Point(26.274868 ,-80.114104 , 4326)
    DECLARE @Radius FLOAT(53) = 100

    SELECT
    NewLeadID
    FROM
    NewLeads NL WITH(NOLOCK)
    WHERE
    @CurrentPosition.STDistance(NL.Geog) <= @Radius

    Does this mean the main server is missing a configuration for spatial data type?

    Please advise

    Friday, October 5, 2012 3:20 PM
  • Have you tried adding an index hint?

    SELECT NewLeadID
    FROM NewLeads NL WITH(INDEX(name_of_your_spatial_index))



    twitter: @alastaira blog: http://alastaira.wordpress.com/ | Pro Spatial with SQL Server 2012

    • Proposed as answer by Shulei Chen Monday, October 15, 2012 9:43 AM
    • Marked as answer by Shulei Chen Wednesday, October 17, 2012 8:06 AM
    Friday, October 5, 2012 4:52 PM
    Answerer