| Author |
 Topic  |
|
|
matty1stop
Starting Member
37 Posts |
 Posted - 01/30/2007 : 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
Sweden
29138 Posts |
Posted - 01/30/2007 : 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 |
 |
|
|
matty1stop
Starting Member
37 Posts |
Posted - 01/30/2007 : 15:01:36
|
| Should/can I use coalesce in the group by? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/30/2007 : 15:19:41
|
Try thisselect 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 |
|
|
|
matty1stop
Starting Member
37 Posts |
Posted - 01/30/2007 : 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.
|
|
|
| |
Topic |
|
|
|
Query Performance
