Как повысить производительность запроса с функцией в условии?

Question

Есть таблица Ausfuehrungen, в которой около 51000 данных. На столбец Plz стоит некластерный индекс. Запрос select выполняется на 0 времени (смотрел в статистике), проблем нет. Но как только я добавляю в условие вызов функции, перфоманс сразу падает - запрос начинает выполняться 7 секунд! Есть еще одна таблица Sub_ZipCodesLocations и там индексы стоят на zipcode, latitude, longitude.

Вот запрос с добавлением функции в условие

Select *
from Ausfuehrungen
where ISNULL([K12].[dbo].GetDistanceOfZipCodes(TRY_CAST(Plz as int), 22307), 999999999) < 40

Вот сама функция

USE [K12]
GO
/****** Object:  UserDefinedFunction [dbo].[GetDistanceOfZipCodes]    Script Date: 19.10.2021 13:52:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER    FUNCTION [dbo].[GetDistanceOfZipCodes](@zipCode1 int, @zipCode2 int)
RETURNS real
AS 
BEGIN

DECLARE @lat1 real, @lng1 real, @lat2 real, @lng2 real,
@source geography, @target geography

select TOP 1 @lat1 = zc1.Latitude, @lng1 = zc1.Longitude from [K12].[dbo].[Sub_ZipCodesLocations] zc1 where zc1.ZipCode = @zipCode1;
select TOP 1 @lat2 = zc2.Latitude, @lng2 = zc2.Longitude from [K12].[dbo].[Sub_ZipCodesLocations] zc2 where zc2.ZipCode = @zipCode2;

if (@lat1 is null or @lng1 is null or @lat2 is null or @lng2 is null)
    return null;


DECLARE @location1 geography = geography::Point(@lat1, @lng1, 4326);
DECLARE @location2 geography = geography::Point(@lat2, @lng2, 4326);

return @location1.STDistance(@location2) / 1000
END

;

Помогите, пожалуйста, разобраться и повысить производительность запроса :) Спасибо

Answer 1

Есть всего два способа ускорения запросов, где отсев идет по значению функции:

Если в функцию передаются только столбцы одной таблицы и константы (не параметры, с вообще никогда не изменяемые) и функция детерминирована, то можно построить индекс по значению этой функции.
Провести отсев большей части данных, которые заведомо не дадут нужные значения.

Первый вариант явно вам не подходит.

А второй в вашем случае можно применить так:

Нужно найти всё, что попадает в круг определенного радиуса.

Если вокруг этого круга опишем квадрат, одна из граней которого параллельна экватору, то всё, что за пределами этого квадрата нам гарантированно не подходит.

Для удобства расчета, вокруг этого квадрата можно описать еще и трапецию, основания которой лежат на параллелях, а боковые стороны - на меридианах. В полярных координатах она будет прямоугольником, и отсев будет иметь вид:

where t.Latitude between @lat1 and @lat2
  and t.Longitude between @lng1 and @lng2

При наличии индексов по широте и долготе это будет работать очень быстро

А функцию GetDistanceOfZipCodes применять уже к результату этой выборки

Update 1

Метод не будет работать возле полюсов. Там придется просто отбирать всё, что выше/ниже определенной параллели.

Update 2

Судя по исходникм функции, у вас координаты хранятся не в самой таблице, а в Sub_ZipCodesLocations, которая неявно присоединяется по zip-коду. Если у вас в основной таблице Ausfuehrungen каждому zip-коду соответствует не одна-две, а десятки и более записей, то имеет смысл сначала выбрать нужные zip-коды, а потом по ним выбирать из основной таблицы.

Answer 2

Как я уже написал в комментариях, и собственно это же самое продублировано в соседнем ответе (но я добавлю немного кода), сначала надо ограничить всю выборку. В целом нам нужен круг с центром в вашей точке, но для простоты и быстроты придется взять квадрат, описанный вокруг этой окружности.

Длина меридианов в целом одинакова, а вот длина параллелей, очевидно, уменьшается от экватора к плюсам. Так что вверх/вниз 1 градус широты это в целом постоянная величина и равна примерно 111.45 км, то влево/вправо она будет зависеть от широты, на которой мы находимся, и в формулу войдет косинус этой широты.

Изначально вы берете свою координаты искомой точки:

DECLARE @zipCode int;
DECLARE @lat float, @lon float;

SELECT @lat = latitude, @lon = longiture 
FROM Sub_ZipCodesLocations zc1 
WHERE ZipCode = @zipCode;

задаете дистанцию 40км (или с запасом). и считаете дельту расстояний

DECLARE @d int = 40,
        @dy float =  @d / 111.45,
        @dx float =  @d / 111.45 / COS(RADIANS(@lat));

Потом фильтруете выборку по полученной области, она будет использовать индексы и работать быстро

WITH zipcodes AS (
    SELECT zipCode, latitude, longitude
         , geography::Point(latitude, logngitude, 4326) as p
    FROM Sub_ZipCodesLocations
    WHERE latitude between @lat - @dy and @lat + @dy  
      AND longitude between @lon - @dx and @lon + @dx
)

и потом уже к этой весьма ограниченной выборке каким-то образом можете применить более детальные свои расчеты расстояния, если оно вообще необходимо.

SELECT zipCode
FROM zipcodes
WHERE p.STDIstance(@p) < 40*1000

где @p = geography::point(@lat, @lon, 4326)

а вообще надо в целом в таблицу добавить столбец geography с точкой, сделать индекс и использовать его, а не пересоздавать на лету, раз уж ваша версия СУБД это позволяет. и как пишут вот тут обратить внимание на флаги 6533, так что возможно все эти танцы с бубнами и не понадобятся вовсе

БЛОГ НА HUSL

Как повысить производительность запроса с функцией в условии?

Ответы (2 шт):