SQL Query to search a properties table...

[Wokawidget]

Hi,

I have two tables...Users and UserProperties

UserProperties has 4 columns:

UserPropertyId INT
UserId INT
PropertyKey VARCHAR(50)
DataValue VARCHAR(MAX)


The following query brings back my user properties:

SELECT UserProperties.*
FROM UserProperties


The following query brings me back ALL UserIds that have a FIRST_NAME set as "Woka":

SELECT UserProperties.UserId
FROM UserProperties
WHERE Userproperties.PropertyKey = 'FIRST_NAME'
AND DataValue = 'Woka'
GROUP BY Userproperties.UserId

The problem arrises when I want to search on multiple fields.

There are 2 ways I can see how to do this:

1) For each field I search on I need to add another INNER JOIN:

SELECT UserProperties.UserId
FROM UserProperties
INNER JOIN UserProperties Props2 ON Props2.UserId = UserProperties.UserId
WHERE UserProperties.PropertyKey = 'FIRST_NAME'
AND UserProperties.DataValue = 'Woka'
AND Props2.PropertyKey = 'LAST_NAME'
AND Props2.DataValue = 'Widget'
GROUP BY UserProperties.UserId

2) Decide what properties are searchable and create a pivot table in a view. I can then do:

SEELCT UserId
FROM vwUserSearch
WHERE vwUserSearch.FIRST_NAME = 'Woka'
AND vwUserSearch.LAST_NAME = 'Widget'

I don't like either.

Anyone got any ideas on this?

cheers,

Woka

[GaryMazzone]

What about

SELECT UserProperties.UserId
FROM UserProperties
WHERE 
    (Userproperties.PropertyKey = 'FIRST_NAME' AND DataValue = 'Woka')
    OR
     (PropertyKey = 'LAST_NAME' AND DataValue = 'Widget')

[Wokawidget]

That's an OR statement...I want and AND

[si_the_geek]

How about something like this?

SELECT UP.UserId
FROM UserProperties UP
WHERE UP.PropertyKey = 'FIRST_NAME'
  AND UP.DataValue = 'Woka'
  AND Exists(
       SELECT 1 
       FROM UserProperties 
       WHERE UserID = UP.UserID
         AND PropertyKey = 'LAST_NAME'
         AND DataValue = 'Widget'
     )
GROUP BY UP.UserId

While this is a bit more long winded than the join method, it does mean that you can easily make it optional based on a parameter (assuming you are using them), by changing:

  AND Exists( ... )

to:

  AND (@param=True OR Exists( ... ) )

[Wokawidget]

SELECT UP.UserId
FROM UserProperties UP
WHERE UP.PropertyKey = 'FIRST_NAME'
  AND UP.DataValue = 'Woka'
  AND Exists(
       SELECT 1 
       FROM UserProperties 
       WHERE UserID = UP.UserID
         AND PropertyKey = 'LAST_NAME'
         AND DataValue = 'Widget'
     )
   AND Exists(
       SELECT 1 
       FROM UserProperties 
       WHERE UserID = UP.UserID
         AND PropertyKey = 'CITY'
         AND DataValue = 'Glasgo'
     )
   AND Exists(
       SELECT 1 
       FROM UserProperties 
       WHERE UserID = UP.UserID
         AND PropertyKey = 'PET'
         AND DataValue = 'Badger'
     )
GROUP BY UP.UserId

Hmmm...I think one thing is for sure...It looks like I will have to dynamically generaly the SQL string in code and not use a stored procedure.

Is there anything LINQ can do here to help?

cheers for the repl,ies.

Woka

[Wokawidget]

also...looking at the query.
For every user there is the following code gets called:

SELECT 1 
       FROM UserProperties 
       WHERE UserID = UP.UserID
         AND PropertyKey = 'LAST_NAME'
         AND DataValue = 'Widget'

10000 users = 10000 EXISTS(SELECT 1... calls

10000 users and 10 properties being searching = 100000 EXISTS calls.

I need to go back to SQL and look at the performance of some of these queries.

cheers,

Woka

[si_the_geek]

If you are passing parameters for the values to the SP (with an option of Null for "do not use"), use the kind of method I showed at the end of my previous post - you will get the benefits of dynamic SQL and the usual benefits of an SP (including speed). The only problem is if the PropertyKey values are not predictable then an SP with this method wont help.


As to the new issue you have raised, it should be better to change this:

AND EXISTS(SELECT 1 FROM

to this:

AND UserID IN(SELECT UserID FROM

...and remove UserID = UP.UserID from the Where clause.

The will allow each subquery to run just once for all users, followed by a presumably much shorter search (assuming the criteria aren't too generic) for each specific user.