Create a View that is more efficient for dynamic banding

I have the following view:

  price_range =
         WHEN instock_units = 0   THEN '0'
         WHEN instock_units > 0   and instock_units < 5     THEN '1'
         WHEN instock_units >= 5 and instock_units < 15   THEN '2'
         WHEN instock_units >= 15 and instock_units < 30   THEN '3'
                                                                                ELSE '99'
FROM openorderline

I would like to create a better solution by creating a table like:
Key   LBound UBound     Description
0      0          0              Zero
1      0          5              1-5
2      5          15            5-15
3      15        30            15-30
99                 100000      Maximim

So when I want to change the banding I simply update the banding table and I don't need top update the view.
Who is Participating?
He wanted a data driven approach.  He already had a view and did not want to have to maintain it each time.

BTW, I liked your computed column approach.  It eliminates having to code to something other than the
base table.

I agree with the level of effort and pointed out in my original post that he could do the maintenance on the view
many times over for the investment in time he would have creating the procedure to build the view for him.  
A neat excersize perhaps but the cost/benefit is dubious.

You can do what is called a "Theta Join" which means the join criteria is based on a range of values, typically a BETWEEN predicate instead of an "=" predicate.

Just create the view with a joing between OPENORDERLINE and BANDING_TABLE such that instock_units between LBound and UBound.

You will want to reset the LBound and UBound values so there is no overlap since BETWEEN is inclusive.

That said, I would not do it.  The problem is that you are adding a bunch of overhead with the join that you don't need.  The CASE statement is
going to execute faster and, when part of more complex queries, confuse the optimizer less.

If you want a "data driven" way to maintain the banding, you could create a table to maintain the information as you have outlined and also
create a procedure that uses dynamic SQL to drop and recreate the view based on the contents of that table.  No more often than you are
likely to change the banding, I am not sure that you would ever get even that investment in time back.

Scott PletcherSenior DBACommented:
I agree that a static CASE statement should perform better than a join.  However, I also agree that you need flexibility.  Also, you should avoid dynamic SQL unless necessary.

Therefore, I suggest you create a computed column on the table that determines the band.  When values change, you can drop and recreate the computed column *once* and no other code will need to change.  For example:

ALTER TABLE openOrderLine
ADD price_range AS
         WHEN instock_units = 0   THEN '0'
         WHEN instock_units > 0   AND instock_units < 5     THEN '1'
         WHEN instock_units >= 5 AND instock_units < 15   THEN '2'
         WHEN instock_units >= 15 AND instock_units < 30  THEN '3'
                                                                                   ELSE '99'

Queries can use price_range just like any other column, but SQL will compute it dynamically when, and if, it's referenced:

SELECT price_range, cust_number
FROM openOrderLine
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

tbochatonAuthor Commented:
This table is used to load a cube into Analysis Services just once in the morning. My users make ALL their queries against the cube, so overhead/performance for the view isn't a showstopper. I would prefer to use a banding table as it would also be used to create my "Banding" dimension.

Gee.  The dynamic SQL I suggested was just so he doesn't have to edit and/or type in the view definition.   It would just build the "CREATE VIEW" command and execute the string once.

What you are proposing is the equivalent of creating a view and still requires him to edit/run DDL when he wants to change something.

If you wanted to eliminate the MANUAL EFFORT TO MAINTAIN the banding, building a routine that either creates a view OR a computed column using Dynamic SQL is the only option.

Scott PletcherSenior DBACommented:
>> If you wanted to eliminate the MANUAL EFFORT TO MAINTAIN the banding <<

Isn't manual effort also required to change rows in the table?  Some manual effort's unavoidable, isn't it?

Long term it's probably worth using the dynamic SQL to create the view, but it will be a fair amount of coding to make it work properly and to test it.
I doubt the banding table approach will cost a great deal in performance. The table will be very small and is likely to stay in memory in its entirety.

If the performance cost does tunr out to be significant, it might be possible to sidestep the join cost by precomputing the  instock_units to band relationship using another query:

create view instockPriceRange
select distinct O.instock_units, B.price_range
from (select distinct instock_units  from openorderline) O inner join banding_table B on O.instock_units between B.LBound and B.UBound

That does the theta join just once for each distinct value of instock_units as opposed to once for each row in the original table. How much of a saving that gives you depends on the  frequency distribution of instock_units.


You have reduced the cost of the join somewhat, however you have incurred a potentially large cost to do the table scan on instock_units to get the distinct values.  Doesn't seem like like much of an advantage to me.  In the case where you are only going to look at a small subset of instock_units, it adds a relatively large amount of overhead.

I think you have pretty well proven my point that anything other than the CASE statement in the view or the computed column adds a not insignificant amount of overhead.

If the author wants to provide a maintenance screen for the banding, he could have the application edit the table then call the PL/SQL I alluded to above to take the contents of the table and create the new version of the view/computed column.

Scott PletcherSenior DBACommented:
He'd better call T-SQL instead because PL/SQL won't run in SQL Server :-)
So many subject areas, so little time... :-)
tbochatonAuthor Commented:
I was looking for a data driven approach, but I will live with changing the View every time the banding changes.

Thanks to all for their responses. I can only accept one, sorry!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.