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.
-
I would try the index first, an unless either of the two columns are already in the primary key I would make an index with both in it, probably RouteID, ClientNumber as but put whichever is most unique first. Then see how things look before you proceed. However keep in mind the new index may slow inserts slightly so the trigger idea might even be better.
-
I think the Trigger is the way to go *but* if the transaction rate is too high I would vote for the index. In adition you should convert your "union" to "union all" ( every little bit helps
)Cheers,
* Noel -
***********************
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).
***********************
Several issues here:
1) UNIONs are poor performers. Assuming there cannot be duplications between the 4 tables, you should use UNION ALLs.
2) Your indexing to optimize the above query should be on ClientNumber, RouteID, SentTime/ReceiveTime. You can add in CampaignID to get a covering index, which will be fastest.
3) The IN with DISTINCT is also a poor performer. Make sure you have an index on subscriptions that is ClientNumber, CampaignID. Depending on how many Campaigns per client, the distinct should be removed.
4) The trigger solution could be workable, but if your indexes aren't right it will CRUSH your performance.
***********************
PS - We desperately need a DB Admin.
***********************
Actually for these sorts of problems all you need is a quick performance review. Drop me a private message if you are interested. I specialize in issues like you are having.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply