query performance

I've got a query that is running very slowly. In the execution plan it says that a Hash Merge Table (Full Outer Join) is taking up 60% of the cost. I also ran they query through the tuning advisor and applied the recommended changes but it still runs very slow.

Can anyone point me in the rigth direction as to where to begin to solve this problem?

Thanks,

Matt

If it helps here is the query

select coalesce(d.systemCode, a.systemCode, w.systemCode, m.systemCode) as 'System Name',
convert(nvarchar, coalesce( w.supersheetdate, d.supersheetdate, a.supersheetdate, m.supersheetdate), 101) as 'Supersheet Date',
coalesce(w.ssn, d.ssn, a.ssn, m.ssn) as 'SSN',
coalesce(d.participantname, a.participantname, m.participantname, ' ') as 'Full Name',
coalesce(w.icunumber, d.icunumber, a.icunumber, m.icunumber) as 'ICU Number',
coalesce(w.icuname, d.icuname, a.icuname, m.icuname) as 'ICU Name',
coalesce( w.vehiclenumber, d.vehiclenumber, a.vehiclenumber, m.vehiclenumber) as 'Vehicle Number',
coalesce(w.vehiclename, m.vehiclename, ' ') as 'Vehicle Name',
coalesce(w.planid, d.planid, a.planid, m.planid) as 'Plan ID',
coalesce(w.planname, d.planname, a.planname, m.planname) as 'Plan Name',
convert(nvarchar, coalesce(w.tradedate, d.tradedate, a.tradedate, m.tradedate),101) as 'Trade Date',
coalesce(w.statecode, d.statecode, m.statecode, ' ') as 'State',
isnull(convert(nvarchar, coalesce(w.taxdate, m.taxdate),101), ' ') as 'Tax Date',
isnull(convert(nvarchar, coalesce(d.confirmdate, m.confirmdate),101),' ') as 'Confirm Date',
coalesce(w.externalaccount,d.externalaccount, a.externalaccount, m.externalaccount) as 'External Account',
'$'+convert(nvarchar(64),isnull(sum(w.withholdingamount),0.00),1) as 'Rpt 1Fed WH',
'$'+convert(nvarchar(64),isnull(sum(w.statewhamount),0.00),1) as 'Rpt 1State WH',
'$'+convert(nvarchar(64),isnull(d.withholdingamount,0.00),1) as 'Rpt 2Fed WH',
'$'+convert(nvarchar(64),isnull(d.statewhamount,0.00),1) as 'Rpt 2State WH',
'$'+convert(nvarchar(64),isnull(a.amount,0.00),1) as 'Cancellation Amount',
'$'+convert(nvarchar(64),isnull(m.withholdingamount,0.00),1) as 'Manual Fed WH',
'$'+convert(nvarchar(64),isnull(m.statewhamount,0.00),1) as 'Manual State WH'

from withholding w
full outer join
(select sum(withholdingamount) as 'withholdingamount',
sum(statewhamount) as 'statewhamount', ssn, supersheetdate, systemcode,
icunumber, icuname, vehiclenumber, planid, planname, externalaccount,
tradedate, statecode, participantname, confirmdate, status
from distributions where status is null or status != 'D'
group by ssn, supersheetdate, systemcode, icunumber, icuname, vehiclenumber,
planid, planname, externalaccount, tradedate, statecode, participantname,
confirmdate, status) d
on w.ssn = d.ssn and
w.supersheetdate = d.supersheetdate and
w.systemcode = d.systemcode and
w.icunumber = d.icunumber and
w.icuname = d.icuname and
w.vehiclenumber = d.vehiclenumber and
w.planid = d.planid and
w.planname = d.planname and
w.externalaccount = d.externalaccount and
w.tradedate = d.tradedate and
w.statecode = d.statecode

full outer join
(select sum(amount) as 'amount', ssn, systemcode, supersheetdate, icunumber,
icuname, vehiclenumber, planid, planname, participantname, externalaccount,
tradedate, status
from adjustments
where status is null or status != 'D'
group by ssn, systemcode, supersheetdate, icunumber, icuname, vehiclenumber, planid,
planname, participantname, externalaccount, tradedate, status) a
on a.ssn = isnull(w.ssn, d.ssn) and
a.systemcode = isnull(w.systemcode, d.systemcode) and
a.supersheetdate = isnull(w.supersheetdate, d.supersheetdate) and
a.icunumber = isnull(w.icunumber, d.icunumber) and
a.icuname = isnull(w.icuname, d.icuname) and
a.vehiclenumber = isnull(w.vehiclenumber, d.vehiclenumber) and
a.planid = isnull(w.planid, d.planid) and
a.planname = isnull(w.planname, d.planname) and
a.externalaccount = isnull(w.externalaccount, d.externalaccount) and
a.tradedate = isnull(w.tradedate, d.tradedate)

full outer join
(select sum(withholdingamount) as 'withholdingamount',
sum(statewhamount) as 'statewhamount', systemcode, taxdate, icunumber, icuname,
planid, planname, ssn, tradedate, supersheetdate, statecode, participantname,
vehiclenumber, vehiclename, externalaccount, confirmdate, status
from manualrecords
where status is null or status != 'D'
group by systemcode, taxdate, icunumber, icuname, planid, planname, ssn, tradedate,
supersheetdate, statecode, participantname, vehiclenumber, vehiclename, externalaccount,
confirmdate, status) m
on m.ssn = coalesce(w.ssn, d.ssn, a.ssn) and
m.systemcode = coalesce(w.systemcode, d.systemcode, a.systemcode) and
m.supersheetdate = coalesce(w.supersheetdate, d.supersheetdate, a.supersheetdate) and
m.icunumber = coalesce(w.icunumber, d.icunumber, a.icunumber) and
m.icuname = coalesce(w.icuname, d.icuname, a.icuname) and
m.vehiclenumber = coalesce(w.vehiclenumber, d.vehiclenumber, a.vehiclenumber) and
m.planid = coalesce(w.planid, d.planid, a.planid) and
m.planname = coalesce(w.planname, d.planname, a.planname) and
m.externalaccount = coalesce(w.externalaccount, d.externalaccount, a.externalaccount) and
m.tradedate = coalesce(w.tradedate, d.tradedate, a.tradedate)

