Yikes. Just glanced back through and spotted this:
isnull(c.DBAction, '') != ''

That's another performance killer. You have a ton of tuning opportunities here without even looking at execution plans for all these queries. I would also examine whether or not all the manipulation of the data in temp tables is necessary. How much of that can be done in one query (note, I'm not advocating for a single query to do this, just some reduction in the quantity) instead of a hundred separate calls. After fixing the bad code smells and reducing the work, I'd break out the execution plans to understand how the optimizer is treating the resulting queries. However, at this point, too much easy work is available to start sweating execution plans.