Improving Speed of Cross Apply

0 votes
2 answers
1379 views
                          I posted a question on how to create a relation between 2 tables based on a spatial field
https://stackoverflow.com/questions/63769885/t-sql-passing-column-to-subquery-for-filtering 

The performance is terrible when the city has only 5k records and listing has 12k
(takes 54 seconds to run on sql server 2019, dual core i5)

Anything I can do to improve it? Execution plan is https://www.brentozar.com/pastetheplan/?id=BkiRk-74P 

2nd Plan (based on the query in the answer by Kumar)
https://www.brentozar.com/pastetheplan/?id=HJSLs_L4D 

This is the query

    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

Indexes on cities and listings

    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


**Updated Query based on Kumar's Suggestion**, which does not bring back any results.  it seems this code is assuming that listing and city have the exact same position.  They dont, I just want to pick the nearest city for each listing.
I also had to change = to STEquals as there were syntax errors otherwise.

    ;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
                        
Asked by Zoinky (359 rep)
Sep 7, 2020, 05:43 PM
Last activity: Sep 2, 2023, 02:05 PM
Improving Speed of Cross Apply

Related Questions
