The attached execution plan doesn't include the UNION. A UNION ALL there if you know there are no duplicates would help some. Also your execution plan doesn't show 1.2 million rows it is showing 35 rows total.

In your first query you do understand that the LEFT JOIN to EVALUATION is turned into an INNER JOIN because of the criteria WHERE "e.EVALUATION_ID = @EvaluationID" so you may not be getting the result you expect and because of this the only LEFT JOIN you are actually getting is to EVALUATION_CRITERIA_DETAIL.

The correlated sub-query to get the max(score) is most likely your biggest performance killer. I normally try to do something like that using either CROSS/OUTER APPLY or a CTE/Derived table to get the SUM. I might to it something like this:

SELECT

e.EVALUATION_ID,

efcd.EVALFORM_CRITERIA_ID,

efc.TITLE_NAME,

efc.BASE_CRITERIA_ID,

efcd.EVALFORM_CRITERIA_DETAIL_ID,

efc.CHILD_EXIST,

efc.PRIORITY_ORDER,

efc.WEIGHTAGE,

NULL, --efc.ANSWER_TYPE,

-- 'A', 1, GETDATE(), efc.LEVEL, efcd.SCORE

'A',

1,

GETDATE(),

efc.LEVEL,

detailScore.score

FROM

EVALFORM ef

INNER JOIN EVALFORM_CRITERIA efc

ON ef.EVALFORM_ID = efc.EVALFORM_ID

LEFT JOIN EVALFORM_CRITERIA_DETAIL efcd

ON efc.EVALFORM_CRITERIA_ID = efcd.EVALFORM_CRITERIA_ID

OUTER APPLY (

SELECT

MAX(score) AS score

FROM

EVALFORM_CRITERIA_DETAIL

WHERE

EVALFORM_CRITERIA_ID = efcd.EVALFORM_CRITERIA_ID AND

STATUS = 'A' AND

OMIT_FROM_SCORE = 'N'

) AS detailScore

INNER JOIN EVALUATION_DETAIL ed

ON efcd.EVALFORM_CRITERIA_DETAIL_ID = ed.EVALFORM_CRITERIA_DETAIL_ID

INNER JOIN EVALUATION e

ON ed.EVALUATION_ID = e.EVALUATION_ID

WHERE

e.EVALUATION_ID = @EvaluationId AND

efc.STATUS = 'A'

UNION

SELECT

@EvaluationId,

efc.EVALFORM_CRITERIA_ID,

efc.TITLE_NAME,

efc.BASE_CRITERIA_ID,

0,

efc.CHILD_EXIST,

efc.PRIORITY_ORDER,

efc.WEIGHTAGE,

NULL, --efc.ANSWER_TYPE,

'A',

1,

GETDATE(),

efc.LEVEL,

NULL --efcd.SCORE

FROM

EVALFORM ef

INNER JOIN EVALFORM_CRITERIA efc

ON ef.EVALFORM_ID = efc.EVALFORM_ID

LEFT JOIN EVALFORM_CRITERIA_DETAIL efcd

ON efc.EVALFORM_CRITERIA_ID = efcd.EVALFORM_CRITERIA_ID

WHERE

efc.CHILD_EXIST = 'Y' AND

efc.STATUS = 'A' AND

ef.EVALFORM_ID = @EvalformId