Query very slow when using spatial with radius

Question

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

Comments on this question have been moved to chat.
– Paul White ♦
Mar 26, 2021 at 12:49 — Paul White, Mar 26, 2021 at 12:49

Andrew Sayer · Accepted Answer · 2021-03-27 16:20:20Z

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.

OP can always have a partitioned heap or CCI with a non-partitioned non-clustered Primary Key. But using two tables or a Temporal Table is probably better. — David Browne - Microsoft, Mar 27, 2021 at 16:29
having non expired listings is an option but that becomes harder to maintain over time. I created a Indexed view that only has non-expired listings but doesnt seem to improve that much. i will post my entire database, with all the indexes I have and will post it all once its ready, hoping someone would look at the database and provide a better solution to my initial thought, will post a link to a new SO question once its ready, as for listing Id sort that could work, I also created an index on expirydate with include of location and createddate, that improved the query a little — Zoinky, Mar 27, 2021 at 16:50
@DavidBrowne-Microsoft I have posted a better q with database and exact queries I run that cause me problems at dba.stackexchange.com/questions/288843/… — Zoinky, Mar 28, 2021 at 1:12

Stephen Morris - Mo64 · Accepted Answer · 2021-03-26 12:36:59Z

I think you should try buffering the point by 15 km (use Point.STBuffer() ) to convert it into a circle & store it in a variable @c ) and then do a Filter operation

WHERE l.location.Filter(@c) = 1

Judging by the discussion you may well have other issues in your view / query and you should test whether using a temp table and either doing the geography filter or the criteria filter first gives you better performance.

To get a totally accurate result you may need to do a crude filter using a slightly larger buffered circle and then a radius seach as a precise filter - depends if you need that precision.

I have posted a better q with database and exact queries I run that cause me problems at dba.stackexchange.com/questions/288843/… — Zoinky, Mar 28, 2021 at 1:13

Vladislav Zalesak · Accepted Answer · 2021-03-27 13:01:01Z

Given the size of the table, i would have a tip to try. I am not that skilled with geography and spatial indexes so it may be an no-go. Instead of selecting and ordering all, try to only select the relevant ListingId that are valid for your conditions, order them by createddate and afterwards do a join the rest data. The sort operation on all columns may cause a big memory grant that could be a problem.

In addition, together with time statistics, take a look at io statistcs.

SELECT ListingID, cast(Location as geography) as loc, createddate
into #preprocess
FROM [dbo].Listings l
WHERE l.ExpiryDate >= GETDATE() 
    

select Listings.* 
from #preprocess as p
join Listings on Listings.ListingID = p.ListingId
where loc.STDistance(@p3) < 15000
ORDER BY p.createddate
OFFSET 0 ROWS FETCH NEXT 21 ROWS ONLY

I have posted a better q with database and exact queries I run that cause me problems at dba.stackexchange.com/questions/288843/… — Zoinky, Mar 28, 2021 at 1:13

Stack Exchange Network

current community

your communities

more stack exchange communities

Query very slow when using spatial with radius

3 Answers 3

Your Answer

Not the answer you're looking for? Browse other questions tagged sql-server spatial or ask your own question.

Hot Network Questions

Query very slow when using spatial with radius

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-server spatial or ask your own question.

Related

Hot Network Questions