Join multiple tables by multiple grouping

Question

We have a passing control system and every pass action is stored Event table in MSSQL Server. We want to join multiple tables with the Event table according to their relations as shown on the image below. However, I am not sure if the grouping approach that I used is correct or not because the query takes a lot of time. Could you please clarify me oh how to join these tables by multiple grouping? Here is the JOIN clause I used:

SELECT t.CardNo, t.EventTime, t1.EmployeeName, 
    t1.Status, t2.EventCH, t3.DoorName, t4.JobName, t5.DeptName 
FROM Event t 

LEFT JOIN Employee AS t1 ON t.EmployeeID = t1.ID 
LEFT JOIN EventType AS t2 ON t.EventTypeID = t2.ID 
LEFT JOIN Door AS t3 ON t.DoorID = t3.ID 
LEFT JOIN Job AS t4 ON t1.JobID = t4.ID 
LEFT JOIN Department AS t5 ON t1.DepartmentID = t5.ID

ORDER BY t.EventID Desc

Update: Posted execution plan below:

Answer 1

Hey have you tried creating two CTE's to group the joins?

So in one CTE create a join for Employee, Department and Job. For the other CTE create a join for Event, Eventtype and Door. Then in the end, join the two CTE's using Employee ID and ID.

Aggregating the joined tables together might be quicker than doing the joins all in one go. By the way, hows the unique constraint for each table?

;WITH a AS

(
SELECT
    t1.ID
   ,t1.EmployeeName
   ,t1.Status
   ,t4.JobName
   ,t5.DeptName
FROM
    Employee t1
LEFT JOIN Job t4
    ON t1.JobID = t4.ID
LEFT JOIN Department t5
    ON t1.DepartmentID = t5.ID
)
,b AS
(
SELECT
    t.EmployeeID
   ,t.CardNo
   ,t.EventTime
   ,t2.EventCH
   ,t3.DoorName
FROM
    [Event] t
LEFT JOIN EventType t2
    ON t.EventTypeID = t2.ID
LEFT JOIN Door t3
    ON t.DoorID = t3.ID 
)

SELECT
    *
FROM
    b
LEFT JOIN a
    ON b.EmployeeID = a.ID

Answer 2

The query in your question is not representative of the query in the query plan. In particular, it has the condition

RIGHT(t.cardno, 8) = RIGHT(t1.cardno, 8)

The use of functions precludes the use of indexes for the plan.

My suggestion is to add a virtual column to each table, index that, and then use that in the join:

alter table tEvent add cardno8 as RIGHT(cardno, 8);
alter table tEmployee add cardno8 as RIGHT(cardno, 8);

create index idx_tEvent_cardno8 tEvent(cardno8);
create index idx_tEmployee_cardno8 tEmployee(cardno8);

This should help the performance, if you change the comparison to:

on t.cardno8 = t1.cardno8

In addition, your query has no WHERE clause, so it is (presumably) processing a lot of data.