Nearest Neighbor (with expanding box) working extremely slow for an update RRS feed

  • Question

  • I am using SQL 2012 sp1 and I have the following issue:

    I have followed the instructions provided at Nearest Neighbors by Isaac Kunen and I have create a table valued function which has as parameters the Geography column and the @Start parameter for the starting distance.

    I have wrapped all of this in an update statement:

    UPDATE Table1 SET Code = result.Code, Distance = result.Distance FROM Table1 AS T OUTER APPLY CalculateClosestDistanceFunction(T.Location,1000) AS result WHERE ID < 100

    My function code is:

    FUNCTION [dbo].[tvf_GetNearestCodeDistance] ( @PointGeoLocation as geography, @start as FLOAT ) RETURNS TABLE AS RETURN ( WITH NearestPoints AS ( SELECT TOP(1) WITH TIES *, sp.geolocation.STDistance(@PointGeoLocation) AS Distance, sp.Code as Code FROM Numbers JOIN Table 2 sp WITH(INDEX(SIDX_Table2)) ON sp.geolocation.STDistance(@PointGeoLocation) < @start*POWER(2,Numbers.n)

    ORDER BY n) SELECT TOP(1) Distance, Code FROM NearestPoints ORDER BY n, Distance )

    Table 1 has 2 million rows.

    Table 2 has 3200 rows.

    Query for 100 rows (WHERE ID < 100) takes about 1 minute. If I run this for all of table 1 it will take 266 hours to complete??

    Is there anything I am doing wrong?



    Thursday, March 7, 2013 5:01 PM

All replies

  • Hi Lattis,
    Thank you for your question. 
    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 
    Thank you for your understanding and support.

    Maggie Luo
    TechNet Community Support

    Monday, March 11, 2013 9:51 AM
  • Can you post a query execution plan?

    If you're using SQL Server 2012, there should be no need for that arcane NN query format - SQL Server should automatically choose an appropriate query plan for nearest neighbours so long as you include the IS NOT NULL condition. See here for an example: http://alastaira.wordpress.com/2012/02/22/updating-a-sql-server-table-with-nearest-neighbours-optimised-for-sql-server-2012-sql-azure/

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

    Monday, March 11, 2013 1:58 PM
  • I have tried the IS NOT NULL approach but the performance was identical.

    Here is the Execution Plan: Execution Plan Link



    Monday, March 11, 2013 3:38 PM
  • Hi,

    Can you try the function code without update to the table directly? maybe using temporary table. Let me know the result.

    Christian HL
    Microsoft Online Community Support

    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Thursday, March 14, 2013 3:22 AM
  • Still very slow:

    57 records in 40 Seconds

    Thursday, March 14, 2013 2:24 PM