Data Joining technique help

  • Hey All,

    I'm wondering if there is a different/better way to join the data from these two tables. Please see my sample data and example query below. Cart_id is unique #mytable_main and not in #mytable_Krb2. I need the Field_name and field_values from #mytable_krb2 joined to #mytable_main. Hopefully, what I'm trying to do makes sense to everyone. Please let me know if there is a better way to do this or some technique that I can employ.

    In my environment #mytable_Krb2  is about 5.2 million rows, with 21K unique cart_id.

    #mytable_main is about 90K rows.

     IF OBJECT_ID('TempDB..#mytable_Krb2 ','U') IS NOT NULL
    DROP TABLE #mytable_Krb2

    CREATE TABLE #mytable_Krb2
    (
    cart_id nvarchar(50) not null,
    field_id nvarchar(50) not null,
    field_name nvarchar(2000) null,
    field_value nvarchar(2000) null,
    DateMod datetime null,
    LoadDate datetime not null
    PRIMARY KEY CLUSTERED
    (
    cart_id ASC,
    field_id ASC
    )ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    INSERT INTO #mytable_Krb2
    (Cart_id,field_id,field_name,field_value,DateMod,LoadDate)
    SELECT '01156','105','field1','Value1' ,'May 16 2023 3:20PM','May 16 2023 5:10PM' UNION ALL
    SELECT '01156','106','field33','Value33' ,'May 16 2023 3:20PM','May 16 2023 5:10PM' UNION ALL
    SELECT '01156','107','field2@','Value44' ,'May 16 2023 3:20PM','May 16 2023 5:10PM' UNION ALL
    SELECT '01689','100','fieldkl','Value5' ,'May 16 2023 3:20PM','May 16 2023 5:10PM' UNION ALL
    SELECT '01689','105','field1','Value1' ,'May 16 2023 3:20PM','May 16 2023 5:10PM' UNION ALL
    SELECT '01689','107','field2@','Value44' ,'May 16 2023 3:20PM','May 16 2023 5:10PM' UNION ALL
    SELECT '01689','110','FieldMar','value17' ,'May 16 2023 3:20PM','May 16 2023 5:10PM' UNION ALL
    SELECT '12589','110','FieldMar','value17' ,'May 16 2023 3:20PM','May 16 2023 5:10PM' UNION ALL
    SELECT '12589','100','field22','Value5' ,'May 16 2023 3:20PM','May 16 2023 5:10PM' UNION ALL
    SELECT '12589','105','field1','Value1' ,'May 16 2023 3:20PM','May 16 2023 5:10PM' UNION ALL
    SELECT '12589','106','field33','Value33' ,'May 16 2023 3:20PM','May 16 2023 5:10PM' UNION ALL
    SELECT '12589','107','field2@','Value44' ,'May 16 2023 3:20PM','May 16 2023 5:10PM' UNION ALL
    SELECT '12589','117','field44','RRRR55' ,'May 16 2023 3:20PM','May 16 2023 5:10PM' UNION ALL
    SELECT '11128','118','field455','RRValue3','May 16 2023 3:20PM','May 16 2023 5:10PM';




    ----------------------------------------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------------------------------------
    IF OBJECT_ID('TempDB..#mytable_main ','U') IS NOT NULL
    DROP TABLE #mytable_main

    CREATE TABLE #mytable_main
    (
    iid BIGINT not null,
    cart_id nvarchar(50) not null,
    Field_desc nvarchar(50) null,
    Value88 nvarchar(50) null,
    Mark33 nvarchar(2000) null,
    DateMark datetime null,
    LastDateMark datetime not null,
    Type3 nvarchar(2000) null,
    PRIMARY KEY CLUSTERED (
    iid,
    cart_id
    )
    ON [PRIMARY]

    ) ON [PRIMARY]
    GO

    INSERT INTO #mytable_main
    (iid,cart_id,Field_desc,Value88,mark33,DateMark,LastDateMark,Type3)
    SELECT '11225598','01156','#@$%^33' ,'Value44' ,'Mercc33','May 11 2023 3:20PM','May 16 2023 5:10PM','des' UNION ALL
    SELECT '11266598','01157','#@$%^33' ,'Value998' ,'Mercc33','May 16 2022 3:20PM','May 16 2023 5:10PM','eee' UNION ALL
    SELECT '11266699','01158','#@$%^33@' ,'Value333' ,'Mercc33','May 16 2021 3:20PM','May 16 2023 5:10PM','eee' UNION ALL
    SELECT '11267699','01629','#@$%^33-9988' ,'Value005' ,'Mercc33','May 12 2023 3:20PM','May 16 2023 5:10PM','des' UNION ALL
    SELECT '11367699','01641','fie$%^33@ld1' ,'Value100' ,'Mercc33','May 16 2019 3:20PM','May 16 2023 5:10PM','eee' UNION ALL
    SELECT '11387699','01689','f$%^33@d2@' ,'Value4400' ,'Mercc33','June 18 2023 3:20PM','May 16 2023 5:10PM','des' UNION ALL
    SELECT '11388699','01700','Fie$%^33@ldMar','value1007' ,'Mercc33','May 16 2023 3:20PM','May 16 2023 5:10PM','eee' UNION ALL
    SELECT '11388700','11128','gggg-33Mar','value107' ,'Mercc33','July 16 2023 3:20PM','May 16 2023 5:10PM','des' UNION ALL
    SELECT '11388710','12505','23643*' ,'Value335' ,'Mercc33','May 16 2023 3:20PM','May 16 2023 5:10PM','des' UNION ALL
    SELECT '11398770','12589','fie$%^31' ,'Value221' ,'Mercc33','January 8 2023 3:20PM','May 16 2023 5:10PM','eee' UNION ALL
    SELECT '11398777','13333','figg-33d33' ,'Value3443' ,'Mercc33','May 16 2023 3:20PM','May 16 2023 5:10PM','778' UNION ALL
    SELECT '11398787','13433','field2@' ,'Value4444' ,'Mercc33','May 16 2024 3:20PM','May 16 2023 5:10PM','778' UNION ALL
    SELECT '11399277','13435','f#@$-33d334' ,'RRRR5544' ,'Mercc33','May 16 2023 3:20PM','May 16 2023 5:10PM','des' UNION ALL
    SELECT '12001201','13437','fie@$%55','RRValue223' ,'Mercc33','May 16 2023 3:20PM','May 16 2023 5:10PM','778' UNION ALL
    SELECT '12088700','13482','gggg-33Mar','value107' ,'Mercc33','July 16 2023 3:20PM','May 16 2023 5:10PM','des' UNION ALL
    SELECT '12088710','13483','23643*' ,'Value335' ,'Mercc33','May 16 2023 3:20PM','May 16 2023 5:10PM','des' UNION ALL
    SELECT '12098770','13511','fie$%^31' ,'Value221' ,'Mercc33','January 8 2023 3:20PM','May 16 2023 5:10PM','eee' UNION ALL
    SELECT '12098777','13515','figg-33d33' ,'Value3443' ,'Mercc33','May 16 2023 3:20PM','May 16 2023 5:10PM','778' UNION ALL
    SELECT '12166598','13518','#@$%^33' ,'Value998' ,'Mercc33','May 16 2022 3:20PM','May 16 2023 5:10PM','eee' UNION ALL
    SELECT '12166699','13530','#@$%^33@' ,'Value333' ,'Mercc33','May 16 2021 3:20PM','May 16 2023 5:10PM','eee' UNION ALL
    SELECT '12167695','13553','#@$%^33-9988' ,'Value005' ,'Mercc33','May 12 2023 3:20PM','May 16 2023 5:10PM','des' UNION ALL
    SELECT '12167699','13563','fie$%^33@ld1' ,'Value100' ,'Mercc33','May 16 2019 3:20PM','May 16 2023 5:10PM','eee' UNION ALL
    SELECT '12187699','13683','f$%^33@d2@' ,'Value4400' ,'Mercc33','June 18 2023 3:20PM','May 16 2023 5:10PM','des' UNION ALL
    SELECT '12198787','11128','field2@' ,'Value4444' ,'Mercc33','May 16 2024 3:20PM','May 16 2023 5:10PM','778' ;


    ----------------------------------------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------------------------------------

    ---example of what I'm trying to do. ---

    SELECT mtt.*, kr1.field_value AS 'field1', kr2.field_value AS 'field2@'
    FROM #mytable_main AS mtt
    LEFT OUTER JOIN #mytable_Krb2 AS kr1
    ON mtt.cart_id = kr1.cart_id AND kr1.field_id = 105
    LEFT OUTER JOIN #mytable_Krb2 AS kr2
    ON mtt.cart_id = kr2.cart_id AND kr2.field_id = 107;
  • You should definitely switch the key order on the lookup table, i.e., not:

    PRIMARY KEY CLUSTERED ( iid, cart_id )

    but instead do this:

    PRIMARY KEY CLUSTERED ( cart_id, iid )

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

  • ScottPletcher wrote:

    You should definitely switch the key order on the lookup table, i.e., not:

    PRIMARY KEY CLUSTERED ( iid, cart_id )

    but instead do this:

    PRIMARY KEY CLUSTERED ( cart_id, iid )

    Thanks! took your advice and it reduced logical reads by 75% on the lookup table.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply