Rarely in my SQL SERVER 2016 environment I need to get the distance between Geography data from two different tables like I show below
CREATE TABLE #Geos1 (GeoPoint GEOGRAPHY)
INSERT INTO #Geos1
SELECT TOP 2000 Geo FROM MyTable1 ORDER BY Id DESC
CREATE TABLE #Geos2 (GeoPoint GEOGRAPHY)
INSERT INTO #Geos2
SELECT TOP 500 Geo FROM MyTable2 ORDER BY Id DESC
SET STATISTICS TIME ON
SET STATISTICS IO ON
SELECT p1.GeoPoint.STDistance(p2.GeoPoint)
FROM #Geos1 p1 CROSS JOIN #Geos2 p2
The problem born when the number of rows grows up. I know the "performance issues" of cross join but I see that I remove the STDistance and "simply" selecting data, the response time goes down considerably (9 sec vs 50 sec is for my reasons "acceptable").
Here the query plan and statistics: Query Plan
Have you any advise to optimize the situation or the only "solution" is to reduce the number of rows? Thanks
