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,MattIf it helps here is the queryselect 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) anda.systemcode = isnull(w.systemcode, d.systemcode) anda.supersheetdate = isnull(w.supersheetdate, d.supersheetdate) anda.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 LarssonHelsingborg, Sweden |
|
|
matty1stop
Starting Member
37 Posts |
Posted - 2007-01-30 : 15:01:36
|
Should/can I use coalesce in the group by? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-30 : 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.tradedatefull 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.tradedatewhere 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 LarssonHelsingborg, Sweden |
|
|
matty1stop
Starting Member
37 Posts |
Posted - 2007-01-30 : 15:49:55
|
Here are the indexesIF 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]GOIF 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]GOIF 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 queryMsg 8120, Level 16, State 1, Line 1Column '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 1Column '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 1Column '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 1Column '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 1Column '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 1Column 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 1Column 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 1Column 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 1Column 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 1Column 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 1Column 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 1Column 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 1Column 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 1Column 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 1Column 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 1Column 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 1Column 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 1Column 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 1Column 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 1Column 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 1Column 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 1Column 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 1Column 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 1Column 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 1Column 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 1Column 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. |
|
|
|
|
|