3

I have the following query that I would like to speed up, I ran it through the Tuner but it didn't suggest any new indexes, so I am looking for some help to improve it. (using SQL Server 2019)

Actual Execution Plan

Query

declare @p3 sys.geography
set @p3=convert(sys.geography,0xE6100000010C030000408ED64540020000800DDC53C0)
exec sp_executesql N'WITH #myselect
                         AS (SELECT 
                                 BedroomsAvailable
                                 ,ListingId 
                                 ,UnitTypeId
                                 ,Price,
                                 PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Price)  
                                 OVER (PARTITION BY UnitTypeId, BedroomsAvailable) AS MedianCont
                            FROM Listings
                            WHERE ExpiryDate >= GetDate() AND  Price >= @MinFilterPrice AND Price <= @MaxFilterPrice AND Location.STDistance(@CurrentLocation) <= @RadiusInKms )
                        SELECT BedroomsAvailable 
                            ,COUNT(listingid) AS Count 
                            ,MIN(price) AS MinPrice 
                            ,MAX(price) AS MaxPrice 
                            ,AVG(price) AS AveragePrice
                            ,STDEV(price) as StandardDeviation 
                            ,MIN(MedianCont) MedianPrice
                            ,UnitTypeId
                        FROM #myselect
                        GROUP BY BedroomsAvailable, UnitTypeId
                        ORDER BY UnitTypeId',N'@CurrentLocation [geography],@RadiusInKms int,@MinFilterPrice decimal(3,0),@MaxFilterPrice decimal(5,0)',@CurrentLocation=@p3,@RadiusInKms=20000,@MinFilterPrice=100,@MaxFilterPrice=10000

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]

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]

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]

CREATE NONCLUSTERED INDEX [IX_Listings_CityId] ON [dbo].[Listings]
(
    [CityId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Listings] ADD  CONSTRAINT [PK_Listings] PRIMARY KEY CLUSTERED 
(
    [ListingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

Didn't really make a difference removing the order by clause.

SELECT (your columns) 
INTO #temptable 
FROM Listings 
WHERE Location.STDistance(@CurrentLocation) <= @RadiusInKms

Using a temp table then running the rest on the temp table seem to have made it slower in general, ran it multiple times 200ms original vs 300ms with temp table.

My table is pretty big (half a million records) so not sure how I can provide a reproduceable example of the data, I was hoping by just looking at my query and the plan it would be obvious to provide some hints at making it faster. I will see how I can provide a repo.

This is running on 6 virtual core/32GB HyperV (yes HyperV is bad but it's VPS).

2
  • 1
    To help set expectations for people trying to answer: this query runs for 370ms. How fast do you need it to be? Dec 31, 2020 at 14:30
  • Does plan change when moving GetDate() execution out to param assignment and adding covering non-clustered index with leading key as the more selective of ExpiryDate vs. Price? If so, please post.
    – Collin
    Jan 11, 2021 at 16:08

2 Answers 2

1

Instead of using PERCENTILE_CONT(0.5) to find the median, use one of the better performing options listed in the references from Aaron Bertrand below:

  • What is the fastest way to calculate the median

    Example:

    DECLARE @c BIGINT = (SELECT COUNT(*) FROM dbo.EvenRows);
    
    SELECT AVG(1.0 * val)
    FROM (
         SELECT val FROM dbo.EvenRows
          ORDER BY val
          OFFSET (@c - 1) / 2 ROWS
          FETCH NEXT 1 + (1 - @c % 2) ROWS ONLY
    ) AS x;
    
  • Best approaches for grouped median

    Example:

    SELECT   d.SalesPerson, w.Median
    FROM
    (
       SELECT SalesPerson, COUNT(*) AS y
       FROM dbo.Sales
       GROUP BY SalesPerson
    ) AS d
    CROSS APPLY
    (
       SELECT AVG(0E + Amount)
       FROM
       (
          SELECT z.Amount
          FROM dbo.Sales AS z
          WHERE z.SalesPerson = d.SalesPerson
          ORDER BY z.Amount
          OFFSET (d.y - 1) / 2 ROWS
          FETCH NEXT 2 - d.y % 2 ROWS ONLY
       ) AS f
    ) AS w(Median);
    
0
-1

Try to move from percentile_cont to APPROX_PERCENTILE_CONT

In this example it pushed the speed from ~13 seconds to ~1.5 seconds.

Full documentation here.

Keep in mind that Microsoft says:

"The function implementation guarantees up to a 1.33% error."

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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