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

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

I tried your suggestion here and the following error was thrown: Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN. – wwarby Nov 18 at 12:01 Have you got a spatial index on the table?

– CatchingMonkey Nov 18 at 12:04 Here is my spatial index. Is there anything wrong with it? CREATE SPATIAL INDEX NDX_Libraries_LatLngGeography ON dbo.

Libraries ( LatLngGeography )USING GEOGRAPHY_GRID WITH ( GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM), CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY GO` – wwarby Nov 18 at 12:04 Urgh. Sorry - I can't work out how to insert line breaks in my comments. I'm new to stackoverflow – wwarby Nov 18 at 12:06 I inserted NDX_Libraries_LatLngGeography in place of SPATIAL_INDEX_NAME_HERE but otherwise the code is identical to what you suggested.

– wwarby Nov 18 at 12:07.

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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.