Create a View that is more efficient for dynamic banding

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.

Bill

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
      CASE
         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'
         END



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

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.

Thanks,

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.

Bill

>> 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
as
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.

monosodiumg,

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.

Bill

He'd better call T-SQL instead because PL/SQL won't run in SQL Server :-)

So many subject areas, so little time... :-)

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!