Multi-row matching - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Multi-row matching

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-26 : 05:47:46

Hi all,

I have an EAV-designed table with PersonData that I need to search and I need a match on *all* search criteria and I think the query at the end gives me the correct result. Is there *any way* of making this more efficient?? I have clustered index seeks across the board but maybe there are more efficient ways of doing it...? This PersonData-table can potentially have millions of rows so I need to make sure that it is as efficient as possible. I would like to do as much filtering as possible within the query in cte1 but I'm a little unsure if it's even possible. I thought about making a csv-list of the DataColumnID's but haven't gotten to that point yet...and I don't know if it will be any better or not.

DECLARE @DataColumns table (
  DataColumnID int IDENTITY(1, 1) NOT NULL,
  ColumnName varchar(200)
  )

DECLARE @PersonData table (
  DataID int IDENTITY(1, 1) NOT NULL,
  DataColumnID int,
  PersonID int,
  Value varchar(2000)
  )

DECLARE @SearchCriteria table (
  DataColumnID int,
  SearchValue varchar(2000)
  )

INSERT INTO @DataColumns
SELECT 'ForumName' UNION ALL SELECT 'Description' UNION ALL SELECT 'PostCount'

INSERT INTO @PersonData
SELECT 1, 1, 'Lumbago' UNION ALL 
SELECT 2, 1, 'Norsk Yak Master' UNION ALL
SELECT 3, 1, '1890' UNION ALL 
SELECT 1, 2, 'tkizer' UNION ALL 
SELECT 2, 2, 'Almighty SQL Goddess' UNION ALL 
SELECT 3, 2, '26768' UNION ALL 
SELECT 1, 3, 'Peso' UNION ALL 
SELECT 2, 3, 'Patron Saint of Lost Yaks' UNION ALL
SELECT 3, 3, '26572' UNION ALL 
SELECT 1, 4, 'visakh16' UNION ALL 
SELECT 2, 4, 'Very Important crosS Applying yaK Herder' UNION ALL 
SELECT 3, 4, '25408'

INSERT INTO @SearchCriteria
SELECT 1, 'go' UNION ALL
SELECT 2, 'Yak' 

;WITH 
  cte1 (PersonID, DataColumnID, Value, SearchValue) AS (
    SELECT a.PersonID, a.DataColumnID, a.Value, b.SearchValue
    FROM @PersonData a
      INNER JOIN @SearchCriteria b
        ON a.DataColumnID = b.DataColumnID
          AND a.Value LIKE '%' + b.SearchValue + '%'
    ),
  cte2 (PersonID, RowNum) AS (
    SELECT PersonID, COUNT(*)
    FROM cte1
    GROUP BY PersonID
    )
    
SELECT a.PersonID, c.ColumnName, a.Value, a.SearchValue
FROM cte1 a
  INNER JOIN cte2 b
    on a.PersonID = b.PersonID
  INNER JOIN @DataColumns c
    ON a.DataColumnID = c.DataColumnID
WHERE b.RowNum = (SELECT COUNT(*) FROM @SearchCriteria)

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-26 : 06:52:09

how about this ?


select	a.PersonID, a.ColumnName, a.Value, a.SearchValue
from
(
	SELECT 	a.PersonID, c.ColumnName, a.Value, b.SearchValue, 
		RowCnt = count(*) over (partition by PersonID)
	FROM 	@PersonData a
		INNER JOIN @SearchCriteria b	ON 	a.DataColumnID = b.DataColumnID
						AND 	a.Value LIKE '%' + b.SearchValue + '%'
		INNER JOIN @DataColumns c	ON 	a.DataColumnID = c.DataColumnID
) a
where	a.RowCnt = (select count(*) from @SearchCriteria)

KH
[spoiler]Time is always against us[/spoiler]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-26 : 07:14:15

Excellent khtan...I haven't worked enough with these windowing functions so see the full potential yet but thanx alot. The estimated execution plan on my real table says 58% query cost on the cte-version and 42% on your version. I only have about 10 rows in my real table so there's not a whole lot to test on Are there other ways as well...?

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-26 : 07:28:07

Wait for Peso to come along .. .. he should have a better way

KH
[spoiler]Time is always against us[/spoiler]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-26 : 08:02:36

Hehe yes Peso is the man for these things although I must say that I'm very happy with your version also

Haven't seen him around for a long time now though...maybe too busy with those diapers

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-26 : 08:08:11

i bet he can make a query run faster than changing a diaper

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-26 : 08:18:46

Try . .


; WITH Search (DataColumnID, SearchValue, SearchCnt)
AS
(
	SELECT	DataColumnID, SearchValue, SearchCnt = COUNT(*) OVER()
	FROM	@SearchCriteria
)
SELECT	a.PersonID, a.ColumnName, a.Value, a.SearchValue
FROM
(
	SELECT 	b.DataColumnID, a.PersonID, c.ColumnName, a.Value, b.SearchValue, 
		RowNo = COUNT(*) OVER (PARTITION BY PersonID), b.SearchCnt
	FROM 	@PersonData a
		INNER JOIN Search b		ON 	a.DataColumnID = b.DataColumnID
						AND 	a.Value LIKE '%' + b.SearchValue + '%'
		INNER JOIN @DataColumns c	ON 	a.DataColumnID = c.DataColumnID
) a
WHERE	a.RowNo	= SearchCnt

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-26 : 08:23:51

quote:
Originally posted by khtan

i bet he can make a query run faster than changing a diaper

KH
[spoiler]Time is always against us[/spoiler]

LOL
It depends ... on how much is inside the diaper

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-26 : 08:26:31

quote:
Originally posted by webfred

quote:
Originally posted by khtan

i bet he can make a query run faster than changing a diaper

KH
[spoiler]Time is always against us[/spoiler]

LOL
It depends ... on how much is inside the diaper

No, you're never too old to Yak'n'Roll if you're too young to die.

You are right. I guess the same also goes for the query

KH
[spoiler]Time is always against us[/spoiler]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-26 : 08:46:12

Well, the estimated execution plan says 44% to the last one and 56% on your previous one, but when it gets this close it's really hard to know if the results are accurate or not. Maybe I'll have to mock up a few million rows of test data and see what it looks like then.

If you got a script for generating data like this please let me know

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-26 : 09:03:02

Generating the data is probably easy. Verifying the result of the query that is the tough one

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources