View with fallback (performance/optimization question)

Question

I have a table with tariffs for stuff; the tariffs table is not important in this scenario, the "tariff values" are. In this Demonstration SQL Fiddle the tariff_plan is the FK to the tariffplans table (not included in the example). The tariff for each "thing" is the tariff_type (simplified to a simple char for demonstration purposes).

I have, for example, a default tariffplan (key = default); this is the tariff that goes for each customer unless another value is defined for the same tariff_type for that customer. A customer is assigned a tariffplan (key = plan_x in my example).

If have tariffs defined for items a, b, c and d in the default plan. In plan_x I define "override" values for a and c.

So, what I do is I select the default plan (alias p below for primary) and left-join the "override" plan (plan_x) to it (alias s below for secondary):

select *
from tariff_values as p
left outer join tariff_values s 
    on (p.tariff_type = s.tariff_type) and (s.tariff_plan = 'plan_x')
where (p.tariff_plan = 'default')

This results, as expected, in:

id   tariff_plan tariff_type tariff_foo tariff_bar id   tariff_plan tariff_type tariff_foo tariff_bar
---- ----------- ----------- ---------- ---------- ---- ----------- ----------- ---------- ----------
1    default     a           0.10       0.20       5    plan_x      a           0.09       0.19
2    default     b           0.55       0.66       NULL NULL        NULL        NULL       NULL
3    default     c           1.99       2.99       6    plan_x      c           0.99       1.99
4    default     d           9.99       6.33       NULL NULL        NULL        NULL       NULL

Because I want to abstract this away I want to put this into a table valued function so I can create a "dynamic view":

select * from dbo.get_tariffplan_for('plan_x', default);

This should result in a "virtual table" (or "dynamic view") similar to the tariff_values table, thus: not having two tariff_foo's and two tariff_bar's and let the application decide which one to use. And so, I resort to ISNULL and Case when... constructs to "override" the default values:

select p.tariff_type,
    ISNULL(s.tariff_foo, p.tariff_foo) as tariff_foo, 
    ISNULL(s.tariff_bar, p.tariff_bar) as tariff_bar,
    ISNULL(s.tariff_plan, p.tariff_plan) as tariff_plan,
    CASE WHEN s.id IS NULL THEN 1 ELSE 0 END as isfallback
from tariff_values as p
left outer join tariff_values s
    on (p.tariff_type = s.tariff_type) and (s.tariff_plan = 'plan_x')
where (p.tariff_plan = 'default')

This results in:

tariff_type tariff_foo tariff_bar tariff_plan isfallback
----------- ---------- ---------- ----------- -----------
a           0.09       0.19       plan_x      0
b           0.55       0.66       default     1
c           0.99       1.99       plan_x      0
d           9.99       6.33       default     1

All I need to do now is stuff this query into a TVF:

CREATE FUNCTION get_tariffplan_for
(   
    @customerplan as varchar(50),
    @defaultplan as varchar(50) = 'default'
)
RETURNS TABLE 
AS RETURN 
(
    select p.tariff_type,
        ISNULL(s.tariff_foo, p.tariff_foo) as tariff_foo, 
        ISNULL(s.tariff_bar, p.tariff_bar) as tariff_bar,
        ISNULL(s.tariff_plan, p.tariff_plan) as tariff_plan,
        CASE WHEN s.id IS NULL THEN 1 ELSE 0 END as isfallback
    from tariff_values as p
    left outer join tariff_values s
        on (p.tariff_type = s.tariff_type) and (s.tariff_plan = @customerplan)
    where (p.tariff_plan = @defaultplan)
);

And there we have it. We can call our function ("dynamic view") as intended (and also use it in selects/joins etc.)

select * from dbo.get_tariffplan_for('plan_x', default);

--or:

select *
from foo
inner join dbo.get_tariffplan_for('plan_x', default) bar
    on foo.tariff_type = bar.tariff_type

Now my first question is:

I have a feeling all these ISNULL (or COALESCE) and/or CASE WHEN ... stunts seem to complicate things unnecessarily and something tells me this can be done more efficiently. However, I can't come up with a better and/or more efficient alternative.

