I have a MS SQL Server 2008 database with the following structure:
• table Persons with a PersonID and some data fields, e.g. Name and State,
• table Projects with Person1, Person2, Person3 and some data fields, e.g. ProjectTitle. I have 3 separate indexes on Person1, 2 and 3.
A person's ID may appear as Person1, Person2 or Person3 of the Projects table, when the person has participated in the project. There each several tens of thousands persons and projects in the database.
I would now like to SELECT all persons that match a certain criteria and count the number of projects they are in. So far I have used
SELECT COUNT(Projects.ProjectID) AS NumProjects,
LEFT JOIN Projects ON (Persons.PersonID = Projects.Person1 OR Persons.PersonID = Projects.Person2 OR Persons.PersonID = Projects.Person3)
GROUP BY Persons.Name
This takes a very long time: About 1 minute to return about 10.000 records. If I remove the 3rd condition and only compare to Person1 or 2, time is about the same. If I only compare to Person1, time is less than a second!
Unfortunately, I need to compare to all Person1, 2 and 3 - could you suggest I way I can cut the cost of the query down?
Thanks a lot for your advice,