question

**Disclaimer** Until 2 hours ago I'd never heard of, let alone used the ST* geometry functions. What follows might be useless to you. Apologies in advance if it is. What I think we have to do is limit the amount of unnecessary use of the geometry functions. Without seeing your code, my hunch is that that is where your bottleneck is. Comparing everything to everything else when you're only interested in those locations that are less than 10m apart seems excessive. If there was a way we could do a preliminary pass over the data to narrow down the focus and knock out say 90% (or whatever) of the comparisons that would obviously speed things up. And perhaps we can. From what I can gather latitudes range from -90 to +90. Longitudes range from -180 to +180. There seems to be about 110 or 111 kms distance between one line of latitude and the next. Line of longitude converge as they approach the poles but appear to be about 100kms apart at the equator and about 47km apart and the arctic and antarctic circles. A fair bit more than 10m in any event. If you store the integer component of each set of coordinates (as smallint columns for example) then you can join your tables and limit each pair to be compared using geometry functions to only those that are +/- 1 degree lat and +/- 1degree long of each other. I'll go out on a limb here and say smallint comparisons in an inner join are going to be a bit faster than geometry functions in a cross join. Without a representative sample of your table A and table B data I can't tell whether my suggested solution should be taken literally but the idea might nevertheless be refined and customised to your specific circumstances. For instance, say your entire area of interest will only ever be within the lat/long rectangle (20 20, 20 0, 0 0, 0 20) then you might want to store the numeric versions of your coordinates as numeric(5,2) or whatever and narrow down your comparisons as required. I had a play around with AdventureWorks2012 using person.address to create two temp tables with the same number of rows as yours, and the number of geometry function comparisons required dropped from and 9.5 million to about 650,000 at +/- 1 degree. The following code (excluding temp table creation) took about 2 minutes 45 seconds to run on my laptop but that's possibly no relevance to you because it's not clear what other processing you have to do. IF OBJECT_ID('tempdb..#addresses_a') IS NOT NULL DROP TABLE #addresses_a; SELECT TOP 1691 a.addressid ,a.SpatialLocation ,cast(a.SpatialLocation.Lat AS smallint) AS latitude_integer ,cast(a.SpatialLocation.Long AS smallint) AS longitude_integer INTO #addresses_a FROM Person.[Address] a ORDER BY a.AddressID ASC IF OBJECT_ID('tempdb..#addresses_b') IS NOT NULL DROP TABLE #addresses_b; SELECT TOP 5778 b.addressid ,b.SpatialLocation ,cast(b.SpatialLocation.Lat AS smallint) as latitude_integer ,cast(b.SpatialLocation.Long AS smallint) AS longitude_integer INTO #addresses_b FROM Person.[Address] b ORDER BY b.AddressID DESC SELECT aa.SpatialLocation.STDistance(ab.SpatialLocation) FROM #addresses_a AS aa JOIN #addresses_b AS ab ON aa.latitude_integer between ab.latitude_integer - 1 and ab.latitude_integer + 1 AND aa.longitude_integer between ab.longitude_integer - 1 and ab.longitude_integer + 1 Note that the above probably won't work properly for that line that runs up the Pacific (-180/+180) so you'll miss coordinates <10m apart where they span this line, but for that pass you could write a specific test.