Sql-server – Emulate user-defined scalar function in a way which doesn’t prevent parallelism

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:

1. 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.
2. 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
3. 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.