Create a View that is more efficient for dynamic banding

Posted on 2004-09-27
Last Modified: 2008-02-01
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.
Question by:tbochaton
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
LVL 19

Expert Comment

ID: 12164570
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.

LVL 69

Expert Comment

by:Scott Pletcher
ID: 12164714
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

Author Comment

ID: 12164761
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.

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

LVL 19

Expert Comment

ID: 12164765
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.

LVL 69

Expert Comment

by:Scott Pletcher
ID: 12164818
>> 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.
LVL 19

Accepted Solution

grant300 earned 250 total points
ID: 12164926
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.

LVL 12

Expert Comment

ID: 12167983
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.

LVL 19

Expert Comment

ID: 12172429

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.

LVL 69

Expert Comment

by:Scott Pletcher
ID: 12172545
He'd better call T-SQL instead because PL/SQL won't run in SQL Server :-)
LVL 19

Expert Comment

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

Author Comment

ID: 12172877
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!


Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question