All geek questions in one place

What indexes optimize this four-join query?

I have a sql query with inner joins of four tables that take more than 30 seconds with current indexes and query structure. I would like to do it as quickly as possible; not less than 5 seconds.

At first I thought about denormalization, but I read here that in general, you can optimize using the correct indexes, etc. I can not understand this in this case. The current query plan contains an index scan on the smallest table and a warning about an invalid connection in one of the internal connections.

  • How can I optimize the speed of the next?
  • What are the indices?
  • What is the structure of the queries?
  • Other considerations?

We have the following tables (indicating the number of rows and corresponding fields):

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 summarize the hours for a given department, date range, etc. I am currently trying:

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 the "foreign keys" and criteria in the sentence WHERE.) I reordered the query to have two small tables first, then the average size, and the large one the last. And Veil takes 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
+3
7

, :

  • .

  • ProjectTransaction:

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

+8

, .

+1

, P.ProjectID PType.ProjectTypeID PRIMARY KEY 's, :

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

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')
+1

, .

ProjectTransaction - , Where. , :

ProjectTransaction.Date
ProjectTransaction.Type

edit: 3,5 , , , , , - SQL Server. , ( , )... . . , .

+1

? , - ( ) , - .

, . , .

0

, .

0

All the tips still sound, but if you want to see an expert opinion (for example, SQL Server), you can do worse than use the Database Engine Configuration Advisor . Even if you do not have extensive real data, the adviser can make some reasonable suggestions based on a small set of test data.

0
source

Source: https://habr.com/ru/post/1708152/

More articles:


All Articles