CODEWITHSUNDEEP
sqlperformancesql-server-2008t-sqlspatial
wwarby
wwarby

Reputation: 2061

How can I improve performance my geography update query in SQL Server 2008 using STDistance?

I am having serious performance issues with a spatial update query in SQL Server 2008 (not R2) using STDistance(). The goal is simple enough: I have a table with 120,000 records, Houses with the column NearestLibrary. I have a second table with 12 records, Libraries with the column Name. Both tables have a geography column LatLngGeography. I want to update Houses.NearestLibrary with the name of the closest library from Libraries.

This query takes around 50 seconds to run:

UPDATE Houses
    SET NearestLibrary = (
    SELECT TOP 1 Name
    FROM Libraries
    ORDER BY Houses.LatLngGeography.STDistance(Libraries.LatLngGeography)
)

That would almost be acceptable, except that when I run it for Parks instead of Libraries where I have about 100 parks, it takes almost 10 times as long. Both tables have properly constructed spatial indexes named IX_Houses_LatLngGeography and IX_Libraries_LatLngGeography respectively but thus far I have been unable to work out how make my query use them.

Is there a way to improve the performance of my query?

Upvotes: 2

Views: 1535

Answers (2)

wwarby
wwarby

Reputation: 2061

Well, I got the execution time down somewhat using a temporary table. Here is my end result query:

IF OBJECT_ID('tempdb..#TempDistances') IS NOT NULL DROP TABLE #TempDistances
CREATE TABLE #TempDistances (
    HouseID varchar(20),
    Distance float
)

INSERT INTO #TempDistances
SELECT h.HouseID, MIN(h.LatLngGeography.STDistance(l.LatLngGeography)) AS Distance
FROM (SELECT * FROM Houses) AS h
LEFT JOIN Libraries AS l
ON h.LatLngGeography.STDistance(l.LatLngGeography) IS NOT NULL
GROUP BY h.HouseID

CREATE NONCLUSTERED INDEX TempDistances_HouseID ON #TempDistances (HouseID)
CREATE NONCLUSTERED INDEX TempDistances_Distance ON #TempDistances (Distance)

UPDATE h
SET NearestLibraryID = l.ID,
NearestLibraryName = l.Name,
NearestLibraryURL = l.URL,
NearestLibraryDistance = t.Distance
FROM (Houses AS h
   INNER JOIN #TempDistances AS t ON h.HouseID = t.LPINumber)
       INNER JOIN Libraries AS l ON h.LatLngGeography.STDistance(l.LatLngGeography) = t.Distance

DROP TABLE #TempDistances

It's still not really as fast as I would like, but it does the nearest parks in 8 minutes instead of 40, so I'm a lot happier than I was. Thanks to @CatchingMonkey for trying to help.

Upvotes: 0

CatchingMonkey
CatchingMonkey

Reputation: 1391

Use an "Index Hint". 

UPDATE Houses     
SET NearestLibrary = (     
    SELECT TOP 1 Name     
    FROM Libraries WITH(INDEX( SPATIAL_INDEX_NAME_HERE ))
    ORDER BY Houses.LatLngGeography.STDistance(Libraries.LatLngGeography) 
)

Upvotes: 1

Related Questions