Próbuję sprawdzić, czy istnieje sposób, aby oszukać SQL Server, aby używał określonego planu dla zapytania.
1. Środowisko
Wyobraź sobie, że masz jakieś dane, które są współużytkowane przez różne procesy. Załóżmy, że mamy wyniki eksperymentów, które zajmują dużo miejsca. Następnie dla każdego procesu wiemy, który rok / miesiąc wyniku eksperymentu chcemy zastosować.
if object_id('dbo.SharedData') is not null
drop table SharedData
create table dbo.SharedData (
experiment_year int,
experiment_month int,
rn int,
calculated_number int,
primary key (experiment_year, experiment_month, rn)
)
go
Teraz dla każdego procesu mamy parametry zapisane w tabeli
if object_id('dbo.Params') is not null
drop table dbo.Params
create table dbo.Params (
session_id int,
experiment_year int,
experiment_month int,
primary key (session_id)
)
go
2. Dane testowe
Dodajmy dane testowe:
insert into dbo.Params (session_id, experiment_year, experiment_month)
select 1, 2014, 3 union all
select 2, 2014, 4
go
insert into dbo.SharedData (experiment_year, experiment_month, rn, calculated_number)
select
2014, 3, row_number() over(order by v1.name), abs(Checksum(newid())) % 10
from master.dbo.spt_values as v1
cross join master.dbo.spt_values as v2
go
insert into dbo.SharedData (experiment_year, experiment_month, rn, calculated_number)
select
2014, 4, row_number() over(order by v1.name), abs(Checksum(newid())) % 10
from master.dbo.spt_values as v1
cross join master.dbo.spt_values as v2
go
3. Pobieranie wyników
Teraz bardzo łatwo jest uzyskać wyniki eksperymentu poprzez @experiment_year/@experiment_month:
create or alter function dbo.f_GetSharedData(@experiment_year int, @experiment_month int)
returns table
as
return (
select
d.rn,
d.calculated_number
from dbo.SharedData as d
where
d.experiment_year = @experiment_year and
d.experiment_month = @experiment_month
)
go
Plan jest ładny i równoległy:
select
calculated_number,
count(*)
from dbo.f_GetSharedData(2014, 4)
group by
calculated_number
zapytanie 0 plan
4. Problem
Ale aby korzystanie z danych było nieco bardziej ogólne, chcę mieć inną funkcję - dbo.f_GetSharedDataBySession(@session_id int). Tak więc najprostszym sposobem byłoby utworzenie funkcji skalarnych, tłumacząc @session_id-> @experiment_year/@experiment_month:
create or alter function dbo.fn_GetExperimentYear(@session_id int)
returns int
as
begin
return (
select
p.experiment_year
from dbo.Params as p
where
p.session_id = @session_id
)
end
go
create or alter function dbo.fn_GetExperimentMonth(@session_id int)
returns int
as
begin
return (
select
p.experiment_month
from dbo.Params as p
where
p.session_id = @session_id
)
end
go
A teraz możemy stworzyć naszą funkcję:
create or alter function dbo.f_GetSharedDataBySession1(@session_id int)
returns table
as
return (
select
d.rn,
d.calculated_number
from dbo.f_GetSharedData(
dbo.fn_GetExperimentYear(@session_id),
dbo.fn_GetExperimentMonth(@session_id)
) as d
)
go
zapytanie 1 plan
Plan jest taki sam, ale oczywiście nie jest równoległy, ponieważ funkcje skalarne wykonujące dostęp do danych sprawiają, że cały plan jest szeregowy .
Wypróbowałem więc kilka różnych podejść, na przykład używając podkwerend zamiast funkcji skalarnych:
create or alter function dbo.f_GetSharedDataBySession2(@session_id int)
returns table
as
return (
select
d.rn,
d.calculated_number
from dbo.f_GetSharedData(
(select p.experiment_year from dbo.Params as p where p.session_id = @session_id),
(select p.experiment_month from dbo.Params as p where p.session_id = @session_id)
) as d
)
go
zapytanie 2 plan
Lub używając cross apply
create or alter function dbo.f_GetSharedDataBySession3(@session_id int)
returns table
as
return (
select
d.rn,
d.calculated_number
from dbo.Params as p
cross apply dbo.f_GetSharedData(
p.experiment_year,
p.experiment_month
) as d
where
p.session_id = @session_id
)
go
plan zapytania 3
Ale nie mogę znaleźć sposobu, aby napisać to zapytanie tak dobre, jak to przy użyciu funkcji skalarnych.
Kilka myśli:
- Zasadniczo chciałbym móc w jakiś sposób powiedzieć SQL Serverowi, aby wstępnie obliczył pewne wartości, a następnie przekazał je dalej jako stałe.
- Pomocne może być, gdybyśmy mieli pośrednią wskazówkę dotyczącą materializacji . Sprawdziłem kilka wariantów (TVF z wieloma instrukcjami lub Cte z górą), ale żaden plan nie jest tak dobry jak ten z funkcjami skalarnymi
- Wiem o nadchodzącej poprawie SQL Server 2017 - Froid: Optymalizacja programów imperatywnych w relacyjnej bazie danych. Nie jestem jednak pewien, czy to pomoże. Fajnie byłoby jednak udowodnić, że się tutaj mylą.
Dodatkowe informacje
Korzystam z funkcji (zamiast wybierać dane bezpośrednio z tabel), ponieważ jest o wiele łatwiejsze w użyciu w wielu różnych zapytaniach, które zwykle mają @session_idjako parametr.
Poproszono mnie o porównanie faktycznych czasów wykonania. W tym konkretnym przypadku
- zapytanie 0 działa przez ~ 500ms
- zapytanie 1 działa przez ~ 1500ms
- zapytanie 2 działa przez ~ 1500ms
- zapytanie 3 działa przez ~ 2000ms.
Plan nr 2 ma skanowanie indeksu zamiast wyszukiwania, które jest następnie filtrowane według predykatów zagnieżdżonych pętli. Plan nr 3 nie jest taki zły, ale nadal działa więcej i działa wolniej niż plan nr 0.
Załóżmy, że dbo.Paramszmienia się to rzadko i zwykle ma około 1–200 wierszy, nie więcej niż, powiedzmy, że 2000 się kiedykolwiek spodziewamy. Teraz jest około 10 kolumn i nie spodziewam się, aby dodawać kolumnę zbyt często.
Liczba wierszy w Params nie jest stała, więc dla każdego @session_idbędzie wiersz. Liczba kolumn, które nie zostały naprawione, jest to jeden z powodów, dla których nie chcę dzwonić dbo.f_GetSharedData(@experiment_year int, @experiment_month int)zewsząd, dlatego mogę wewnętrznie dodać nową kolumnę do tego zapytania. Z przyjemnością usłyszę wszelkie opinie / sugestie na ten temat, nawet jeśli ma pewne ograniczenia.
