none
Spatial index hints don't work in SQL Server 2008?

    Question

  • Using the following

    SELECT *  FROM dbo.GRSM_WETLAND_POLY     CROSS APPLY (SELECT TOP 1 Name, shape                   
    FROM GRSM.dbo.GRSM_Trails --WITH(index S319_idx))                  
    WHERE GRSM_Trails.Shape.STDistance(dbo.GRSM_WETLAND_POLY.Shape) IS NOT NULL
                      ORDER BY GRSM_Trails.Shape.STDistance(dbo.GRSM_WETLAND_POLY.Shape) ASC) fnc

     

    runs very slow on 134 rows (56 seconds), however, with the index hint
    uncommented, it returns


    Msg 8635, Level 16, State 4, Line 3
    The query processor could not produce
    a query plan for a query with a spatial index hint. Reason: Spatial indexes do
    not support the comparator supplied in the predicate. Try removing the index
    hints or removing SET FORCEPLAN.

    Execution plan shows the filter cost at 98%, it's querying against 1400 rows
    in the other table, so the total cost is 134 * 1400 individual seeks, which is
    where the delay is. On their own, the spatial indexes in each table perform
    great, with no fragmentation, 99% page fulness, and use medium for all 4 grid
    levels with 16 cells per object. Changing the spatial index properties on either
    table had no effect on performance.

    Documentation suggests that spatial index hints can only be used in queries
    in SQL Server 2012, but surely there's a work around for this?

    Tuesday, May 08, 2012 12:31 AM

