Sql-server – Query very slow when using spatial with radius

I have the following statement which is very slow (takes 6-30 seconds to get 21 records among 800k) using SQL Server 2019 Standard running 32GB ram (server does other things too) with 6 cpu cores running in HyperV

Here is the executed plan as well https://www.brentozar.com/pastetheplan/?id=ByvIHsP4d

I have noticed that if i change the 15km radius to something lower the query is faster but would like to improve this with 15km radius

DECLARE @p3 sys.geography
SET @p3=convert(sys.geography,0xE6100000010C010000209DE44540FFFFFF3F77CA53C0)
SELECT l.*
FROM [dbo].Listings l
WHERE l.ExpiryDate >= GETDATE() AND l.Location.STDistance(@p3) < 15000
ORDER BY createddate
OFFSET 0 ROWS FETCH NEXT 21 ROWS ONLY

I have the following spatial indexes

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

CREATE SPATIAL INDEX [16_MMMM] ON [dbo].[Listings]
(
    [Location]
)USING  GEOGRAPHY_GRID 
WITH (GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM), 
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

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

AFTER SUGGESTIONS

Original

Query

DECLARE @p3 sys.geography
SET @p3=convert(sys.geography,0xE6100000010C010000209DE44540FFFFFF3F77CA53C0) DECLARE @radius int
SET @radius = 15000

SET STATISTICS TIME ON
SELECT createddate,
       title,
       availabledate
FROM [dbo].Listings l
WHERE l.ExpiryDate >= GETDATE()
  AND l.Location.STDistance(@p3) < @radius
ORDER BY createddate
OFFSET 0 ROWS FETCH NEXT 21 ROWS ONLY
SET STATISTICS TIME OFF

Timings

(21 rows affected)

(1 row affected)

 SQL Server Execution Times:
   CPU time = 1063 ms,  elapsed time = 3530 ms.

Actual Executed Plan

https://www.brentozar.com/pastetheplan/?id=B1lk_1YEd

Temp Table

Query

DECLARE @p3 sys.geography
SET @p3=convert(sys.geography,0xE6100000010C010000209DE44540FFFFFF3F77CA53C0) DECLARE @radius int
SET @radius = 15000

--Temp Table
SET STATISTICS TIME ON
SELECT createddate,
       title,
       availabledate,
       l.Location.STDistance(@p3) AS distance INTO #tempTable
FROM listings l
WHERE l.ExpiryDate >= getdate()

SELECT *
  FROM #tempTable WHERE distance < @radius
ORDER BY createddate
OFFSET 0 ROWS FETCH NEXT 21 ROWS ONLY
DROP TABLE #tempTable
SET STATISTICS TIME OFF

Timings

(74103 rows affected)

(1 row affected)

 SQL Server Execution Times:
   CPU time = 3610 ms,  elapsed time = 9030 ms.

(21 rows affected)

(1 row affected)

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 1231 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

Actual Executed Plan

https://www.brentozar.com/pastetheplan/?id=SJQ1YkKN_

Temp Table – Radius Filter First

Query

DECLARE @p3 sys.geography
SET @p3=convert(sys.geography,0xE6100000010C010000209DE44540FFFFFF3F77CA53C0) DECLARE @radius int
SET @radius = 15000

--Temp Table
SET STATISTICS TIME ON
SELECT createddate,
       title,
       availabledate
       ,expirydate
       INTO #tempTable
FROM listings l
WHERE l.location.STDistance(@p3) < @radius

SELECT *
  FROM #tempTable WHERE expirydate >= getdate()
ORDER BY createddate
OFFSET 0 ROWS FETCH NEXT 21 ROWS ONLY
DROP TABLE #tempTable
SET STATISTICS TIME OFF

Timings

(47530 rows affected)

(1 row affected)

 SQL Server Execution Times:
   CPU time = 3281 ms,  elapsed time = 4696 ms.

(21 rows affected)

(1 row affected)

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 1880 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 67 ms.

Actual Executed Plan

https://www.brentozar.com/pastetheplan/?id=Sk7zX-YEd

CTE

Query

SET STATISTICS TIME ON 
GO 
WITH cte(createddate, title, availabledate, distance) AS
  (SELECT createddate,
          title,
          availabledate,
          Location.STDistance(convert(sys.geography,0xE6100000010C010000209DE44540FFFFFF3F77CA53C0)) AS distance
   FROM GridListings)
SELECT *
FROM cte
WHERE distance < 15000
ORDER BY createddate
OFFSET 0 ROWS FETCH NEXT 21 ROWS ONLY
SET STATISTICS TIME OFF

Timings

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(21 rows affected)

(1 row affected)

 SQL Server Execution Times:
   CPU time = 1125 ms,  elapsed time = 1752 ms.

Actual Executed Plan

https://www.brentozar.com/pastetheplan/?id=Byg-9yFVu

Listing Table

