SQL Server Performance

Query thoughts

Discussion in 'T-SQL Performance Tuning for Developers' started by brimba, Apr 5, 2005.

  1. brimba New Member

    Hello!<br /><br /><br />I have some thoughts about a query and database design.<br /><br />I have two tables.<br /><br />One table to store information about ones users properties.<br />One table to store information about ones wants properties.<br /><br />The layout for this is <br />CREATE TABLE [dbo].[properties](<br />[userId] [int] NOT NULL,<br />[propertyCategoryId] [int] NOT NULL,<br />[propertyChoicesMask] [int] NOT NULL,<br /> CONSTRAINT [PK_Properties] PRIMARY KEY CLUSTERED <br />(<br />[userId] ASC,<br />[propertyCategoryId] ASC,<br />[propertyChoicesMask] ASC<br />) ON [PRIMARY]<br />) ON [PRIMARY]<br /><br /><br />The layout for booth tables are the same.<br /><br />Here is some example data.<br /><br />--The own properties<br />INSERT INTO ownProperties (userId, propertyCategoryId, propertyChoicesMask) VALUES (1, 10, <img src='/community/emoticons/emotion-11.gif' alt='8)' /> <br />INSERT INTO ownProperties (userId, propertyCategoryId, propertyChoicesMask) VALUES (1, 12, 4)<br /><br /><br />INSERT INTO seekProperties (userId, propertyCategoryId, propertyChoicesMask) VALUES (1, 10, 107) <br />INSERT INTO seekProperties (userId, propertyCategoryId, propertyChoicesMask) VALUES (1, 12, 4)<br />INSERT INTO seekProperties (userId, propertyCategoryId, propertyChoicesMask) VALUES (2, 10, 11)<br />INSERT INTO seekProperties (userId, propertyCategoryId, propertyChoicesMask) VALUES (2, 12, 7)<br />INSERT INTO seekProperties (userId, propertyCategoryId, propertyChoicesMask) VALUES (2, 14, 12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /><br /><br />What we se here is that the user with UserID has the value (bit value) of 8 for category 10. And the value 4 for category 12.<br /><br />For the seek table, we notice that the user 1 wants to find properties with the bit value of 107 in category 10, and so on.<br /><br />Now I (as user 1) wants to find all other users that is seeking my properties.<br /><br />If we saved the property value for the user on one row we could easily do this by querying like: select userId from ownProperties where ((cat10 & cat10Mask)&gt;0) and ((cat12 & @cat12Mask)&gt;0). The problem is that we then would have to build our query dynamicly. But the worst problem is that we would have to create a new column for each new category that we add.<br /><br />So what I try is to normalize it some, but maintain speed.<br /><br />Can anyone come up with good solution, or maybe point me into another direction that will work and still have good maintainability and flexibility.<br /><br />Thanks!
  2. Adriaan New Member

    To find all other users seeking any of the properties that you own:

    SELECT O.userId, S.userId
    FROM ownProperties O
    INNER JOIN seekProperties S
    ON (O.propertyCategoryId = S.propertyCategoryId)
    WHERE (O.userId <> S.userId)
    AND (O.propertyChoicesMask & S.propertyChoicesMask) > 0

    If you need to search for a given category + choice, add:

    AND (O.propertyCategoryId = @Category)
    AND (O.propertyChoicesMask & @Choice) = @Choice
    AND (S.propertyChoicesMask & @Choice) = @Choice
  3. brimba New Member

    Hello!

    That works almost perfect. Thank you alot!

    The problem with it is that it will return users even though that the user that was found dont have all the properties that the requesting user has.

    So if user 1 has propeties 1,2,3 then the other user also has to have atleast all those properties.

    Like this:

    user1 is seeking for category 1,2,3 (the user that is searching)

    user2 has category 1,2
    user3 has category 1,2,3,4

    The result should only return user3 since that is the only user that has all the categories that the searching user has (user1)

    Thanks!
  4. Adriaan New Member

    Avoid using IN - not sure how you could use that, and it will probably take forever ...

    Assuming you have a table that lists all the property choices per category (let me call it CategoryOption) you could add a clause like:

    AND NOT EXISTS
    (SELECT T.CatOption FROM CategoryOption T
    WHERE T.CategoryId = O.propertyCategoryId
    AND (T.OptionValue & O.propertyChoicesMask) <> (T.OptionValue & S.propertyChoicesMask)
    AND (((T.OptionValue & O.propertyChoicesMask) > 0) OR ((T.OptionValue & S.propertyChoicesMask) > 0)))

    If the query finds is an option within the given category that doesn't match both masks, and at least one of the masks does match the option, then the row is not returned.
  5. brimba New Member

    Will that really work?

    Let me rephrase my question.

    You showed an example of "To find all other users seeking any of the properties that you own:"

    But I want to do this.

    "To find all other users seeking ALL of the properties that you own:"

    The user that is seeking may be seeking more properties but the only thing here that is important is that the seeking user has atleast the properties that I have (own).

    Thank you very much!
  6. Adriaan New Member

    I gather you do have a lookup table for the values corresponding to the choices that make up the mask for each category? Of course they will be simply 2^0, 2^1, 2^3, ..., but you do need a list of values against which to match each mask.

    ***

    Coming to think of it, I think the NOT EXISTS clause above is for finding "all other users who are seeking a number of properties, and you own all those properties". Is that close enough?

    But I guess "all other users seeking ALL of the properties that you own" can be expressed by dropping half of the last pair of criteria in the subquery:

    AND NOT EXISTS
    (SELECT T.CatOption FROM CategoryOption T
    WHERE T.CategoryId = O.propertyCategoryId
    AND (T.OptionValue & O.propertyChoicesMask) <> (T.OptionValue & S.propertyChoicesMask)
    AND ((T.OptionValue & S.propertyChoicesMask) > 0))

    Does that help?

    ***

    These NOT EXISTS clauses sure can cause migraines!
  7. brimba New Member

    I will try it out, and let you know how it goes.

    Thank you!
  8. brimba New Member

    I cannot get it to work.<br /><br />Here is some sample data:<br /><br />insert into usersSeekProperties (userId, propertyCategoryId, propertyChoicesId) values (42, 3, <img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />insert into usersSeekProperties (userId, propertyCategoryId, propertyChoicesId) values (42, 4, 3)<br />insert into usersSeekProperties (userId, propertyCategoryId, propertyChoicesId) values (42, 5, 7)<br /><br />insert into usersOwnProperties (userId, propertyCategoryId, propertyChoicesId) values (40051, 3, <img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />insert into usersOwnProperties (userId, propertyCategoryId, propertyChoicesId) values (40051, 4, 1)<br />insert into usersOwnProperties (userId, propertyCategoryId, propertyChoicesId) values (40051, 5, 4)<br />insert into usersOwnProperties (userId, propertyCategoryId, propertyChoicesId) values (40051, 6, 1)<br /><br />insert into usersOwnProperties (userId, propertyCategoryId, propertyChoicesId) values (40061, 3, <img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />insert into usersOwnProperties (userId, propertyCategoryId, propertyChoicesId) values (40061, 4, 1)<br /><br />insert into usersOwnProperties (userId, propertyCategoryId, propertyChoicesId) values (40071, 3, <img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />insert into usersOwnProperties (userId, propertyCategoryId, propertyChoicesId) values (40071, 4, 1)<br />insert into usersOwnProperties (userId, propertyCategoryId, propertyChoicesId) values (40071, 5, 32)<br /><br />insert into usersOwnProperties (userId, propertyCategoryId, propertyChoicesId) values (40081, 3, <img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />insert into usersOwnProperties (userId, propertyCategoryId, propertyChoicesId) values (40081, 4, 1)<br />insert into usersOwnProperties (userId, propertyCategoryId, propertyChoicesId) values (40081, 5, 4)<br /><br /><br />When you seek, you can have more than one bit "marked" as propertyChoicesId, but usersOwnProperties can only hold one bit.<br />You can see that since the userId 42 has the bitValue of 7 on category 5. 7 represent the bitvalues 4+2+1.<br /><br />The table that stores all the propertychoices look like this:<br /><br />CREATE TABLE [dbo].[propertyChoices](<br />[id] [int] IDENTITY(1,1) NOT NULL,<br />[propertyCategoryId] [int] NOT NULL,<br />[bitvalue] [bigint] NOT NULL<br />)<br /><br />At a later stage I will remove the id-column, but for now I use it.<br /><br />Example data can be this:<br /><br />insert into propertyChoices (propertyCategoryId, bitvalue) values (3, 1)<br />insert into propertyChoices (propertyCategoryId, bitvalue) values (3, 2)<br />insert into propertyChoices (propertyCategoryId, bitvalue) values (3, 4)<br />insert into propertyChoices (propertyCategoryId, bitvalue) values (3, <img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />insert into propertyChoices (propertyCategoryId, bitvalue) values (3, 16)<br />insert into propertyChoices (propertyCategoryId, bitvalue) values (3, 32)<br /><br />insert into propertyChoices (propertyCategoryId, bitvalue) values (4, 1)<br />insert into propertyChoices (propertyCategoryId, bitvalue) values (4, 2)<br />insert into propertyChoices (propertyCategoryId, bitvalue) values (4, 4)<br />insert into propertyChoices (propertyCategoryId, bitvalue) values (4, <img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /><br />insert into propertyChoices (propertyCategoryId, bitvalue) values (5, 1)<br />insert into propertyChoices (propertyCategoryId, bitvalue) values (5, 2)<br />insert into propertyChoices (propertyCategoryId, bitvalue) values (5, 4)<br />insert into propertyChoices (propertyCategoryId, bitvalue) values (5, <img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />...<br />insert into propertyChoices (propertyCategoryId, bitvalue) values (5, 512)<br />insert into propertyChoices (propertyCategoryId, bitvalue) values (5, 1024)<br /><br />As you can see each category has its "own" bitmask numbers that is raising ^.<br /><br />What I am trying to do with this is that the user 42 (usersSeekProperties) will find all userIds in usersOwnProperties that has atleast all of the propertycategorys that user 42 does, and also that matches the bitvalue. The bitvalue matching is the easy part for me, but I cannot find out how to make sure that the user in usersOwnProperties have all the categories that the seeking user has.<br /><br />With my example data the outcome should be like this:<br /><br />40051: Will be returned since it has all the categories and also atleast one bit is correct<br />40061: Will fail since one category is missing<br />40071: Will fail since the bit mask is wrong. 40071 has 32 (single) but user 42 is seeking (7) which is 1,2 and 4.<br />40081: Will be returned since it has all categories and atleast one bit is correct.<br /><br /><br />Thanks!
  9. Adriaan New Member

    Your initial post suggested you had two tables, ownProperties and seekProperties, and both had the bitmask. That is a nice setup for handling this kind of "condensed" information, and the logic I gave you should work with those tables plus propertyChoices - with some testing and tuning and perhaps some modification.

    I'm not sure how you could make it work with usersOwnProperties, which is basically a normalized table - normalized form is your enemy here.
  10. brimba New Member

    The usersOwnProperties is the same as ownProperties. I still have the two tables and one table with the possible category choices. Its not different from my first post.

    I dont know how you are thinking with that not exists(). Maybe you could explain your solution a bit more so I can understand how you think.

    Thanks!
  11. Adriaan New Member

    This kind of logic works in negation only, so there is no "positive" form - unless you want to work out the criteria for all possible combinations.

    The NOT EXISTS clause can be paraphrased as:

    "there is no propertyChoices entry that is not matched by the bitmasks of both ownProperties seekProperties for the same combination of UserId's for the same category".
  12. brimba New Member

    Cannot get this to work. Any other ideas?
  13. Adriaan New Member

    Still not sure exactly what tables you're working with, so could you give us a single script that creates the tables and inserts some values that we can work with.<br /><br />At the top of this thread you were using a bitmask value:<pre id="code"><font face="courier" size="2" id="code">INSERT INTO ownProperties (userId, propertyCategoryId, propertyChoicesMask) VALUES (1, 10, <img src='/community/emoticons/emotion-11.gif' alt='8)' /></font id="code"></pre id="code">... while in your last script you're using only a single option per row:<pre id="code"><font face="courier" size="2" id="code">INSERT INTO usersOwnProperties (userId, propertyCategoryId, propertyChoicesId) values (40051, 3, <img src='/community/emoticons/emotion-11.gif' alt='8)' /></font id="code"></pre id="code">
  14. brimba New Member

    I am sorry about the misleading name of the column. the column propertyChoicesId is a bitmask value, so its real name is propertyChoicesIdMask.

    I got it to work in one way. I need to try it out in a large scale now (in progress of generating posts).


    Here is how I did it.

    Maybe you can find some ways to make it better? As you can see I am using IN.

    DECLARE @tbl TABLE(
    userID INT
    )

    INSERT INTO
    @tbl
    SELECT
    o2.userId
    FROM
    usersSeekProperties s1
    INNER JOIN usersOwnProperties o2 ON s1.propertyCategoryId = o2.propertyCategoryId
    AND CompareCategoriesByUserId(42, o2.userId) = 1
    AND (s1.propertyChoicesIdMask& o2.propertyChoicesIdMask) > 0

    SELECT
    distinct s.userId
    FROM
    usersOwnProperties s
    WHERE
    userId IN(
    SELECT
    t1.userId
    FROM
    @tbl t1
    )
    AND
    (
    (SELECT count(*) FROM usersSeekProperties WHERE userId = 42)
    =
    (SELECT count(*) FROM @tbl t2 WHERE t2.userId = s.userId)
    )

    The function CompareCategoriesByUserId take two userIds and make sure that the second userId (o2.userId) has at least all of the categories that the first userId (in this case 42) has.
    After I have found all those I put them i a table and after that I need to do one more select to make sure (again) that it has all categories. I do that in the last statement (the compare of my counts()).

    Thank you!

Share This Page