I'm trying to see if there's a way to trick SQL Server to use a certain plan for the query.
1. Environment
Imagine you have some data which is shared between different processes. So, suppose we have some experiment results which take a lot of space. Then, for each process we know which year/month of experiment result we want to use.
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
Now, for every process we have parameters saved in the table
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. Test data
Let's add some test data:
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. Fetching results
Now, it's very easy to get experiment results by @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
The plan is nice and parallel:
select
calculated_number,
count(*)
from dbo.f_GetSharedData(2014, 4)
group by
calculated_number
query 0 plan
4. Problem
But, to make usage of the data a bit more generic, I want to have another function - dbo.f_GetSharedDataBySession(@session_id int). So, straightforward way would be to create scalar functions, translating @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
And now we can create our function:
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
query 1 plan
The plan is the same except it's, of course, not parallel, because scalar functions performing data access make the whole plan serial.
So I've tried a several different approaches, like, using subqueries instead of scalar functions:
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
query 2 plan
Or using 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
query 3 plan
But I can't find a way to write this query to be as good as the one using scalar functions.
Couple of thoughts:
- Basically what I'd want is to being able to somehow tell SQL Server to pre-calculate certain values and then pass them further as constants.
- What could be helpful is if we had some intermediate materialization hint. I've checked a couple of variants (multi-statement TVF or cte with top), but no plan is as good as the one with scalar functions so far
- I know about coming improvement of SQL Server 2017 - Froid: Optimization of Imperative Programs in a Relational Database.I'm not sure it will help, though. It would've been nice to be proven wrong here, though.
Additional information
I am using a function (rather than selecting data directly from the tables) because it is much easier to use in many different queries, which usually have @session_id as a parameter.
I was asked to compare actual execution times. In this particular case
- query 0 runs for ~500ms
- query 1 runs for ~1500ms
- query 2 runs for ~1500ms
- query 3 runs for ~2000ms.
Plan #2 has an index scan instead of a seek, which is then filtered by predicates on nested loops. Plan #3 is not that bad, but still does more work and works slower that plan #0.
Let's assume that dbo.Params is changed rarely, and usually have around 1-200 rows, not more than, let's say 2000 is ever expected. It's around 10 columns now and I don't expect to add column too often.
The number of rows in Params is not fixed, so for every @session_id there'll be a row. Number of columns there is not fixed, it's one of the reasons I don't want to call dbo.f_GetSharedData(@experiment_year int, @experiment_month int) from everywhere, so I can add new column to this query internally.
I'd be glad to hear any opinions/suggestions on this, even if it has some restrictions.
