October 15, 2018 at 8:48 am
So this is partially theoretical and partially based on a real situation with NDA applied. Since I can't name columns, I'll name faux columns that sound kinda ridiculous, but the question is real.
Say we have an Orders table where one order can have multiple sub-orders, one sub-order, or no sub-orders. Think of a restaurant where a party can have one check (the original order), split the check in half (a dutch date or 1 sub-order off the main order), or split the check in multiple ways (group party, 2+ sub-orders to the main order). And any matching in the order has to be done based not just on OrderNumber but on SubOrderNumber.
CREATE TABLE #Orders (OrderID INT IDENTITY(1,1) NOT NULL, OrderNumber VARCHAR(10), SubOrderNumber VARCHAR(5), TotalSum MONEY, OrderDate DATETIME);
INSERT INTO #Orders (OrderNumber, SubOrderNumber, TotalSum, OrderDate)
VALUES ('ACD1234', NULL, 50.00, '01/01/2018'),
('BDIS5648', NULL, 105.00, '07/04/2018'),
('15826DZE','90EED',80.00,'10/14/2018'),
('15826DZE','58DDH',35.00,'10/16/2018'),
('58dEW25','AAA',28.00,'09/26/2018'),
('5984322A',NULL,857.00,'08/03/2018');
CREATE TABLE #StagingOrders (OrderNumber VARCHAR(10), SubOrderNumber VARCHAR(5), TotalSum MONEY, OrderDate DATETIME);
INSERT INTO #StagingOrders (OrderNumber, SubOrderNumber, TotalSum, OrderDate)
VALUES ('15826DZE','90EED',80.00,'10/14/2018'),
('15826DZE','58DDH',35.00,'10/16/2018'),
('JJ89432',NULL,128.00,'10/15/2018'),
('IUEE2341','1234',87.50,'10/15/2018');
Now if I have to do a join on the tables that always includes SubOrderNumber, usually I do something like the below. Simply because NULL never equals NULL and I can't be sure that all records will always have a SubOrderNumber value filled out. But obviously that means the SubOrderNumber part of the JOIN is not SARGable and it might cause indexes to never be properly used.
SELECT so.*, o.*
FROM #StagingOrders so
INNER JOIN #Orders o
ON so.OrderNumber = o.OrderNumber
AND ISNULL(so.SubOrderNumber,'') = ISNULL(o.SubOrderNumber,'')
So my questions are: 1) Does anyone else have this issue?
2) Is there a way around the use of ISNULL() that properly utilizes indexes and makes this SARGable?
Like I said, this is based off of something I really have to do on a regular basis. In the particular case I'm thinking of, SubOrderNumber is filled in for approximately 30% of the record set and NULL for the rest of the record set. But I have to code down to the SubOrderNumber level of granularity for financial reasons. If the SON doesn't exist, then the financials exist on the ON level. Because of the ability for multiple SONs, though, I have to be able to match on them exactly.
Thoughts?
October 15, 2018 at 9:29 am
Brandie I've always done something like this to keep it Sarg-able; does that help? it's an OR, i know, but it would allow indexes to be used if they exist on the columns AND (so.SubOrderNumber =o.SubOrderNumber OR (so.SubOrderNumber IS NULL AND o.SubOrderNumber IS NULL) )
Lowell
October 15, 2018 at 9:36 am
Never use ISNULL() in a JOIN (or WHERE) is a good rule.
To avoid issues with NULL, perhaps you could use empty string as no suborder rather than NULL?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 15, 2018 at 10:01 am
I experience this everyday with transactions at work. Dealing in insurance a Policy might be co-insured, meaning that the multiple insurers will insure parts of the risk. If that is the case, a transaction will also had "sub transactions" for each co-insurer. If, however, the product isn't something that isn't split then it won't have any co-insurer transactions. So, for transaction (and policy) details I might have something like:
Like, Lowell, though, if i'm looking for a specific insurer, I use an OR, but I actually JOIN to the table twice and then ISNULL in the SELECT:
This generally retains the SARGability.
Edit: Grrr, SSC pasting game and single quote handling...
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 15, 2018 at 10:01 am
ScottPletcher - Monday, October 15, 2018 9:36 AMNever use ISNULL() in a JOIN (or WHERE) is a good rule.To avoid issues with NULL, perhaps you could use empty string as no suborder rather than NULL?
The source data doesn't allow for that. The tables in question are loaded by other processes (some from other systems) which I can't change for various reasons.
October 15, 2018 at 10:02 am
Lowell - Monday, October 15, 2018 9:29 AMBrandie I've always done something like this to keep it Sarg-able; does that help? it's an OR, i know, but it would allow indexes to be used if they exist on the columnsAND (so.SubOrderNumber =o.SubOrderNumber OR (so.SubOrderNumber IS NULL AND o.SubOrderNumber IS NULL) )
Huh. I'll check that out. See what changes I see. Thanks, Lowell.
October 15, 2018 at 10:08 am
Thom A - Monday, October 15, 2018 10:01 AMLike, Lowell, though, if i'm looking for a specific insurer, I use an OR, but I actually JOIN to the table twice and then ISNULL in the SELECT:SELECT P.B@,
P.Polref@,
L.Trantype,
ISNULL(Lc.coinsurer, L.Insurer) AS Insurer,
ISNULL(Lc.Premium, L.Premium) AS Premium
FROM dbo.[Policy] P
JOIN dbo.Ledger L ON P.B@ = L.B@ AND P.Polref@ = L.Polref@
LEFT JOIN dbo.Ledger Lc ON L.B@ = Lc.B@ AND L.Polref@ = Lc.Polref@
AND L.Suffix = Lc.MasterSuffix
WHERE L.Premium > 0
AND (Lc.Coinsurer = 'Aviva'
OR (Lc.Polref@ IS NULL AND L.Insurer = 'Aviva'))
AND L.Trantype IN ('New Business','Renewal','Cancellation','Endorsement');This generally retains the SARGability.
And you don't run into problems with duplicate records by joining the table twice?
October 15, 2018 at 10:19 am
Brandie Tarvin - Monday, October 15, 2018 10:08 AMThom A - Monday, October 15, 2018 10:01 AMLike, Lowell, though, if i'm looking for a specific insurer, I use an OR, but I actually JOIN to the table twice and then ISNULL in the SELECT:SELECT P.B@,
P.Polref@,
L.Trantype,
ISNULL(Lc.coinsurer, L.Insurer) AS Insurer,
ISNULL(Lc.Premium, L.Premium) AS Premium
FROM dbo.[Policy] P
JOIN dbo.Ledger L ON P.B@ = L.B@ AND P.Polref@ = L.Polref@
LEFT JOIN dbo.Ledger Lc ON L.B@ = Lc.B@ AND L.Polref@ = Lc.Polref@
AND L.Suffix = Lc.MasterSuffix
WHERE L.Premium > 0
AND (Lc.Coinsurer = 'Aviva'
OR (Lc.Polref@ IS NULL AND L.Insurer = 'Aviva'))
AND L.Trantype IN ('New Business','Renewal','Cancellation','Endorsement');This generally retains the SARGability.
And you don't run into problems with duplicate records by joining the table twice?
No, as I'm handling the transaction type in there WHERE. The subtransaction always has the transaction type "coinsured", so I can always identify those. In yours it looks like the subtransaction has NULL if there are none, so that could work. I admit, your set up is a little different to mine, as you don't have a "lead" transaction, where as as I do. Effectively I treat it like a hierarchy (which is known to have a maximum of 2 levels).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 15, 2018 at 10:32 am
Well, I'm going to start with Lowell's solution first, on account of these staging tables being wide and long. See how that works. Then maybe later I'll try the self-outer join to see how that works.
Thanks everyone for your assistance. At first blush, it appears a whole minute + was cut off of an ad-hoc query, so I have high hopes this works for the bigger coding I have to do.
October 15, 2018 at 10:57 am
Brandie Tarvin - Monday, October 15, 2018 10:32 AMWell, I'm going to start with Lowell's solution first, on account of these staging tables being wide and long. See how that works. Then maybe later I'll try the self-outer join to see how that works.Thanks everyone for your assistance. At first blush, it appears a whole minute + was cut off of an ad-hoc query, so I have high hopes this works for the bigger coding I have to do.
Lowell's is definitely the right step for what you have here as it stands. Getting rid of those ISNULL's should be a massive boon. Saw loads here when I got rid of them all in some of the SP's.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply