T-SQL - Ways to get customized rows?

  • 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
  • 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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Hi Phil,

    Pretty cool, your solution runs fastest compared to  Row_Number solution and max/group by solution.  Thank you very much!

  • 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;
  • 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.

  • Coriolan wrote:

    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.

  • 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

  • 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. 

  • Thank you, Jeff.  A good use of CROSS APPLY.

  • jcelko212 32090 wrote:

    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

  • Thank you, Jeff.  Another good use for CROSS APPLY!

  • 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".

  • 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.

  • 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. 

  • 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