Hi,

Interesting question.

Are the indexes you mentioned clustered or non-clustered? It is important. I'll presume they are clustered and can suggest to you to replace this set of data

(

SELECT a1.ProfileID, a2.ProfilePracticeAreaID

FROM dbo.Profiles a1

INNER JOIN

dbo.Profiles_PracticeAreas a2 ON a2.ProjectID = @ProjectID AND a2.ProfileID = a1.ProfileID

WHERE a1.ProjectID = @ProjectID

AND a1.StateID = @StateID

AND a2.PracticeAreaID = @PracticeAreaID

) a0

with a temp table a0 with clustered index on a0.ProfileID and then execute the query.

I'll be waiting for your results...and for the other suggestions as well.

Regards

IgorMi