Answers

  • There’s a couple of SQL problems in there. You can’t use “dbo.” in an ON clause and can’t use TBL_LOCATIONS.SHAPE anywhere in the query without referring to that table as a table to select from in the FROM clause. So, let’s approach it step-at-a-time. Because there’s a few different SQL constructs in there...
     
    Looks like you are trying to apply Isaac’s algorithm to the original problem that you stated. So let’s state your original T-SQL statement as “for each wetland (location), I’d like to return the closest trail”.
     
    Since I don’t have your original data, I’ll start by defining the simplest tables that I can that look the same (pardon the weird mixed case in some of the examples):
     
    create table numbers (n int);
    create table tlu_trails (id int primary key, name char(10), shape geography);
    create table tbl_locations (id int primary key, shape geography);
    create spatial index s13_idx on tlu_trails(shape); /* shorter definition because using the autogrid spatial index in SQL2012 */
    go
     
    Caveat: These all compiled for me and ran on the *empty* tables with incident, let me know of any of the get the “can’t produce plan” error.
    Now, Issac’s formula starts with a single geography (@x), to get the nearest neighbor. So, for starters, let’s do one location.
     
    DECLARE @x geography;
    SELECT @x = shape FROM TBL_LOCATIONS where id = 1;  /* do only one location with id of 1 */
    DECLARE @start FLOAT = 1000;
    WITH NearestPoints AS
    (  
    SELECT TOP(1) WITH TIES Numbers.n, Name, TLU_TRAILS.SHAPE.STDistance(@x) AS dist  
    FROM Numbers
    JOIN dbo.TLU_TRAILS WITH(INDEX(S13_idx))   
    ON TLU_TRAILS.Shape.STDistance(@x) < @start*POWER(2,Numbers.n)  
    ORDER BY n
    )
    SELECT TOP(1) * FROM NearestPoints
    ORDER BY n, dist
    go
     
    To use this with CROSS APPLY, we can make the one-location case into a multi-statement table-valued function. CROSS APPLY will run that function N times, once for each location.
     
    create function dbo.get_nearest(@x geography)
    returns @t table (
      n int,
      name varchar(10),
      dist float)
    as
    begin
    DECLARE @start FLOAT = 1000;
    WITH NearestPoints AS
    (  
    SELECT TOP(1) WITH TIES Numbers.n, Name, TLU_TRAILS.SHAPE.STDistance(@x) AS dist  
    FROM Numbers
    JOIN dbo.TLU_TRAILS WITH(INDEX(S13_idx))   
    ON TLU_TRAILS.Shape.STDistance(@x) < @start*POWER(2,Numbers.n)  
    ORDER BY n
    )
    insert into @t
    SELECT TOP(1) * FROM NearestPoints
    ORDER BY n, dist;
    return;
    end
    go
     
    And use the function with CROSS APPLY, to obtain the nearest trail to each location:
    SELECT * FROM TBL_LOCATIONS
    CROSS APPLY dbo.get_nearest(TBL_LOCATIONS.shape);
    go
     
    If you want the whole thing inlined (which usually produces better performance, but may not in this case because of the spatial indexing consideration), you can use a derived table instead of a CTE:
     
    -- Original one-location case with derived table
    DECLARE @x geography;
    SELECT @x = shape FROM TBL_LOCATIONS where id = 1;
    DECLARE @start FLOAT = 1000;
    SELECT TOP(1) * FROM
    (  
    SELECT TOP(1) WITH TIES Numbers.n, Name, TLU_TRAILS.SHAPE.STDistance(@x) AS dist  
    FROM Numbers
    JOIN dbo.TLU_TRAILS WITH(INDEX(S13_idx))   
    ON TLU_TRAILS.Shape.STDistance(@x) < @start*POWER(2,Numbers.n)  
    ORDER BY n
    )
    AS NearestPoints
    ORDER BY n, dist
    go
     
    And use that (derived table) with CROSS APPLY:
    DECLARE @start float = 1000;
    SELECT * FROM TBL_LOCATIONS
    CROSS APPLY
    (
    SELECT TOP(1) * FROM
    (  
    SELECT TOP(1) WITH TIES Numbers.n, Name, TLU_TRAILS.SHAPE.STDistance(TBL_LOCATIONS.Shape) AS dist  
    FROM Numbers
    JOIN dbo.TLU_TRAILS WITH(INDEX(S13_idx))   
    ON TLU_TRAILS.Shape.STDistance(TBL_LOCATIONS.Shape) < @start*POWER(2,Numbers.n)  
    ORDER BY n
    )
    AS NearestPoints
    ORDER BY n, dist
    ) as a
    go
     
    Or, for clarity, make the derived table version into an inline table-valued function (which means its inlined, its part of the plan of, the query that uses it, like a view is).
     
    create function dbo.vget_nearest(@x geography, @start float)
    returns table
    as
    return
    SELECT TOP(1) * FROM
    (  
    SELECT TOP(1) WITH TIES Numbers.n, Name, TLU_TRAILS.SHAPE.STDistance(@x) AS dist  
    FROM Numbers
    JOIN dbo.TLU_TRAILS WITH(INDEX(S13_idx))   
    ON TLU_TRAILS.Shape.STDistance(@x) < @start*POWER(2,Numbers.n)  
    ORDER BY n
    )
    AS NearestPoints
    ORDER BY n, dist
    go
     
    -- And use it with CROSS APPLY, like this (or hardcode the number 1000 in the TVF):
    DECLARE @start FLOAT = 1000;
    SELECT * FROM TBL_LOCATIONS
    CROSS APPLY
    dbo.vget_nearest(TBL_LOCATIONS.shape, @start)
    go
     
    Let me know if anything is unclear of if it can’t use the index. And (especially) if it helps performance...
    Hope this helps,
    Cheers,
    Bob
    Friday, May 11, 2012 12:22 AM

