SQL Server Performance

query performance

Discussion in 'Performance Tuning for DBAs' started by matty1stop, Feb 1, 2007.

  1. matty1stop New Member

    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


  2. Adriaan New Member

    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.

Share This Page