June 20, 2019 at 10:06 pm
We have a table where we keep rows with vanilla settings for products, and customized rows per product per customer as they see fit to override. When a customer does not override, the vanilla rows with vanilla settings are selected but when customized rows exists for products and customers, the customized rows must be returned and the vanilla rows thrown out.
I have the sample code and my solution using ROW_NUMBER() to guide the row selection. This ROW_NUMBER seems to make our query run longer so I am asking if anyone see better options to achieve the same results.
Thank you!
IF OBJECT_ID('tempdb..#TItems') IS NOT NULL
DROP TABLE #TItems
IF OBJECT_ID('tempdb..#TAttrib') IS NOT NULL
DROP TABLE #TAttrib
CREATE TABLE #TItems (ItemID int, Name varchar(50))
CREATE TABLE #TAttrib (AttribID int, ClinicID int , ItemID int, AutoReorder bit, isFavorite bit, IsControlled bit )
Insert into #TItems (ItemId, Name)
Select 1 as ItemID, 'Item1' as Name UNION
Select 2 as ItemID, 'Item2' as Name UNION
Select 3 as ItemID, 'Item3' as Name ;
INSERT INTO #TAttrib (AttribID , ClinicID, ItemID, AutoReorder , isFavorite , IsControlled )
SELECT 10, NULL, 1,1,1,1 UNION
SELECT 20, NULL, 2,1,1,1 UNION
SELECT 30, NULL, 3,1,1,1 UNION
SELECT 40, 200, 1, 0,0,1 UNION
SELECT 50, 500, 2,1,0,0 UNION
SELECT 60, 500, 3,1,1,0 ;
-- Clinics 100, 300, 400 do not have Attrib. They will use the vanilla attrib rows for items 1-3.
-- Clinic 200 has customized item 1, clinic 500 has customized item 3.
-- expected Attrib result set for clinic 100: AttribIDs 10, 20, 30
-- expected Attrib result set for clinic 200: AttribIDs 40, 20, 30
-- expected Attrib result set for clinic 500: AttribIDs 10, 50, 60
Declare @ClinicID int = 500
SELECT * FROM
(
SELECT AttribID, ClinicID , ItemID, ROW_NUMBER() OVER (
PARTITION BY ItemID ORDER BY ClinicID DESC
) RN FROM #TAttrib
where ClinicID is NULL or ClinicID = @ClinicID ) RS
WHERE RN = 1 June 20, 2019 at 11:05 pm
This was a bit of a logic mind-bender! Try this and see whether it's any quicker:
SELECT *
FROM #TAttrib ta
WHERE ta.ClinicID IS NULL
AND NOT EXISTS
(
SELECT 1
FROM #TAttrib ta2
WHERE ta2.ClinicID = @ClinicID
AND ta.ItemID = ta2.ItemID
)
OR ta.ClinicID = @ClinicID;
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 21, 2019 at 12:21 am
Hi Phil,
Pretty cool, your solution runs fastest compared to Row_Number solution and max/group by solution. Thank you very much!
June 21, 2019 at 11:02 am
With a small structure change
ALTER TABLE #TItems ADD
AutoReorder bit
, isFavorite bit
, IsControlled bit;
UPDATE i
SET AutoReorder = a.AutoReorder
, isFavorite = a.isFavorite
, IsControlled = a.IsControlled
FROM #TItems AS i
INNER JOIN #TAttrib AS a
ON i.ItemID = a.ItemID
AND a.ClinicID IS NULL;
DELETE FROM #TAttrib
WHERE ClinicID IS NULL;
This could be another possibility
DECLARE @ClinicID int = 500;
SELECT i.ItemID, i.Name
, AutoReorder = ISNULL( a.AutoReorder, i.AutoReorder )
, isFavorite = ISNULL( a.isFavorite, i.isFavorite )
, IsControlled = ISNULL( a.IsControlled, i.IsControlled )
FROM #TItems AS i
LEFT JOIN #TAttrib AS a
ON a.ItemID = i.ItemID
AND a.ClinicID = @ClinicID;
June 21, 2019 at 11:35 pm
Thank you, Desnorton. We run in Azure SQL on a low end service tier so we don't use temp tables much.
I will look into the other possibility.
June 22, 2019 at 4:38 am
Thank you, Desnorton. We run in Azure SQL on a low end service tier so we don't use temp tables much. I will look into the other possibility.
The temp tables are the ones that you supplied. So you can modify that to use your real tables.
June 23, 2019 at 3:47 pm
You can use CROSS APPLY for this:
Declare @clinicID int = 500;
Select *
From #TItems i
Cross Apply (Select Top 1
a.AttribID
, a.ClinicID
, a.ItemID
, a.AutoReorder
, a.isFavorite
, a.isControlled
From #TAttrib a
Where a.ItemID = i.ItemID
And (a.ClinicID = @clinicID Or a.ClinicID Is Null)
Order By
a.ClinicID desc
) c;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 25, 2019 at 5:11 pm
Please do a little research and look up the design flaw called EAV. You are reinventing it, and on top of that, you added assembly language style bit flags. You would actually be better off if you went to a non-relational pointer based database product.
You not only guaranteed yourself poor performance but have also lost data integrity and scalability.
Please post DDL and follow ANSI/ISO standards when asking for help.
June 25, 2019 at 8:33 pm
Thank you, Jeff. A good use of CROSS APPLY.
June 25, 2019 at 8:56 pm
Please do a little research and look up the design flaw called EAV. You are reinventing it, and on top of that, you added assembly language style bit flags. You would actually be better off if you went to a non-relational pointer based database product. You not only guaranteed yourself poor performance but have also lost data integrity and scalability.
Joe, SQL Server still doesn't support the Boolean data type which is the alternative to the bit flags recommended by the standards that you tout. You should be complaining to Microsoft, not berating people who are doing the best that they can with the system that they have at hand.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 25, 2019 at 9:03 pm
Thank you, Jeff. Another good use for CROSS APPLY!
June 25, 2019 at 9:29 pm
Cluster the Attrib table on ( ClinicID, ItemID ) and any of the methods will likely run fast enough, although I like the CROSS APPLY approach myself.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 25, 2019 at 9:35 pm
Point taken. On the real life table, we do have an index on ( ClinicID, ItemID ). I will probably drop the clustered PK and make ( ClinicID, ItemID ) a clustered index.
June 26, 2019 at 4:30 pm
Booleans have always been a problem in SQL. Our three value logic has problems from a mathematical viewpoint. Starting with the basics, Boolean datatype should have {true, false} as it's only allowed values, but we have {true, false, unknown} thanks to the NULLs. However, SQL has a couple of rules about those NULLs. The first is that all data types must be NULL-able. This means that we did have a Boolean datatype it would have to have the domain {true, false, unknown, NULL}. This problem was already troublesome for the BIT datatype in SQL Server. Earlier versions of this product allowed only {0,1} in the domain. This model followed the usual computer science conventions how we think about bits. But later that was changed to allow {0,1, NULL} and this became a numeric data type (with some restrictions on the math could be done with it).
The other law of nulls in this language is that they propagate. This would mean that if we do logical operations (and, or, not) with the NULL, we should expect to get a null result back. But the rules for three value logic state that we have to handle unknowns differently. Here's a quick look at some of the problems.
(true AND unknown) = unknown
(true AND null) = null --- law of propagation
(true OR unknown) = true
(true OR null) = null --- law of propagation
But wait it gets even worse! David McGovern loves to point out that SQL does not have conference rules so it's not really an inferential system.. This is why we refer to "search conditions" and not predicates were being really picky about the SQL standards. In particular, you need to have material implication to have a deductive system. Material implication into value logic can be defined by something called Smisteru's rule, (A IMP B) = (not-A AND B). Or it can be defined by a lookup table, following an extension of the two-valued logic definition. The convention has always been that a true premise cannot imply a false conclusion. Both the lookup table in this rule agree on that point, but they differ after that.
On top of that throw in that, the DDL and the DML have different rules. In DML, the {false, unknown} values are treated the same and reject a search condition. In the DDL, {true, unknown} values are treated the same and accept a check condition.
When I'm teaching it I usually tell people that we have a "predicate language" and can't use flags. The idea is that the three-valued logic cannot be folded back into the database layer. I usually at this point pull out my Masters in math and do some hand waving about the theory of types etc. etc. and bore the hell out of everybody.
.
Please post DDL and follow ANSI/ISO standards when asking for help.
June 27, 2019 at 5:50 pm
I totally agree with you. Please continue with your teachings about data types best practices.
Most of those settings are in binary state (1/0, true/false, Yes/No) and where they can be in binary state, we do not allow nulls and set a default of 0 to avoid issues with nulls in sql statements. These settings could have a datatype of char(1) instead of bit with a default value of course to prevent nulls.
Though I mention "settings" because most overrides are truly binary settings , there are overrides can be made on entity attributes. For ex: a drug that has a tradename such as Sodium Chloride 0.9% 500ml Excel Bag 24/cs can be:
500ml NS non-pvc bag as override for Clinic 1
*0.9%NS 500ml Excel Bag 24/cs as override for Clinic 2
0.9% Normal Saline 500cc Excel Bag 24/cs as override for Clinic 3
Sodium Chloride 0.9% 500ml non-dehp Bag 24/cs as override for Clinic 4
etc...
Most clinics are fine operating with the manufacturer tradename but a small percentage have their lingo and operate with their overridden names instead.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply