Apply search criteria on dynamic field table without using grouping or pivoting

Question

I have following two tables, one table is of person and another table is for storing various dynamic properties/information about the person.

 Id | Persons               PersonId | Field  | Value         
----+-------------         ----------+--------+-----------
 1  | Peter                      1   | City   | New York 
 2  | Jane                       1   | Age    | 26
                                 2   | City   | New York
                                 2   | Age    | 50

Can I apply a search condition in an sql query like person with age > 25 and city = 'New York' without grouping or pivoting the table.
What is best way to apply the search criteria with least performance overhead.

How are you going to pass the parameters, especially the operator (e.g. greater than, equals)? — Szymon, Nov 07 '13 at 09:23
But you don't know what the criteria might be, right? Because otherwise something simple like `select PersonId from table2 where [field] = 'age' and [value] > 25` would work. — Szymon, Nov 07 '13 at 09:29

AlexK · Accepted Answer · 2013-11-07T14:45:38.840

SELECT key1.PersonId 
FROM KeyValue key1
    INNER JOIN KeyValue key2 ON key1.PersonId = key2.PersonId
WHERE key1.[Field] = 'Age' and key1.[Value] > 25
    AND key2.[Field] = 'City' and key2.[Value] = 'New York'

Update

I did some tests and INNER JOIN looks fast enough. Here result and test script

SET NOCOUNT ON 
SET STATISTICS IO ON

CREATE TABLE KeyValue (
    ID INT NOT NULL IDENTITY CONSTRAINT [PK_KeyValue] PRIMARY KEY CLUSTERED
    ,PersonId INT NOT NULL
    ,Field varchar(30) NOT NULL
    ,Value varchar(255) NOT NULL
    ,CONSTRAINT UQ__KeyValue__PersonId_Field UNIQUE (PersonId, Field)
)
GO
--INSERT INTO KeyValue 500K "users", 4 "Fields" - 2M rows

CREATE NONCLUSTERED INDEX [IX__KeyValue__Field_Value_ID]
ON [dbo].[KeyValue] ([Field],[Value]) INCLUDE ([PersonId])
GO

select PersonId from (
    select PersonId, ROW_NUMBER() OVER (PARTITION BY PersonId ORDER BY PersonId) RowNumber from (
        select PersonId from KeyValue where [Field] = 'Age' and [Value] > 25 union all
        select PersonId from KeyValue where [Field] = 'City' and [Value] = 'Sydney' union all
        select PersonId from KeyValue where [Field] = 'Email' and [Value] = 'xxxxx@gmail.com' union all
        select PersonId from KeyValue where [Field] = 'Name' and [Value] = 'UserName' 
    ) x
) y where RowNumber = 4
--Table 'KeyValue'. Scan count 20, logical reads 1510, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

select PersonId from (
        select PersonId from KeyValue where [Field] = 'Age' and [Value] > 25 union all
        select PersonId from KeyValue where [Field] = 'City' and [Value] = 'Sydney' union all
        select PersonId from KeyValue where [Field] = 'Email' and [Value] = 'xxxxx@gmail.com' union all
        select PersonId from KeyValue where [Field] = 'Name' and [Value] = 'UserName' 
) x GROUP by PersonId
HAVING COUNT(*) = 4
--Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'KeyValue'. Scan count 4, logical reads 1377, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SELECT key1.PersonId 
FROM KeyValue key1
    INNER JOIN KeyValue key2 ON key1.PersonId = key2.PersonId
    INNER JOIN KeyValue key3 ON key1.PersonId = key3.PersonId
    INNER JOIN KeyValue key4 ON key1.PersonId = key4.PersonId
WHERE key1.[Field] = 'Age' and key1.[Value] > 25
    AND key2.[Field] = 'City' and key2.[Value] = 'Sydney'
    AND key3.[Field] = 'Email' and key3.[Value] = 'xxxxx@gmail.com' 
    AND key4.[Field] = 'Name' and key4.[Value] = 'UserName' 
-- Table 'KeyValue'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SET STATISTICS IO OFF
GO

Szymon · Answer 2 · 2013-11-07T10:31:20.330

1

You can use the following structure:

select PersonId from
(
    select PersonId from xxx where [Field] = 'Age' and [Value] > 25
    union all
    select PersonId from xxx where [Field] = 'City' and [Value] = 'New York' 
) x
group by PersonId
having count(*) = 2

You can create one more query for the union for each parameter. So this way it will return each PersonId as many times as it matches the criteria. Then you can select those PersonId that match all the criteria, i.e. count(*) equals number of parameters.

You can extend that to more parameters easily.

This should perform well if you have the right indexes on Field and Value.

This is a version without group by (though it has the same effect):

select PersonId from
(
    select PersonId, ROW_NUMBER() OVER (PARTITION BY PersonId ORDER BY PersonId) RowNumber from
    (
        select PersonId from xxx where [Field] = 'Age' and [Value] > 25
        union all
        select PersonId from xxx where [Field] = 'City' and [Value] = 'Sydney' 
    ) x
) y
where RowNumber = 2

edited Nov 07 '13 at 10:31

answered Nov 07 '13 at 09:38

Szymon

42,577
16
96
114

I want to avoid Group by :) – Asif Mushtaq Nov 07 '13 at 10:16
Which SQL Server version is it for? – Szymon Nov 07 '13 at 10:25
Another version added – Szymon Nov 07 '13 at 10:31
what do you think about the solution AlexK provided? – Asif Mushtaq Nov 07 '13 at 10:42
1

It's ok if you have 2 parameters. But what about 5 or 10? – Szymon Nov 07 '13 at 10:43
2

If you have many parameters check [this topic](http://stackoverflow.com/questions/1504793/sql-design-approach-for-searching-a-table-with-an-unlimited-number-of-bit-fields). Also you can think about [Sparse columns](http://technet.microsoft.com/en-us/library/cc280604.aspx) – AlexK Nov 07 '13 at 11:09

tillmannen · Answer 3 · 2013-11-08T09:10:07.787

0

If I understand your question correct somthing like this would work:

SELECT 
  one.* 
FROM table1 AS one
INNER JOIN table2 AS two1 ON one.Id = two1.PersonId
INNER JOIN table2 AS two2 ON one.Id = two2.PersonId
WHERE (two1.field = 'age' AND two1.value > 25) 
AND (two2.field = 'city' AND two2.value = 'New York')

Good luck!

edited Nov 08 '13 at 09:10

answered Nov 07 '13 at 09:42

tillmannen

106
1
6

do you think it would return anything? – Asif Mushtaq Nov 08 '13 at 04:39

Apply search criteria on dynamic field table without using grouping or pivoting

3 Answers3