So I'm hoping someone here has some ideas on how to improve this.

My second question is:

What if I had a product (tariff_type q for example) that I sold exclusively to some customer; the tariff wouldn't be in the default tariff-plan so I'd have to add another select to the above (with a union) to get all exclusive tariffs for that customer in the resultset. That would result in a query like this:

select p.tariff_type,
    ISNULL(s.tariff_foo, p.tariff_foo) as tariff_foo, 
    ISNULL(s.tariff_bar, p.tariff_bar) as tariff_bar,
    ISNULL(s.tariff_plan, p.tariff_plan) as tariff_plan,
    CASE WHEN s.id IS NULL THEN 1 ELSE 0 END as isfallback,
    0 as isexclusive
from tariff_values as p
left outer join tariff_values s
    on (p.tariff_type = s.tariff_type) and (s.tariff_plan = @customerplan)
where (p.tariff_plan = @defaultplan)

UNION

--Exclusive values
select p.tariff_type,
    p.tariff_foo, 
    p.tariff_bar,
    p.tariff_plan,
    0 as isfallback,
    1 as isexclusive
from tariff_values p
left outer join tariff_values s
    on (p.tariff_type = s.tariff_type) AND (s.tariff_plan = 'default')
where p.tariff_plan = 'plan_x'
    and s.id is null

(Demonstrated in this SQL fiddle)

In the above example I use another left join with s.id is null (but that could be rewritten in other ways (using count, exists, not in, having...whatever)) to retrieve the customer-exclusive tariffs. But maybe there's a better way instead of the union?

Answer 1

a couple of questions, as my rep is too low to post them via comments

1. What are you trying to do with the output and to what extent can you change the data model?

   • If both the table layout and output has to be exactly as in your example, then I don't see any major improvements. Outer join + isnull isn't too bad.

     • To improve performance, you could:

       • ensure there are indexes on the keys you're joining on
       • start using synthetic keys for plans (should be better then a varchar)

     • If changing the DDL or data is ok, you can also:

       • create a dedicated table to contain the default values (with no field for tariff_plan). let's call this one the "default_tariff" table and the other one "specific_tariff_types"

2. About the last requirement, I'm guessing this means its possible to have tariff plans for which no default exists? A full outer join should work too.

3. I guess you're using a TVF to sidestep the headaches of putting this in a normal view ? I have a feeling it should be possible to use a normal view without performance penalties, though the query would become more complex.

here is a proposal:

CREATE FUNCTION get_tariffplan_for
(   
    @customerplan as varchar(50)
)
RETURNS TABLE 
AS RETURN 
(
    select p.tariff_type,
        ISNULL(s.tariff_foo, p.tariff_foo) as tariff_foo, 
        ISNULL(s.tariff_bar, p.tariff_bar) as tariff_bar,
        ISNULL(s.tariff_plan, p.tariff_plan) as tariff_plan,
        CASE WHEN s.id IS NULL THEN 1 ELSE 0 END as isfallback
    from default_tariff_values as p
    full outer join specific_tariff_values s
        on (p.tariff_type = s.tariff_type) and (s.tariff_plan = @customerplan)
);

edit: here is a proposal (without changing the data model)

CREATE FUNCTION get_tariffplan_for
(   
    @customerplan as varchar(50)
)
RETURNS TABLE 
AS RETURN 
(
    select p.tariff_type,
        ISNULL(s.tariff_foo, p.tariff_foo) as tariff_foo, 
        ISNULL(s.tariff_bar, p.tariff_bar) as tariff_bar,
        ISNULL(s.tariff_plan, p.tariff_plan) as tariff_plan,
        CASE WHEN s.id IS NULL THEN 1 ELSE 0 END as isfallback,
        CASE WHEN p.id IS NULL then 1 else 0 END as isexclusive
    from 
    (select tariff_type,tariff_foo,tariff_bar,tariff_plan 
    from tariff_values where tariff_plan = 'default') 
    as p
    full outer join 
        (select tariff_type,tariff_foo,tariff_bar,tariff_plan 
        from tariff_values where tariff_plan = @customerplan) 
        as s
        on (p.tariff_type = s.tariff_type)
);