SQL Optimization Help

[vuyiswam]

Good Day All

i have the Following SQL Code, if i have a lot of Data it takes long, can you advice on SQl Optimization

-- Note this takes terms into account when making clashes on subject level
-- Count the number of curricula which cause a certain clash
select distinct m1.ID Modl1, m2.ID Modl2, cs1.currstrmid [CurrStrm], count( distinct n1.Curr) Clashes
into #ClashList
from #tmpCL CL
inner join tbl_node n1 on n1.id = CL.node1
inner join tbl_node n2 on n2.id = CL.node2
inner join tbl_modl m1 on m1.id = n1.modl -- link to the module table to look up the terms
inner join tbl_subj_strm ss1 on ss1.modlid=m1.id
inner join mtm_curr_strm cs1 on cs1.subjstrmid=ss1.id and cs1.nodeid=n1.id
inner join tbl_modl m2 on m2.id = n2.modl
inner join tbl_subj_strm ss2 on ss2.modlid=m2.id
inner join mtm_curr_strm cs2 on cs2.subjstrmid=ss2.id and cs2.nodeid=n2.id
--inner join tbl_term_cl on (m1.Term = Term1 and m2.Term = Term2) -- do the checks on terms
-- or (m1.Term = Term2 and m2.Term = Term1)
where m1.ID < m2.ID and n1.curr = n2.curr and cs1.currstrmid=cs2.currstrmid
group by m1.ID, m2.ID, m1.descr, m2.descr, cs1.currstrmid
union
select distinct m2.ID Modl1, m1.ID Modl2, cs1.currstrmid [CurrStrm], count( distinct n1.Curr ) Clashes
from #tmpCL CL
inner join tbl_node n1 on n1.id = CL.node1
inner join tbl_node n2 on n2.id = CL.node2
inner join tbl_modl m1 on m1.id = n1.modl
inner join tbl_subj_strm ss1 on ss1.modlid=m1.id
inner join mtm_curr_strm cs1 on cs1.subjstrmid=ss1.id and cs1.nodeid=n1.id
inner join tbl_modl m2 on m2.id = n2.modl
inner join tbl_subj_strm ss2 on ss2.modlid=m2.id
inner join mtm_curr_strm cs2 on cs2.subjstrmid=ss2.id and cs2.nodeid=n2.id
--inner join tbl_term_cl on (m1.Term = Term1 and m2.Term = Term2) -- do the checks on terms
-- or (m1.Term = Term2 and m2.Term = Term1)
where M2.ID < M1.ID and n1.curr = n2.curr and cs1.currstrmid=cs2.currstrmid
group by m1.ID, m2.ID, m1.descr, m2.descr, cs1.currstrmid

Thank you