?
Solved

Create a View that is more efficient for dynamic banding

Posted on 2004-09-27
11
Medium Priority
?
505 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
[X]
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
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: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
      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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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: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.
0
 
LVL 19

Accepted Solution

by:
grant300 earned 1000 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:Scott Pletcher
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

770 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