where (w.status is null or w.status != 'D') and
coalesce(d.supersheetdate, a.supersheetdate, w.supersheetdate, m.supersheetdate) <= '01/29/2007'
and coalesce(d.icuname,a.icuname, w.icuname, m.icuname) in ('Acme Investment Company') and
coalesce(d.systemCode, a.systemCode, w.systemCode, m.systemCode) ='AX' and
coalesce( d.supersheetdate, a.supersheetdate, w.supersheetdate, m.supersheetdate) >= '01/19/2007'

group by w.systemcode, w.taxdate, w.systemcode, w.icunumber, w.icuname, w.planid, w.icuname,
w.planname, w.ssn, w.tradedate, w.supersheetdate, w.statecode, w.vehiclenumber, w.vehiclename,
w.externalaccount, d.ssn, d.supersheetdate, d.systemcode, d.icunumber, d.icuname,
d.vehiclenumber, d.planid, d.planname, d.externalaccount, d.tradedate, d.statecode,
d.participantname, d.confirmdate, d.withholdingamount, d.statewhamount, a.ssn,a.systemcode,
a.supersheetdate, a.icunumber, a.icuname, a.vehiclenumber, a.planid, a.planname,
a.participantname, a.externalaccount, a.tradedate, a.amount, m.systemcode, m.taxdate,
m.icunumber, m.icuname, m.planid, m.planname, m.ssn, m.tradedate, m.supersheetdate,
m.statecode, m.participantname, m.vehiclenumber, m.vehiclename, m.externalaccount,
m.confirmdate, m.withholdingamount, m.statewhamount

order by coalesce(d.supersheetdate, a.supersheetdate, w.supersheetdate, m.supersheetdate),
coalesce(d.ssn,a.ssn, w.ssn, m.ssn), coalesce(d.planname, a.planname, w.planname, m.planname)


Here are the indexes

CREATE NONCLUSTERED INDEX [_dta_index_adjustments_67_981578535__K9_K2_K3_K4_K5_K10_K6_K7_K8_K11_K13_K14_K12] ON [adjustments]
(
[SSN] ASC,
[SystemCode] ASC,
[SupersheetDate] ASC,
[ICUNumber] ASC,
[ICUName] ASC,
[VehicleNumber] ASC,
[PlanID] ASC,
[PlanName] ASC,
[ParticipantName] ASC,
[ExternalAccount] ASC,
[TradeDate] ASC,
[status] ASC,
[Amount] ASC
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_icuname] ON [adjustments]
(
[ICUName] ASC
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [distributions](
[RecordID] [bigint] IDENTITY(1,1) NOT NULL,
[SystemCode] [nvarchar](2) NOT NULL,
[SupersheetDate] [smalldatetime] NOT NULL,
[ICUNumber] [nvarchar](9) NOT NULL,
[ICUName] [nvarchar](35) NOT NULL,
[PlanID] [nvarchar](9) NOT NULL,
[PlanName] [nvarchar](35) NOT NULL,
[ParticipantName] [nvarchar](35) NOT NULL,
[SSN] [nvarchar](11) NOT NULL,
[VehicleNumber] [nvarchar](10) NOT NULL,
[TradeDate] [smalldatetime] NOT NULL,
[DistributionAmount] [money] NOT NULL,
[ForfeitureAmount] [money] NOT NULL,
[WithholdingAmount] [money] NOT NULL,
[ExternalAccount] [nvarchar](11) NOT NULL,
[ConfirmDate] [smalldatetime] NOT NULL,
[StateCode] [nvarchar](2) NOT NULL,
[StateWHAmount] [money] NOT NULL,
[Status] [char](1) NULL,
[EditId] [int] NOT NULL
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [_dta_index_distributions_67_997578592__K9_K3_K2_K4_K5_K10_K6_K7_K15_K11_K17_K8_K16_K19_K14_K18] ON [distributions]
(
[SSN] ASC,
[SupersheetDate] ASC,
[SystemCode] ASC,
[ICUNumber] ASC,
[ICUName] ASC,
[VehicleNumber] ASC,
[PlanID] ASC,
[PlanName] ASC,
[ExternalAccount] ASC,
[TradeDate] ASC,
[StateCode] ASC,
[ParticipantName] ASC,
[ConfirmDate] ASC,
[Status] ASC,
[WithholdingAmount] ASC,
[StateWHAmount] ASC
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [_dta_index_withholding_c_67_1013578649__K10_K3_K2_K4_K5_K6_K8_K9_K11_K12_K15] ON [withholding]
(
[SSN] ASC,
[SupersheetDate] ASC,
[SystemCode] ASC,
[ICUNumber] ASC,
[ICUName] ASC,
[VehicleNumber] ASC,
[PlanID] ASC,
[PlanName] ASC,
[ExternalAccount] ASC,
[TradeDate] ASC,
[StateCode] ASC
) ON [PRIMARY]
GO

Three full outer joins, on derived tables that are actually aggregates - with little or no filter criteria - and you're surprised it's slow?

Do the rowcounts for the individual queries, then do the maths for the overall rowcount. A full outer join is not quite as bad as a cartesian product, but still are you sure you need a FULL outer join?

When you start adding criteria, don't forget to apply them - wherever possible - also to the derived tables.