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".
February 4, 2025 at 4:04 pm
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