Major help need with combining and pulling data from four different transaction tables.

I have four different transaction tables. Each one has a ClientNumber(varchar), CampaignID, SentTime, and RouteID, along with some other columns I don't need for this query. These tables consist of about 3.2 million transactions total, and are growing by 450k per month.


The Problem:
I need to find the CampaignID of a given client on a given RouteID.

So here is what I'm trying to do that is really slow right now.

select top 1 CampaignID from
(
select top 1 CampaignID,SentTime DT from mttransactions where ClientNumber = @CN and RouteID = @RID order by SentTime DESC
union
select top 1 CampaignID,ReceiveTime DT from motransactions where ClientNumber = @CN and RouteID = @RID order by ReceiveTime DESC
union
select top 1 CampaignID,SentTime DT from matransactions where ClientNumber = @CN and RouteID = @RID order by SentTime DESC
union
select top 1 CampaignID,SentTime DT from rctransactions where ClientNumber = @CN and RouteID = @RID order by SentTime DESC
) T order by DT DESC


I also occasionally need to filter these down a bit further to check if the ClientNumber is in a subscription table. In this case I add on a subquery to my where clause of the 4 unioned queries, ex. "and CampaignID IN (select distinct CampaignID from subscriptions where ClientNumber = @CN).


So my questions are.

A.) How can I speed these up?

B.) These are probably my most important queries. Where exactly should I put indexes? Currently I have indexes on CampaignID and SentTime because we do reports and wanted to speed them up. However, I'm willing to change the indexes and sacrifice report speed for better user experience.

C.) Would it be beneficial to put triggers on these transaction tables to update some sort of user table with the most recent CampaignID?



Thanks in advance for the help,
-Jayson

PS - We desperately need a DB Admin.

Few things that need to be done
(1) Add indexes on Clientnumner, RouteId. If you are using 2005 use the INCLUDE option and put in DT too.
(2) TOP + ORDER BY can be a killer if you have millions of rows and can also cause tempdb contention.
(3) The IN is another killer. Use EXISTS instead. Here's a sample
SELECT..
FROM SomeTable T
WHERE...
AND EXISTS (SELECT * from subscriptions where ClientNumber = @CN And CampaignID = T.CampainId
(4) If you have done all of above and your performance increases you can definetely kill it with Triggers. Triggers come with a lot of baddage and you have to use them wisely. For me, Triggers is a no-no.