0

Okay, so I know the title is a bit cryptic so I'll do what I can to explain the "problem" I have and the solution I am currently using.

Problem: An 'object' of work needs to be distributed to the apropriate user based on said object's properties. The idea is that there is an object of work has properties. Those properties are mapped to skills. A user has skills and is able to work on an object which is within the user's skillset. There are several [three] property definitions and I currently have the following table structures.

              |-- Object to Property Set 1 -- Property Set 1 to Skill --|
Object Table -|-- Object to Property Set 2 -- Property Set 2 to Skill --|-- User Skill -- User Table
              |-- Object to Property Set 3 -- Property Set 3 to Skill --|

The query may be a bit easier to understand:

  SELECT counts.object_id,
         COUNT(DISTINCT counts.object_skill) object_skill_count,
         COUNT(DISTINCT counts.user_skill) user_skill_count
  FROM
    (SELECT object.object_id,
            sp.skill_id object_skill,
            us.skill_id user_skill
     FROM object_table object
     LEFT JOIN object_property op ON op.object_id = object.object_id
     LEFT JOIN skill_property sp ON sp.property_id = op.property_id
     LEFT JOIN user_skill us ON us.skill_id = sp.skill_id
     AND us.active = 1
     AND us.user_id = {$userid} -- <=- inserted from a PHP script
     AND object.state = 1
     UNION SELECT object.object_id,
                  sf.skill_id object_skill,
                  us.skill_id user_skill
     FROM object_table object
     LEFT JOIN object_flag obf ON obf.object_id = object.object_id
     LEFT JOIN skill_flag sf ON sf.flag_id = obf.flag_id
     LEFT JOIN user_skill us ON us.skill_id = sf.skill_id
     AND us.active = 1
     AND us.user_id = {$userid} -- <=- inserted from a PHP script
     AND object.state = 1
     UNION SELECT object.object_id,
                  svf.skill_id object_skill,
                  us.skill_id user_skill
     FROM object_table object
     LEFT JOIN object_creator oc ON oc.creator_id = object.creator_id
     LEFT JOIN skill_creator sc ON sc.flag_id = oc.flag_id
     LEFT JOIN user_skill us ON us.skill_id = sc.skill_id
     AND us.active = 1
     AND us.user_id = {$userid} -- <=- inserted from a PHP script
     AND object.state = 1) counts
  GROUP BY counts.object_id

Here we get a count of all the skills an object as well as count the number of skills the user has on that same object. If the two counts match, we know the user can work on the object. If the object's skill count exceeds the user's count, the object is beyond the user's capabilities and will not be assigned to that user.

While the above query works, it slows significantly when thrown at a large[r] table. Would like to know if there is a better way of doing things. And, since the internet is filled with amazing people, here we are.

Retroactive Update: The Left joins in this case are there because objects can have no properties. This equates to the count 0-0 and thus makes the object workable by anyone.

kdougan
  • 333
  • 2
  • 10

1 Answers1

1

It looks ok. Conditions placed on data joins instead of where clause, no order by's.

Possible options:

1) Check for missing indexes

http://basitaalishan.com/2013/03/13/find-missing-indexes-using-sql-servers-index-related-dmvs/

2) Change 'left joins' to 'inner joins'

INNER JOIN vs LEFT JOIN performance in SQL Server

3) Use 'UNION ALL' instead of 'Union'

performance of union versus union all

Community
  • 1
  • 1
Shep
  • 638
  • 3
  • 15
  • Thanks Shep. I'll take a look at the links. To be honest, the stated solution was a drunken napkin sketch with my brother. :P Totally using it in production too! – kdougan Dec 18 '15 at 05:24
  • Well, thank you. Oh, and I forgot, the left joins are in there because null values are acceptable as objects can have no properties and are thus workable by anyone. – kdougan Dec 18 '15 at 05:54