Solved

Create a View that is more efficient for dynamic banding

Posted on 2004-09-27
11
460 Views
Last Modified: 2008-02-01
I have the following view:

SELECT
  price_range =
      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,  
  cust_number
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.
0
Comment
Question by:tbochaton
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 19

Expert Comment

by:grant300
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.

Bill
0
 
LVL 69

Expert Comment

by:ScottPletcher
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
      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
0
 

Author Comment

by:tbochaton
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.

Thanks,
0
 
LVL 19

Expert Comment

by:grant300
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.

Bill
0
 
LVL 69

Expert Comment

by:ScottPletcher
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 19

Accepted Solution

by:
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.

Bill
0
 
LVL 12

Expert Comment

by:monosodiumg
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
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.

0
 
LVL 19

Expert Comment

by:grant300
ID: 12172429
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
0
 
LVL 69

Expert Comment

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

Expert Comment

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

Author Comment

by:tbochaton
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!




0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
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
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

758 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now