I have a stored procedure that is taking a long time to run. I have to optimize it.
While testing I found that there is a join that is taking a long time :
select t1.col1, t1.col2, count(distinct a.personid) e1, count(distinct b.personid) e2
from table1 t1
join messages m with(nolock) on m.messageid = t1.messageid
join messagerecipients mr with(nolock) on m.messageid = mr.messageid
left join people a with(nolock) on mr.personid = emp.personid and emp.persontypeid = 2
left join people b with(nolock) on mr.personid = con.personid and con.persontypeid = 1
The personid in messagerecipients does not have an index. So the join are taking a long time when I find the count in the select statement.
I do not intent to modify the production table for now to apply indexes.
Is there a way I can optimize my query
View Complete Post