提高交叉應用的速度

我發布了一個關於如何根據空間欄位在 2 個表之間創建關係的問題 https://stackoverflow.com/questions/63769885/t-sql-passing-column-to-subquery-for-filtering

當city只有5k條記錄而listing有12k條時性能很糟糕（在sql server 2019上執行需要54秒，雙核i5）

我能做些什麼來改進它？執行計劃是https://www.brentozar.com/pastetheplan/?id=BkiRk-74P

第二個計劃（基於 Kumar 回答中的查詢） https://www.brentozar.com/pastetheplan/?id=HJSLs_L4D

這是查詢

select l.*, city.*
from  listings l
cross apply (
    select  top (1)  c.UnicodeName, c.name, r.code as region, cn.code as country from cities c
    inner join regions r on r.regionid = c.regionid
    inner join Countries cn on cn.CountryId = r.countryid
    where c.location.STDistance(l.location) is not null
    order by c.Location.STDistance(l.location) 
) as city

城市索引和列表

CREATE SPATIAL INDEX [256_HHHH] ON [dbo].[Listings]
(
   [Location]
)USING  GEOGRAPHY_GRID 
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH), 
CELLS_PER_OBJECT = 256, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE SPATIAL INDEX [16_HHHH] ON [dbo].[Listings]
(
   [Location]
)USING  GEOGRAPHY_GRID 
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH), 
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

根據 Kumar 的建議更新了查詢，但沒有返回任何結果。似乎這段程式碼假設列表和城市具有完全相同的位置。他們沒有，我只想為每個列表選擇最近的城市。我還必須將 = 更改為 STEquals，否則會出現語法錯誤。

;With CTE as
(
select l.*, c.RegionId, c.UnicodeName, c.name,c.location.STDistance(l.location) Locationdistance
from  listings l
inner join  cities c on l.location.STEquals(c.Location)=1

)

select c.*,r.code as region, cn.code as country
from CTE C
inner join regions r on r.regionid = c.RegionId
    inner join Countries cn on cn.CountryId = r.countryid
    where Locationdistance is not null
order by Locationdistance

始終驗證連接是否從每個連接中返回正確的行數。

試試這個，

;With CTE as
(
select l.*, city.*
from  listings l
cross apply (
    select  top (1)  c.UnicodeName, c.name,c.regionid
    --, r.code as region, cn.code as country 
    from cities c
    --inner join regions r on r.regionid = c.regionid
    --inner join Countries cn on cn.CountryId = r.countryid
    where c.location.STDistance(l.location) is not null
    order by c.Location.STDistance(l.location) 
) as city
)

select c.* 
 ,r.code as region, cn.code as country 
from CTE C
inner join regions r on r.regionid = c.regionid
inner join Countries cn on cn.CountryId = r.countryid
OPTION (MAXDOP 1, RECOMPILE);

估計行數減少。

實際行數和估計行數之間的差異是否減少。

我認為您必須提供最大搜尋距離以減少搜尋的組合數量。

所以而不是

c.location.STDistance(l.location) is not null

嘗試

c.location.STDistance(l.location) < 20

或者任何適合您的案例的值

引用自：https://dba.stackexchange.com/questions/275109