Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Query Performance

Author  Topic 

matty1stop
Starting Member

37 Posts

Posted - 2007-01-30 : 14:35:13
I've got a query that is running very slowly. In the execution plan it says that a Hash Match (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)


SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 14:48:37
Do you really need all these columns in the GROUP BY?
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
You have only 15 non-aggregated columns in the SELECT part.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

matty1stop
Starting Member

37 Posts

Posted - 2007-01-30 : 15:01:36
Should/can I use coalesce in the group by?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 15:19:41
Try this
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 as w
full 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
) as 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 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
) as a on a.ssn = d.ssn
and a.systemcode = d.systemcode
and a.supersheetdate = d.supersheetdate
and a.icunumber = d.icunumber
and a.icuname = d.icuname
and a.vehiclenumber = d.vehiclenumber
and a.planid = d.planid
and a.planname = d.planname
and a.externalaccount = d.externalaccount
and a.tradedate = d.tradedate
full 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
) as m on m.ssn = d.ssn
and m.systemcode = d.systemcode
and m.supersheetdate = d.supersheetdate
and m.icunumber = d.icunumber
and m.icuname = d.icuname
and m.vehiclenumber = d.vehiclenumber
and m.planid = d.planid
and m.planname = d.planname
and m.externalaccount = d.externalaccount
and m.tradedate = d.tradedate
where coalesce(d.supersheetdate, a.supersheetdate, w.supersheetdate, m.supersheetdate) <= '01/29/2007'
and coalesce(d.icuname, a.icuname, w.icuname, m.icuname) = '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 coalesce(d.systemCode, a.systemCode, w.systemCode, m.systemCode),
convert(nvarchar, coalesce(w.supersheetdate, d.supersheetdate, a.supersheetdate, m.supersheetdate), 101),
coalesce(w.ssn, d.ssn, a.ssn, m.ssn),
coalesce(d.participantname, a.participantname, m.participantname, ' '),
coalesce(w.icunumber, d.icunumber, a.icunumber, m.icunumber),
coalesce(w.icuname, d.icuname, a.icuname, m.icuname),
coalesce( w.vehiclenumber, d.vehiclenumber, a.vehiclenumber, m.vehiclenumber),
coalesce(w.vehiclename, m.vehiclename, ' '),
coalesce(w.planid, d.planid, a.planid, m.planid),
coalesce(w.planname, d.planname, a.planname, m.planname),
convert(nvarchar, coalesce(w.tradedate, d.tradedate, a.tradedate, m.tradedate), 101),
coalesce(w.statecode, d.statecode, m.statecode, ' '),
isnull(convert(nvarchar, coalesce(w.taxdate, m.taxdate), 101), ' '),
isnull(convert(nvarchar, coalesce(d.confirmdate, m.confirmdate),101), ' '),
coalesce(w.externalaccount,d.externalaccount, a.externalaccount, m.externalaccount),
isnull(d.withholdingamount, 0.00),
isnull(d.statewhamount, 0.00),
isnull(a.amount, 0.00),
isnull(m.withholdingamount, 0.00),
isnull(m.statewhamount, 0.00)
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)
But the query will still run slow because of all the bindings in the JOINs...

Which index do you have on the tables?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

matty1stop
Starting Member

37 Posts

Posted - 2007-01-30 : 15:49:55
Here are the indexes

IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[Adjustments]') AND name = N'_dta_index_Adjustments_67_981578535__K9_K2_K3_K4_K5_K10_K6_K7_K8_K11_K13_K14_K12')
CREATE NONCLUSTERED INDEX [_dta_index_Adjustments_67_981578535__K9_K2_K3_K4_K5_K10_K6_K7_K8_K11_K13_K14_K12] ON [dbo].[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

IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[Adjustments]') AND name = N'IX_icuname')
CREATE NONCLUSTERED INDEX [IX_icuname] ON [dbo].[Adjustments]
(
[ICUName] ASC
) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[Distributions]') AND name = N'_dta_index_Distributions_67_997578592__K9_K3_K2_K4_K5_K10_K6_K7_K15_K11_K17_K8_K16_K19_K14_K18')
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 [dbo].[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]


I got the following message when I ran the posted query

Msg 8120, Level 16, State 1, Line 1
Column 'd.withholdingamount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'd.statewhamount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'a.amount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'm.withholdingamount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'm.statewhamount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8127, Level 16, State 1, Line 1
Column name 'd.supersheetdate' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8127, Level 16, State 1, Line 1
Column name 'd.supersheetdate' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8127, Level 16, State 1, Line 1
Column name 'a.supersheetdate' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8127, Level 16, State 1, Line 1
Column name 'a.supersheetdate' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8127, Level 16, State 1, Line 1
Column name 'w.SupersheetDate' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8127, Level 16, State 1, Line 1
Column name 'w.SupersheetDate' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8127, Level 16, State 1, Line 1
Column name 'm.supersheetdate' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8127, Level 16, State 1, Line 1
Column name 'd.ssn' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8127, Level 16, State 1, Line 1
Column name 'd.ssn' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8127, Level 16, State 1, Line 1
Column name 'a.ssn' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8127, Level 16, State 1, Line 1
Column name 'a.ssn' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8127, Level 16, State 1, Line 1
Column name 'w.SSN' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8127, Level 16, State 1, Line 1
Column name 'w.SSN' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8127, Level 16, State 1, Line 1
Column name 'm.ssn' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8127, Level 16, State 1, Line 1
Column name 'd.planname' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8127, Level 16, State 1, Line 1
Column name 'd.planname' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8127, Level 16, State 1, Line 1
Column name 'a.planname' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8127, Level 16, State 1, Line 1
Column name 'a.planname' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8127, Level 16, State 1, Line 1
Column name 'w.PlanName' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8127, Level 16, State 1, Line 1
Column name 'w.PlanName' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8127, Level 16, State 1, Line 1
Column name 'm.planname' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Go to Top of Page
   

- Advertisement -