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

Make sure you have indexes on foreign key columns.

Answer 2

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

Answer 3

Assuming that P.ProjectID and PType.ProjectTypeID are PRIMARY KEY's, create this index:

CREATE INDEX ix_ptran_emloyee_type_date ON ProjectTransaction(RefEmployeeID, Type, Date)

, and get rid of GROUP BY:

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

Answer 4

What does your query plan say? If you have two index scans on the same table, followed by a hash join (or similar) between the results of them both, create a compound index with the fields from both of the index scans - the DB can then do a single index lookup in the compound index.

Make sure you have up-to-date statistics. The database can optimise better when those are provided.

Answer 5

Indexes on all the foreign keys, obviously.

Also ProjectTransaction is your only really big table, and you are doing lots of filtering on it in the Where clause. I would add indexes to all the columns that you are filtering on:

ProjectTransaction.Date
ProjectTransaction.Type

edit: As you have a table there with 3.5 million rows, another optimisation 'technique' you might want to consider is upgrading the hardware on your SQL Server. Some extra memory or a few extra processors can sometimes be more cost-effective than development time spent optimising (depending on development costs and how much optimisation you are doing, obviously) ... e.g. see Hardware is Cheap, Programmers are Expensive.

Answer 6

The Database Engine Tuning Advisor will tell you if you just ask it.

Answer 7

All the advice so far has been sound, but if you want the expert's (i.e. SQL Server's) view, you could do worse than use the Database Engine Tuning Advisor. Even if you don't have extensive real-world data, the Adviser can make some sensible suggestions based on a small set of test data.