Guru: The Uncertainty of Redundant Row Selection
February 8, 2021 Ted Holt
Is redundancy good or bad? I say it depends. According to Nassim Nicholas Taleb, “Redundancy is ambiguous because it seems like a waste if nothing unusual happens. Except that something unusual happens — usually.” I have seen some unusual behavior when joining database tables, but try as I might, I can’t figure out what that unusual behavior depends on. Let me show you what I mean.
First, we need some data for illustration. Let’s say that our company uses an ERP system that was designed for make-to-stock manufacturing. What the factory builds goes into the warehouse, and customer orders are filled from the stock in the warehouse. There is no link between sales orders and manufacturing orders.
However, the Powers That Be decided years ago, before we came onto the scene, that the business should operate on a make-to-order basis, which requires manufacturing orders to be linked to sales orders. That presented a problem for IT (or MIS or Data Processing or whatever they were calling business computing in those days). Some enterprising programmer suggested they store sales order and sales order line number in an unused text field for notes in the manufacturing order, and it’s been that way since.
Here are the tables.
create table sohdr ( OrderNbr dec(5) primary key, CustNbr dec(5), OrderDate dec(8)); create table sodtl ( OrderNbr dec(5), LineNbr dec(2), ItemNbr char(6), Quantity dec(3), Price dec(5,2), primary key (OrderNbr, LineNbr)); create table mo ( OrderNbr dec(5) primary key, Item char(6), Quantity dec(3), SchedDate dec(8), Notes char(30)); insert into sohdr values ( 100, 220, 20210115), ( 101, 318, 20210115), ( 102, 190, 20210116); insert into sodtl values ( 100, 1, 'AB-101', 12, 1.00), ( 100, 2, 'DA-033', 10, 20.00), ( 101, 1, 'AB-101', 6, 1.25), ( 102, 1, 'JH-712', 9, 2.00); insert into mo values ( 751, 'AB-101', 12, 20210118, '0010001');
There is a one-to-many relationship between sales order header and sales order detail, and a one-to-one relationship between sales order detail and manufacturing order.
Now it’s time to query the database. Someone we serve wants to know the customer we’re selling to, the items they’re purchasing, and the amount of money per item that we expect to get from the customer for sales order number 100.
-- Query 1 select h.OrderNbr, h.CustNbr, d.ItemNbr, d.Quantity * d.Price as Extended from sohdr as h join sodtl as d on h.OrderNbr = d.OrderNbr where h.OrderNbr = 100;
The query selects one row (record) from the sales order header table and one or more rows from the sales order detail. The desired row is indicated by the where clause.
Of course, the query will also only select detail rows for order 100, due to the nature of the equi-join operation. We don’t have to explicitly tell SQL that we only want detail rows for order 100, but we could, like this:
-- Query 2 select h.OrderNbr, h.CustNbr, d.ItemNbr, d.Quantity * d.Price as Extended from sohdr as h join sodtl as d on h.OrderNbr = d.OrderNbr where h.OrderNbr = 100 and d.OrderNbr = 100;
What do you think of the new WHERE clause? Not only did I test the header for order 100, I tested the detail table as well. I call this a redundant test. If you know of a formal term that’s already used for an unnecessary test against a secondary table, please let me know.
Does the redundant test improve the query? When I ran this code, I got worse performance with the redundant test than I did with Query 1. Visual Explain showed an additional logic step for Query 2. That strikes me as strange.
Next, I tried moving the redundant test into the JOIN.
-- Query 3 select h.OrderNbr, h.CustNbr, d.ItemNbr, d.Quantity * d.Price as Extended from sohdr as h join sodtl as d on h.OrderNbr = d.OrderNbr and d.OrderNbr = 100 where h.OrderNbr = 100;
Performance of Query 3 was even worse than that of Query 2. Query 1, the simplest of the three, performed best.
Now let’s add the manufacturing order to the mix. The recipient of our efforts has requested that we add the manufacturing order number and the date the product is to be built. They also only want to know about line 1 of the sales order.
-- Query 4 select h.OrderNbr, h.CustNbr, d.ItemNbr, d.Quantity * d.Price as Extended, m.OrderNbr, m.SchedDate from sodtl as d join sohdr as h on h.OrderNbr = d.OrderNbr join mo as m on d.OrderNbr = dec(substr(m.Notes,1,5),5,0) and d.LineNbr = dec(substr(m.Notes,6,2),2,0) where d.OrderNbr = 100 and d.LineNbr = 1;
How do you like that join from sales order detail to manufacturing order? I wish I could say that that sort of join is rare, but I’ve seen it many, many times over the years.
What will a redundant test do for this query?
-- Query 5 select h.OrderNbr, h.CustNbr, d.ItemNbr, d.Quantity * d.Price as Extended, m.OrderNbr, m.SchedDate from sodtl as d join sohdr as h on h.OrderNbr = d.OrderNbr join mo as m on d.OrderNbr = dec(substr(m.Notes,1,5),5,0) and d.LineNbr = dec(substr(m.Notes,6,2),2,0) where d.OrderNbr = 100 and d.LineNbr = 1 and m.Notes = '0010001';
Believe it or not, performance improves!
I had a situation like this in the not too distant past. When I told Run SQL Statements to run the query without the redundant test, the system churned until I finally canceled the query in disgust. When I added the redundant test, the result set popped up immediately.
We human beings, it seems to me, are comforted by hard-and-fast rules. We like to hear “Always do this!” and “Never do that!” But life has led me to believe that the best we can hope for in most cases are guidelines. This certainly applies to query optimization. So here are your guidelines for redundant row-selection tests.
- A redundant test may improve the performance of a query.
- A redundant test may degrade the performance of a query.
I wish I could give you something better, friend, but I don’t have it. The good news is that you don’t have to tune every query. Just tune the ones that don’t run fast enough. A redundant test may not help, but it’s worth a shot.