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)
);

• RobIII: 1) I want the output to be a ("dynamic") view so I can abstract all the shenanigans behind it away and base new queries on this view instead. I can change the datamodel but don't see a reason to (there are indices). The synthetic key is there, I've left it out for clarity of the demo (1, 2 vs. "default", "plan_x"). I can create a default_tariff table but don't see the point as it contains the exact same data (e.g. a self-join will accomplish the same). 2) True; when no default tariff exists it's a "customer exclusive". 3) True; I'm trying to abstract the view and "hide" the "base query".
• RobIII: So in short (ran out of comment-space :P ) I'm trying to avoid having to repeat the same pattern over-and-over again for queries that are based on this table; stuffing this in a "dynamic view" (TVF) will save me from most headaches further on, avoiding (unnecessary) "complex" queries. Anyway, I might sound reserved but your feedback is appreciated. Thanks!
• r.m: well, sounds fair. I'm out of ideas then though :)
• r.m: learned something new :) pressing enter in comment fields posts them immediately and they can't be edited after 5 minutes. oh well. let me retry: well, sounds fair. I'm out of ideas then though :) About (2) - true, a self-join will work too and be quite fast if the indexes and such are set. There is a difference but in this case it's probably academical. Joining a largish table (containing all tariff values) to itself is going to be slightly slower than joining a large table (all tariff values) to a small one (default tariff values), everything else being equal.