What indexes optimize this query with four joins?

Question

I have an sql query with inner joins of four tables that takes more than 30 seconds with the current indexes and query structure. I would like to make it as fast as possible; at least faster than 5 seconds.

I first thought about denormalizing, but read here that generally it should be possible to optimize via correct indexes etc. I cannot figure it out in this case. The current query plan contains an index scan on the smallest table and a 'no join predicate' warning on one of the inner joins.

• How can I optimize the speed of the following?
• Which indexes?
• Which query structure?
• Other considerations?

We have the following tables (with number of rows and relevant fields indicated):

TableName           Rows  Fields
------------------- ----- ----------------------------------------------
ProjectType         150   ProjectTypeID, ProjectTypeName
Employee            200   EmployeeID, RefDepartmentID
Project             0.2M  ProjectID, RefProjectTypeID
ProjectTransaction  3.5M  Hours, RefEmployeeID, RefProjectID, Date, Type

The query should sum the hours for a given department, date range, etc. Currently I try:

SELECT E.RefDepartmentID, SUM(PTran.Hours)
FROM Employee E
JOIN ProjectTransaction PTran
    ON E.EmployeeID = PTran.RefEmployeeID
JOIN Project P
    ON PTran.RefProjectID = P.ProjectID
JOIN ProjectType PType
    ON P.RefProjectTypeID = PType.ProjectTypeID
WHERE E.RefDepartmentID = @departmentID
    AND @from <= PTran.Date AND PTran.Date <= @to
    AND PTran.Type = 0
    AND PType.ProjectTypeName NOT IN (N'1', N'2', N'3')
GROUP BY E.RefDepartmentID

---

Thanks for all the quick answers. (I already had indexes on 'foreign keys' and criteria in WHERE clause.) I reordered the query to have the two small tables first, then the medium sized, and the big one last. And voila takes around one second:

SELECT E.RefDepartmentID, SUM(PTran.Hours)
FROM Employee E
JOIN ProjectType PType
    ON E.RefCustomerID = PType.RefCustomerID
JOIN Project P
    ON PType.ProjectTypeID = P.RefProjectTypeID
JOIN ProjectTransaction PTran
    ON E.EmployeeID = PTran.RefEmployeeID
    AND P.ProjectID = PTran.RefProjectID
WHERE E.RefDepartmentID = @departmentID
    AND @from <= PTran.Date AND PTran.Date <= @to
    AND PTran.Type = 0
    AND PType.ProjectTypeName NOT IN (N'1', N'2', N'3')
GROUP BY E.RefDepartmentID

Answer 1

It not always work, but try to:

1. Reorder tables in joins from the smallest one to the biggest one.

2. Use subquery instead of ProjectTransaction table:

   JOIN (SELECT RefEmployeeID, RefProjectID FROM ProjectTransaction WHERE @from <= PTran.Date AND PTran.Date <= @to AND PTran.Type = 0) AS trans