All replies

  • Hi there,

    Spatial index hints definitely work in SQL Server 2008 - what documentation have you seen that suggests otherwise?

    The problem with your query is that it looks like you're trying to implement the new nearest-neighbour query pattern that was only introduced in SQL Server 2012 (which requires the additional STDistance() IS NOT NULL predicate) - this won't work in SQL Server 2008/R2.

    In fact, it looks like you're following the exact query pattern I gave in my blog post: http://alastaira.wordpress.com/2012/02/22/updating-a-sql-server-table-with-nearest-neighbours-optimised-for-sql-server-2012-sql-azure/ - as per the title of that post, this solution is optimised for SQL Server 2012/Azure. As I wrote there, if you're using SQL Server 2008/R2 you'll have to use one of the other patterns for identifying nearest neighbours, such as those described here: http://blogs.msdn.com/b/isaac/archive/2008/10/23/nearest-neighbors.aspx


    twitter: @alastaira blog: http://alastaira.wordpress.com/

    Tuesday, May 08, 2012 6:50 AM
  • What you’re trying to use is a new SQL Server 2012 feature called the nearest neighbor optimization.
     
    In SQL Server 2008 and 2008 R2 the only predicates that can use the spatial index (and therefore can use a spatial index hint) are enumerated here: http://technet.microsoft.com/en-us/library/bb895373(v=sql.105).aspx (geometry) and here http://technet.microsoft.com/en-us/library/bb933796(v=sql.105).aspx (geography). So the comparator that error message is complaining about is “Shape.STDistance(dbo.GRSM_WETLAND_POLY.Shape) IS NOT NULL” because spatial index only supports STDistance with “<” or “<=” but not “IS NULL” (until SQL Server 2012 with this specific optimization).
     
     
    Hope this helps, Bob
    Tuesday, May 08, 2012 6:55 AM
  • Thanks for the replies! Now I appear to be having begginers-syntax issues...and I've tried every permutation of populating the table/column values I can think of in your example, and still receive the same error. The tables and columns DO exist, and are recognzied by other queries...

    use xxxx
    
    DECLARE @start FLOAT = 1000;  
    WITH NearestPoints AS
    (   
    SELECT TOP(1) WITH TIES Name, TLU_TRAILS.SHAPE.STDistance(TBL_LOCATIONS.SHAPE) AS dist   
    FROM Numbers JOIN TLU_TRAILS WITH(INDEX(S13_idx))    
    ON dbo.TLU_TRAILS.Shape.STDistance(TBL_LOCATIONS.SHAPE) < @start*POWER(2,Numbers.n)   
    ORDER BY n)SELECT TOP(1) * FROM NearestPoints
    ORDER BY n, dist
    Msg 344, Level 16, State 1, Line 8
    Remote function reference 'dbo.TLU_TRAILS.Shape.STDistance' is not allowed, and the column name 'dbo' could not be found or is ambiguous.




    • Edited by tpcolson Thursday, May 10, 2012 10:34 PM
    Thursday, May 10, 2012 10:34 PM
  • There’s a couple of SQL problems in there. You can’t use “dbo.” in an ON clause and can’t use TBL_LOCATIONS.SHAPE anywhere in the query without referring to that table as a table to select from in the FROM clause. So, let’s approach it step-at-a-time. Because there’s a few different SQL constructs in there...
     
    Looks like you are trying to apply Isaac’s algorithm to the original problem that you stated. So let’s state your original T-SQL statement as “for each wetland (location), I’d like to return the closest trail”.
     
    Since I don’t have your original data, I’ll start by defining the simplest tables that I can that look the same (pardon the weird mixed case in some of the examples):
     
    create table numbers (n int);
    create table tlu_trails (id int primary key, name char(10), shape geography);
    create table tbl_locations (id int primary key, shape geography);
    create spatial index s13_idx on tlu_trails(shape); /* shorter definition because using the autogrid spatial index in SQL2012 */
    go
     
    Caveat: These all compiled for me and ran on the *empty* tables with incident, let me know of any of the get the “can’t produce plan” error.
    Now, Issac’s formula starts with a single geography (@x), to get the nearest neighbor. So, for starters, let’s do one location.
     
    DECLARE @x geography;
    SELECT @x = shape FROM TBL_LOCATIONS where id = 1;  /* do only one location with id of 1 */
    DECLARE @start FLOAT = 1000;
    WITH NearestPoints AS
    (  
    SELECT TOP(1) WITH TIES Numbers.n, Name, TLU_TRAILS.SHAPE.STDistance(@x) AS dist  
    FROM Numbers
    JOIN dbo.TLU_TRAILS WITH(INDEX(S13_idx))   
    ON TLU_TRAILS.Shape.STDistance(@x) < @start*POWER(2,Numbers.n)  
    ORDER BY n
    )
    SELECT TOP(1) * FROM NearestPoints
    ORDER BY n, dist
    go
     
    To use this with CROSS APPLY, we can make the one-location case into a multi-statement table-valued function. CROSS APPLY will run that function N times, once for each location.
     
    create function dbo.get_nearest(@x geography)
    returns @t table (
      n int,
      name varchar(10),
      dist float)
    as
    begin
    DECLARE @start FLOAT = 1000;
    WITH NearestPoints AS
    (  
    SELECT TOP(1) WITH TIES Numbers.n, Name, TLU_TRAILS.SHAPE.STDistance(@x) AS dist  
    FROM Numbers
    JOIN dbo.TLU_TRAILS WITH(INDEX(S13_idx))   
    ON TLU_TRAILS.Shape.STDistance(@x) < @start*POWER(2,Numbers.n)  
    ORDER BY n
    )
    insert into @t
    SELECT TOP(1) * FROM NearestPoints
    ORDER BY n, dist;
    return;
    end
    go
     
    And use the function with CROSS APPLY, to obtain the nearest trail to each location:
    SELECT * FROM TBL_LOCATIONS
    CROSS APPLY dbo.get_nearest(TBL_LOCATIONS.shape);
    go
     
    If you want the whole thing inlined (which usually produces better performance, but may not in this case because of the spatial indexing consideration), you can use a derived table instead of a CTE:
     
    -- Original one-location case with derived table
    DECLARE @x geography;
    SELECT @x = shape FROM TBL_LOCATIONS where id = 1;
    DECLARE @start FLOAT = 1000;
    SELECT TOP(1) * FROM
    (  
    SELECT TOP(1) WITH TIES Numbers.n, Name, TLU_TRAILS.SHAPE.STDistance(@x) AS dist  
    FROM Numbers
    JOIN dbo.TLU_TRAILS WITH(INDEX(S13_idx))   
    ON TLU_TRAILS.Shape.STDistance(@x) < @start*POWER(2,Numbers.n)  
    ORDER BY n
    )
    AS NearestPoints
    ORDER BY n, dist
    go
     
    And use that (derived table) with CROSS APPLY:
    DECLARE @start float = 1000;
    SELECT * FROM TBL_LOCATIONS
    CROSS APPLY
    (
    SELECT TOP(1) * FROM
    (  
    SELECT TOP(1) WITH TIES Numbers.n, Name, TLU_TRAILS.SHAPE.STDistance(TBL_LOCATIONS.Shape) AS dist  
    FROM Numbers
    JOIN dbo.TLU_TRAILS WITH(INDEX(S13_idx))   
    ON TLU_TRAILS.Shape.STDistance(TBL_LOCATIONS.Shape) < @start*POWER(2,Numbers.n)  
    ORDER BY n
    )
    AS NearestPoints
    ORDER BY n, dist
    ) as a
    go
     
    Or, for clarity, make the derived table version into an inline table-valued function (which means its inlined, its part of the plan of, the query that uses it, like a view is).
     
    create function dbo.vget_nearest(@x geography, @start float)
    returns table
    as
    return
    SELECT TOP(1) * FROM
    (  
    SELECT TOP(1) WITH TIES Numbers.n, Name, TLU_TRAILS.SHAPE.STDistance(@x) AS dist  
    FROM Numbers
    JOIN dbo.TLU_TRAILS WITH(INDEX(S13_idx))   
    ON TLU_TRAILS.Shape.STDistance(@x) < @start*POWER(2,Numbers.n)  
    ORDER BY n
    )
    AS NearestPoints
    ORDER BY n, dist
    go
     
    -- And use it with CROSS APPLY, like this (or hardcode the number 1000 in the TVF):
    DECLARE @start FLOAT = 1000;
    SELECT * FROM TBL_LOCATIONS
    CROSS APPLY
    dbo.vget_nearest(TBL_LOCATIONS.shape, @start)
    go
     
    Let me know if anything is unclear of if it can’t use the index. And (especially) if it helps performance...
    Hope this helps,
    Cheers,
    Bob
    Friday, May 11, 2012 12:22 AM
  • Bob, I sincerely thank you for the incredibly succinct and clear example of how to accomplish the query in several different ways. I got them working on SQL Express at home, and will be trying this out on the server at work tomorrow!
    Friday, May 11, 2012 2:15 AM
  • @tpcolson - I've marked Bob's post as the answer. (If, for no other reason, than the effort that must have been involved in typing it!)

    twitter: @alastaira blog: http://alastaira.wordpress.com/

    Friday, May 11, 2012 7:18 AM