CODEWITHSUNDEEP
sqlsql-serverquery-optimizationjoin
user8419075
user8419075

Reputation: 1

SQL Multiple Joins not working as expected

I have following query not working when I try to join all 4 tables (It is taking over an hour to run, I have to eventually kill the query without any data being returned). It works when Table 1,2 & 3 are joined AND Then If I try Table 1,2 & 4 join but not when I attempt to join all 4 tables below.

Select * From
    (Select 
        R.ID, R.MId, R.RId, R.F_Name, R.F_Value, FE.FullEval, M.Name, RC.CC     
        FROM Table1 as R
        Inner Join Table2 FE
                ON R.ID = FE.RClId and R.MId = FE.MId and R.RId = FE.RId
            Inner Join Table3 as M
                 ON R.MId = M.MId and FE.MId = M.MId
               Inner Join Table4 as RC
                  ON R.RId = RC.RId and FE.RId = RC.RId and FE.Date = RC.Date

    ) AS a

NOTE: 1) RId is not available in table3. 2) MId is not available in table4.

Thanks for help.

Upvotes: 0

Views: 1159

Answers (1)

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2063

Since you mentioned that you don't have permission to view the query plan, try breaking down into each table join. You can also check which table join is taking time to retrieve records. From there, you can investigate the data why it's taking time. It may be because of non-availability of column keys in Table 3 and Table 4?

WITH Tab1_2 AS
(SELECT r.ID, r.MId, r.RId, r.F_Name, r.F_Value, fe.FullEval, fe.date
   FROM Table1 as r
  INNER JOIN Table2 fe
     ON r.ID = fe.RClId
    AND r.MId = fe.MId
    AND r.RId = fe.RId
  WHERE ... -- place your conditions if any
),

Tab12_3 AS
(SELECT t12.*, m.Name
   FROM Tab1_2 t12
  INNER JOIN Table3 as m
     ON t12.MId = m.MId
  WHERE ... -- place your conditions if any
),

Tab123_4 AS
(SELECT t123.ID, t123.MId, t123.RId, t123.F_Name, t123.F_Value, t123.FullEval, rc.CC
   FROM Tab12_3 t123
  INNER JOIN Table4 as rc
     ON t123.RId = rc.RId 
    AND t123.Date = rc.Date
  WHERE ... -- place your conditions if any
)

SELECT *
  FROM Tab123_4 t1234

Upvotes: 1

Related Questions