CREATE TABLE [dbo].[Listings](
    [ListingID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](1000) NULL,
    [Createddate] [datetime2](7) NOT NULL,
    [LastUpdatedDate] [datetime2](7) NOT NULL,
    [AvailableDate] [datetime2](7) NULL,
    [ExpiryDate] [datetime2](7) NOT NULL,
    [Address] [nvarchar](1000) NOT NULL,
    [Description] [nvarchar](max) NULL,
    [IsAutoGenerated] [bit] NULL,
    [Location] [geography] NULL,
    [Price] [money] NULL,
    [BedroomsAvailable] [int] NULL,
    [UnitTypeId] [int] NOT NULL,
    [Baths] [float] NULL,
    [Sqft] [int] NULL,
    [NumberParkingSpotsIncluded] [int] NULL,
    [IsSmokingFriendly] [bit] NULL,
    [IsPetFriendly] [bit] NULL,
    [IsDenIncluded] [bit] NULL,
    [IsFurnished] [bit] NULL,
    [HasYard] [bit] NULL,
    [HasBalcony] [bit] NULL,
    [HasElevator] [bit] NULL,
    [IsHydroIncluded] [bit] NULL,
    [IsCableIncluded] [bit] NULL,
    [IsWaterIncluded] [bit] NULL,
    [IsInternetIncluded] [bit] NULL,
    [IsHeatIncluded] [bit] NULL,
    [HasLaundryInUnit] [bit] NULL,
    [HasLaundryInBuilding] [bit] NULL,
    [HasAirConditioning] [bit] NULL,
    [HasDishwasher] [bit] NULL,
    [HasGym] [bit] NULL,
    [HasPool] [bit] NULL,
    [HasFridge] [bit] NULL,
    [HasConcierge] [bit] NULL,
    [Has24HourSecurity] [bit] NULL,
    [HasBarrierFreeEntranceAndRamp] [bit] NULL,
    [HasBicyleParking] [bit] NULL,
    [HasStorageLocker] [bit] NULL,
    [HasLandline] [bit] NULL,
    [IsWheelChairAccessible] [bit] NULL,
    [HasBrailleLabels] [bit] NULL,
    [HasAudioPrompts] [bit] NULL,
    [HasAccessibleWashroomsInSuite] [bit] NULL,
    [HasVisualAids] [bit] NULL,
    [HasRentDeferral] [bit] NULL,
    [RentalsVirtualOptions] [nvarchar](max) NULL,
    [LeaseDuration] [nvarchar](max) NULL,
    [ImportedEmail] [nvarchar](500) NULL,
    [ImportedContactInfo] [nvarchar](500) NULL,
    [ImportedReferenceNumber] [bigint] NOT NULL,
    [ImportedPhoneNumber] [nvarchar](50) NULL,
    [ImportedSourceID] [int] NULL,
    [ImportWebsite] [nvarchar](500) NULL,
    [CityId] [int] NULL,
 CONSTRAINT [PK_Listings] PRIMARY KEY CLUSTERED 
(
    [ListingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Listings] ADD  CONSTRAINT [DF__Listings__Import__2739D489]  DEFAULT (CONVERT([bigint],(0))) FOR [ImportedReferenceNumber]
GO

ALTER TABLE [dbo].[Listings] ADD  CONSTRAINT [DF__Listings__CityId__0F624AF8]  DEFAULT ((0)) FOR [CityId]
GO

ALTER TABLE [dbo].[Listings]  WITH CHECK ADD  CONSTRAINT [FK_City_Listings] FOREIGN KEY([CityId])
REFERENCES [dbo].[Cities] ([CityId])
GO

ALTER TABLE [dbo].[Listings] CHECK CONSTRAINT [FK_City_Listings]
GO

Best Answer

From the first posted execution plan, the spatial index is identifying the rows which match your location filter in about 100ms, that's not your problem. The problem is that there are almost 50,000 rows that match this filter which then need to also be filtered down by your expiryDate filter (roughly only 10% match this) which you can only do by visiting all 50,000 rows in your table. This is taking you about 3 seconds, only 0.3 seconds of that is visiting rows you need.

Unfortunately, you can't include your expiry date column as part of your location spatial index, otherwise that could be a pretty neat solution.

Are you able to remove these expired rows from the table? Partitioning is a reasonable solution for this but you'd need a clustered primary key which would need to include the partitioning column, that's a big ask. Routine processes to insert the expired rows into a different table and delete them from this table could be a good idea.

Doing a top-N sort of these 5,000 rows is taking about 0.6 seconds, would you get away with using the primary key column rather than createddate? Depending on what the column refers to, this could be strongly related - and if you are just including a column for pagination safely then the primary key is arguably the safest possible way. If you did do this then instead of having to do a sort of the rows after you read them, you would only have to sort the primary key column result that the spatial index scan gives you - this would cut down on the work done massively if the spatial index is only going to return rows that mostly match your ExpiryDate filter.

If you do both

Remove expired rows
Change the order by to use ListingId

You could end up with an execution that takes more like 0.15 seconds.

Best Answer

Related Solutions

Sql-server – Parent-Child Tree Hierarchical ORDER

Sql-server – Spatial Index ignored when using additional non-spatial conditions

Related Question