October 29, 2014 at 4:32 pm
If object_id('tempdb..#LD') is not null drop table #LD
create table #LD(ID int identity, LA_Type_ID int, LA_Value int, LID bigint unique, MText varchar(255))
insert into #LD values
(22901, 1652, 1231, 'ABC'),
(22901, 1652, 1232, 'ABC'),
(22901, 1653, 1233, 'ABC'),
(22901, 1654, 1234, 'ABC'),
(22901, 1655, 1235, 'ABC'),
(22902, 64, 1236, 'ABC'),
(22902, 67, 1237, 'ABC'),
(22902, 68, 1238, 'ABC'),
(22902, 64, 1239, 'ABC')
-- select * from #LD
If object_id('tempdb..#MD') is not null drop table #MD
create table #MD(UID int, BID varchar(255), BWG varchar(255))
insert into #MD values (567, '1652,1654', '64,67')
-- select * from #MD
-- Expected Result:
-- For every row in #LD table we need to check the #MD table. When LA_Type_ID = 22901 then we need to check #MD table BID column and when its 22902 then BWG column.
-- (22901, 1652, 1231, 'ABC'), -- Include this row in result set as LA_Type_ID 22901 and LA_Value is 1652 and its exists in #MD Table BID column
-- (22901, 1652, 1232, 'ABC'), -- Include this row in result set as LA_Type_ID 22901 and LA_Value is 1652 and its exists in #MD Table BID column
-- (22901, 1653, 1233, 'ABC'), -- Exclude this row as LA_Vlaue 1653 is not match in #MD table BID column
-- (22901, 1654, 1234, 'ABC'), -- Include this row in result set as LA_Type_ID 22901 and LA_Value is 1654 and its exists in #MD Table BID column
-- (22901, 1655, 1235, 'ABC'), -- Exclude this row as LA_Vlaue 1655 is not match in #MD table BID column
-- (22902, 64, 1236, 'ABC'), -- Include this row in result set as LA_Type_ID 22902 and LA_Value is 64 and its exists in #MD Table BWG column
-- (22902, 67, 1237, 'ABC'), -- Include this row in result set as LA_Type_ID 22902 and LA_Value is 67 and its exists in #MD Table BWG column
-- (22902, 68, 1238, 'ABC'), -- Exclude this row as LA_Vlaue 68 is not match in #MD table BWG column
-- (22902, 64, 1239, 'ABC') -- Include this row in result set as LA_Type_ID 22902 and LA_Value is 64 and its exists in #MD Table BWG column
-- Please remember I have big data set in #LD table
October 29, 2014 at 5:14 pm
inayatkhan (10/29/2014)
If object_id('tempdb..#LD') is not null drop table #LDcreate table #LD(ID int identity, LA_Type_ID int, LA_Value int, LID bigint unique, MText varchar(255))
insert into #LD values
(22901, 1652, 1231, 'ABC'),
(22901, 1652, 1232, 'ABC'),
(22901, 1653, 1233, 'ABC'),
(22901, 1654, 1234, 'ABC'),
(22901, 1655, 1235, 'ABC'),
(22902, 64, 1236, 'ABC'),
(22902, 67, 1237, 'ABC'),
(22902, 68, 1238, 'ABC'),
(22902, 64, 1239, 'ABC')
-- select * from #LD
If object_id('tempdb..#MD') is not null drop table #MD
create table #MD(UID int, BID varchar(255), BWG varchar(255))
insert into #MD values (567, '1652,1654', '64,67')
-- select * from #MD
-- Expected Result:
-- For every row in #LD table we need to check the #MD table. When LA_Type_ID = 22901 then we need to check #MD table BID column and when its 22902 then BWG column.
-- (22901, 1652, 1231, 'ABC'), -- Include this row in result set as LA_Type_ID 22901 and LA_Value is 1652 and its exists in #MD Table BID column
-- (22901, 1652, 1232, 'ABC'), -- Include this row in result set as LA_Type_ID 22901 and LA_Value is 1652 and its exists in #MD Table BID column
-- (22901, 1653, 1233, 'ABC'), -- Exclude this row as LA_Vlaue 1653 is not match in #MD table BID column
-- (22901, 1654, 1234, 'ABC'), -- Include this row in result set as LA_Type_ID 22901 and LA_Value is 1654 and its exists in #MD Table BID column
-- (22901, 1655, 1235, 'ABC'), -- Exclude this row as LA_Vlaue 1655 is not match in #MD table BID column
-- (22902, 64, 1236, 'ABC'), -- Include this row in result set as LA_Type_ID 22902 and LA_Value is 64 and its exists in #MD Table BWG column
-- (22902, 67, 1237, 'ABC'), -- Include this row in result set as LA_Type_ID 22902 and LA_Value is 67 and its exists in #MD Table BWG column
-- (22902, 68, 1238, 'ABC'), -- Exclude this row as LA_Vlaue 68 is not match in #MD table BWG column
-- (22902, 64, 1239, 'ABC') -- Include this row in result set as LA_Type_ID 22902 and LA_Value is 64 and its exists in #MD Table BWG column
-- Please remember I have big data set in #LD table
These are all done using simple SELECTs. Please post the code that you've tried because most folks 1) don't want to do your job for your or 2) don't want to do your homework for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 29, 2014 at 9:29 pm
I tried this and its work but its taking little bit time if there is no other way or best approach then I can use this one.
select a.* from #LD a
where exists (select 1 from #md b
where (a.LA_Type_ID = 22901 AND ','+b.BID+',' like '%,' + convert(varchar(10),a.LA_Value) + ',%')
OR (a.LA_Type_ID = 22902 AND ','+b.BWG+',' like '%,' + convert(varchar(10),a.LA_Value) + ',%'))
October 30, 2014 at 10:04 pm
I had eye surgery today and can't see very well, yet. I'll have to get back to this. Hopefully, someone will jump in in my place because I'm not going to be able to look at the computer for any long period of tine for the next couple of days.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 31, 2014 at 2:21 am
Any chance #md table can be refactored? Type of
create table #MD2(UID int, LA_Type_ID int, LA_Value int);
insert into #MD2 values
(567, 22901,1652)
,(567, 22901,1654)
,(567, 22902,64)
,(567, 22902,67)
Then query will have good chances to utilize indexes and probably perfom better.
select a.* from #LD a
where exists (select 1 from #md2 b
where a.LA_Type_ID = b.LA_Type_ID AND b.LA_Value = a.LA_Value);
October 31, 2014 at 2:48 am
How many rows in table #md?
What's the distribution of values in the columns BID & BWG?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 31, 2014 at 3:22 am
I think you should look into normalizing (splitting the strings so that each value has it's own row) the BID and BWG columns in the #MD table so that you can use straightforward joins. As a starting point you'll need to find out what the longest string values are in the BID and BWG columns.
Then, I suggest you have a close look at the following articles from Jeff Moden:
The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url]
Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url]
And read this blog post from Aaron Bertrand:
Split strings the right way – or the next best way
Between them they should put you on the right track for a solution with decent performance.
October 31, 2014 at 5:49 am
Yes, I update #md table and its better now.
Thanks
October 31, 2014 at 5:57 am
jonharding2204 (10/31/2014)
I think you should look into normalizing (splitting the strings so that each value has it's own row) the BID and BWG columns in the #MD table so that you can use straightforward joins. As a starting point you'll need to find out what the longest string values are in the BID and BWG columns.Then, I suggest you have a close look at the following articles from Jeff Moden:
The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url]
Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url]
And read this blog post from Aaron Bertrand:
Split strings the right way – or the next best way
Between them they should put you on the right track for a solution with decent performance.
In seeing that article, I feel compelled to point out a significant difference in the functions. Jeff's articles you posted above use a varchar(8000) as the string parameter to be split and Aaron's function uses a varchar(max). I have gone pretty far down this road and they do not have the same performance.
I've tried this using 1M-row and 10M-row test tables. I used strings up to 7998 characters in length in a varchar(8000) column and got my baseline performance.
I then created a table with varchar(max) and used the exact same strings as I did in the baseline table with a new version of the function to handle the max and the execution time nearly doubled. I tried quite a few approaches to handle the max. While certain approaches produced better results than others, the performance of the original just wasn't there. The max data types are handled differently under the hood and they just don't like to be joined to.
If you need to split a varchar(8000) or nvarchar(4000), the delimited split functions are excellent tools. If you need to split a string that's larger, I would make a separate function and suffer the performance hit only when absolutely necessary.
Of course, the alternative to wrestling with it is to create a CLR and use it for everything.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply