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