Sql have required indexes.But still it is running slow.How to increase performance of the query
Also, there are some possibly unnecessary bits of code in there that you might be able to drop out. For example, the WHERE 1=1 clauses always evaluate to TRUE and are entirely unneeded. The ORDER BY statements add more steps to the plan. If you can move the ORDER BY from the INSERT statements to the SELECT * FROM #constituents, you'll save a lot of work. The most draining, though, are the GROUP BY clauses. I don't see any use of aggregate function calls, so can you live without the GROUP BY clauses and still get the same result set? If so, you'll save a TON of processing.