My query takes very long time to execute it

Question

SELECT DISTINCT servRole,
                (SELECT PositionLongtext
                 FROM   tblPositionMaster tpm
                 WHERE  tsc.servRole = tpm.tblID)         AS ServPositionID,
                (SELECT EmployeeName
                 FROM   tblEmployeeList te
                 WHERE  tsc.ECNoServ = te.ECNo
                        AND te.PeriodName = @PeriodName)  AS SERVName,
                (SELECT ECNo
                 FROM   tblEmployeeList tl
                 WHERE  tsc.ECNoServ = tl.ECNo
                        AND tl.PeriodName = @PeriodName)  AS ECNoServ,
                CustRole,
                (SELECT PositionLongtext
                 FROM   tblPositionMaster tpm
                 WHERE  tsc.CustRole = tpm.tblID)         AS CustPositionID,
                (SELECT EmployeeName
                 FROM   tblEmployeeList tm
                 WHERE  tsc.ECNOCust = tm.ECNo
                        AND tm.PeriodName = @PeriodName)  AS CUSTName,
                (SELECT ECNo
                 FROM   tblEmployeeList tel
                 WHERE  tsc.ECNOCust = tel.ECNo
                        AND tel.PeriodName = @PeriodName) AS ECNOCust,
                (SELECT DirectoryName
                 FROM   tblAttributeDirectory td
                 WHERE  td.DirectoryID = tsc.DirectoryID) AS DirectoryName,
                (SELECT DirectoryID
                 FROM   tblAttributeDirectory td
                 WHERE  td.DirectoryID = tsc.DirectoryID) AS DirectoryID,
                FinalscoreEmp,
                Flag
FROM   tblServToCustMapping tsc
WHERE  tsc.PeriodName = @PeriodName
ORDER  BY ECNoServ 

Answer 1

You can rewrite the query using JOINs instead of the correlated subquery:

SELECT DISTINCT 
  servRole,
  tpm.PositionLongtext AS ServPositionID,
  te.EmployeeName AS SERVName, 
  tl. ECNo AS ECNoServ,
  CustRole,
  tpm.PositionLongtext AS CustPositionID,
  tm.EmployeeName AS CUSTName,
  tel.ECNo AS ECNOCust,
  td2.DirectoryName AS DirectoryName,
  td2.DirectoryID AS DirectoryID,
  FinalscoreEmp,
  Flag
FROM   tblServToCustMapping      tsc
INNER JOIN tblPositionMaster     tpm ON tsc.servRole   = tpm.tblID
INNER JOIN tblEmployeeList       te  ON tsc.ECNoServ   = te.ECNo
INNER JOIN tblEmployeeList       tl  ON tsc.ECNoServ   = tl.ECNo
INNER JOIN tblPositionMaster     tpm ON tsc.CustRole   = tpm.tblID
INNER JOIN tblEmployeeList       tm  ON tsc.ECNOCust   = tm.ECNo
INNER JOIN tblEmployeeList       tel ON tsc.ECNOCust   = tel.ECNo
INNER JOIN tblAttributeDirectory td1 ON td.DirectoryID = tsc.DirectoryID
INNER JOIN tblAttributeDirectory td2 ON td.DirectoryID = tsc.DirectoryID
WHERE tsc.PeriodName = @PeriodName
  AND te.PeriodName  = @PeriodName
  AND tl.PeriodName  = @PeriodName
  AND tel.PeriodName = @PeriodName
  AND tm.PeriodName  = @PeriodName
ORDER  BY ECNoServ;