Curious thought on how to approach making a query like this "better'

  • Troubleshooting queries like this are always fun, this definitely isn't one of the worst I've seen, but its a popular one where the report writers liek to copy and paste this code into other procedures as a basis for others.

    The temp and parameter tables used, often have hundreds of thousands of rows in them...I don't supposed there's a way to throw an error across the entire server if a temp table exceeds a certain number of rows in there?

    Anyone else feel the pain or am I just whining?

    SELECT

    b.PrimaryAccountNumber,

    b.trantime,

    b.posttime,

    ISNULL(b.AuthDateTime,b.posttime),

    RTRIM(b.storename),

    ISNULL(CONVERT(decimal(30, 4), c.quantity), 0) AS [Qty],

    0,

    ROUND(CASE WHEN c.originalamount_ = 0.00 THEN c.[TransactionAmount]

    ELSE c.originalamount_

    END / (CASE WHEN CONVERT(decimal(30, 4), c.quantity) = 0 THEN 1

    ELSE CONVERT(decimal(30, 4), c.quantity)

    END), 3),

    CASE WHEN c.originalamount_ = 0.00 THEN c.[TransactionAmount]

    ELSE c.originalamount_

    END,

    0,

    b.ProgramName,

    ISNULL(b.PORefNumber, br.PORefNumber),

    ISNULL(a.OdometerReading, '0'),

    RTRIM(ISNULL(b.storecity, br.storecity)),

    RTRIM(ISNULL(b.storestate, br.storestate)),

    c.unitofcost,

    c.RepricedTranAmount,

    (CASE WHEN c.originalamount_ = 0.00 THEN c.[TransactionAmount]

    ELSE c.originalamount_

    END - c.RepricedTranAmount) AS Discount,

    ROUND((CASE WHEN c.FedExciseTaxExemptStatus = 'Y' THEN c.FedTaxAmount

    ELSE 0

    END) + (CASE WHEN c.StateFuelTaxExemptStatus = 'Y' THEN c.StateTaxAmount

    ELSE 0

    END) + (CASE WHEN c.CountyFuelTaxExemptStatus = 'Y' THEN c.CountyTaxAmount

    ELSE 0

    END) + (CASE WHEN c.CityFuelTaxExemptStatus = 'Y' THEN c.CityTaxAmount

    ELSE 0

    END) + (CASE WHEN c.LocalSalesTaxExemptStatus = 'Y' THEN c.LocalTax

    ELSE 0

    END)

    + (CASE WHEN c.Misc1ExciseTaxExemptStatus = 'Y' THEN c.MiscTaxAmount1

    ELSE 0

    END) + (CASE WHEN c.Misc2ExciseTaxExemptStatus = 'Y' THEN c.MiscTaxAmount2

    ELSE 0

    END) + (CASE WHEN c.StateSalesTaxExemptStatus = 'Y' THEN c.StateSalesTaxAmount

    ELSE 0

    END)

    + (CASE WHEN c.CountySalesTaxExemptStatus = 'Y' THEN c.CountySalesTaxAmount

    ELSE 0

    END) + (CASE WHEN c.CitySalesTaxExemptStatus = 'Y' THEN c.CitySalesTaxAmount

    ELSE 0

    END) + (CASE WHEN c.MiscSalesTaxExemptStatus = 'Y' THEN c.MiscSalesTaxAmount

    ELSE 0

    END) + (CASE WHEN c.FreightExemptStatus = 'Y' THEN c.Freight

    ELSE 0

    END), 2) Exemptions,

    ISNULL(ROUND(RepricedTranAmount - ((CASE WHEN c.FedExciseTaxExemptStatus = 'Y' THEN c.FedTaxAmount

    ELSE 0

    END)

    + (CASE WHEN c.StateFuelTaxExemptStatus = 'Y' THEN c.StateTaxAmount

    ELSE 0

    END)

    + (CASE WHEN c.CountyFuelTaxExemptStatus = 'Y' THEN c.CountyTaxAmount

    ELSE 0

    END)

    + (CASE WHEN c.CityFuelTaxExemptStatus = 'Y' THEN c.CityTaxAmount

    ELSE 0

    END)

    + (CASE WHEN c.LocalSalesTaxExemptStatus = 'Y' THEN c.LocalTax

    ELSE 0

    END)

    + (CASE WHEN c.Misc1ExciseTaxExemptStatus = 'Y' THEN c.MiscTaxAmount1

    ELSE 0

    END)

    + (CASE WHEN c.Misc2ExciseTaxExemptStatus = 'Y' THEN c.MiscTaxAmount2

    ELSE 0

    END)

    + (CASE WHEN c.StateSalesTaxExemptStatus = 'Y'

    THEN c.StateSalesTaxAmount

    ELSE 0

    END)

    + (CASE WHEN c.CountySalesTaxExemptStatus = 'Y'

    THEN c.CountySalesTaxAmount

    ELSE 0

    END)

    + (CASE WHEN c.CitySalesTaxExemptStatus = 'Y'

    THEN c.CitySalesTaxAmount

    ELSE 0

    END)

    + (CASE WHEN c.MiscSalesTaxExemptStatus = 'Y'

    THEN c.MiscSalesTaxAmount

    ELSE 0

    END) + (CASE WHEN c.FreightExemptStatus = 'Y' THEN c.Freight

    ELSE 0

    END)), 2), b.transactionamount) AmountDue,

    0,

    (CASE WHEN c.FedExciseTaxExemptStatus = 'Y' THEN (c.FedTaxAmount * -1)

    ELSE 0

    END) AS fedtax,

    (CASE WHEN c.StateFuelTaxExemptStatus = 'Y' THEN (c.StateTaxAmount * -1)

    ELSE 0

    END) AS StateTax,

    (CASE WHEN c.MiscSalesTaxExemptStatus = 'Y' THEN (c.MiscSalesTaxAmount * -1)

    ELSE 0

    END) AS MiscSalesTax,

    (CASE WHEN c.FreightExemptStatus = 'Y' THEN (c.Freight * -1)

    ELSE 0

    END) AS freight,

    c.[productcode1],

    k.LutDescription,

    '',

    a.ApprovalCode,

    b.[txncode_internal],

    b.[tranid],

    ISNULL(CASE WHEN c.originalamount_ = 0.00 THEN c.[TransactionAmount]

    ELSE c.originalamount_

    END, b.transactionamount),

    b.cmttrantype,

    a.[taxexemptTotalBalance],

    ISNULL(a.FleetCardIdDriverNo, ar.FleetCardIdDriverNo),

    a.[ResponseCode],

    a.EDTransactionFlag,

    a.PermitState,

    a.PermitNumber,

    b.FleetNumber,

    a.TrailerNumber,

    b.DriverLicenseNumber,

    b.TripNumber,

    RTRIM(a.DealerName),

    ISNULL(RTRIM(a.DealerAddressLine1), ''),

    ISNULL(a.txnflag, 'M'),

    CASE WHEN a.EDTXnFlag = '1' THEN 'E'

    WHEN a.tranid IN (SELECT

    tranid

    FROM

    #PaymentTranIDs

    WHERE

    TranID IS NOT NULL) THEN 'P' --526171

    ELSE ISNULL(a.txnflag, 'M')

    END,

    CASE WHEN a.tranid IN (SELECT

    tranid

    FROM

    #PaymentTranIDs

    WHERE

    TranID IS NOT NULL)

    THEN (SELECT DISTINCT

    yy.[LutDescription]

    FROM

    THISDATABASE.dbo.CCardLookUp AS yy WITH (READUNCOMMITTED)

    INNER JOIN THISDATABASE.dbo.Payments AS pp WITH (READUNCOMMITTED)

    ON pp.pmtpaymenttype = yy.lutcode

    AND lutid = 'pmtpaymenttype'

    WHERE

    pp.tranid = a.tranid)

    ELSE b.TransactionDescription

    END TransactionDescription,

    CASE WHEN a.txnflag IN ('0', '5', '3') THEN 'Card Transactions'

    WHEN a.txnflag = '1' THEN 'Tire Programs'

    WHEN a.txnflag = '2' THEN 'Plus Chek Transactions'

    WHEN a.txnflag = '4' THEN 'Permit Transaction'

    WHEN a.EDTxnFlag = '1' THEN 'ED'

    WHEN a.tranid IN (SELECT

    tranid

    FROM

    #PaymentTranIDs

    WHERE

    TranID IS NOT NULL)

    THEN (SELECT DISTINCT

    yy.[LutDescription]

    FROM

    THISDATABASE.dbo.CCardLookUp AS yy WITH (READUNCOMMITTED)

    INNER JOIN THISDATABASE.dbo.Payments AS pp WITH (READUNCOMMITTED)

    ON pp.pmtpaymenttype = yy.lutcode

    AND lutid = 'pmtpaymenttype'

    WHERE

    pp.tranid = a.tranid)

    ELSE 'Miscellaneous Activity'

    END TxnFlagDesc,

    b.RevTgt,

    b.tranref,

    --FIL.invoicenumber,--b.InvoiceNumber,

    CONVERT(BIGINT,LTRIM(RTRIM(b.InvoiceNumber))),

    p.lutdescription,

    c.producttype,

    ISNULL(b.TransactionDescription, d.lutdescription),

    ISNULL(e.Parent01AID, e.acctid),

    CASE WHEN cl.LutDescription LIKE 'Monthly%' THEN 'Monthly'

    WHEN cl.LutDescription IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday',

    'Sunday') THEN 'Weekly'

    WHEN cl.LutDescription LIKE '%Daily%' THEN 'Daily'

    WHEN cl.LutDescription LIKE '%Bi-Weekly%' THEN 'Bi-Weekly'

    WHEN cl.LutDescription LIKE '%Annually%' THEN 'Annually'

    WHEN cl.LutDescription LIKE '%Quarterly%' THEN 'Quarterly'

    WHEN cl.LutDescription LIKE '%Semi-Monthly%' THEN 'Semi-Monthly'

    WHEN cl.LutDescription LIKE '%Semi-Annually%' THEN 'Semi-Annually'

    WHEN cl.LutDescription LIKE '%45 Day Cycle%' THEN '45 Day Cycle'

    ELSE 'Other'

    END BillingCycle,

    u.CheckNumber,

    u.CheckAmount,

    Pt.CreditType,

    CASE WHEN b.cmttrantype LIKE '%[a-z]%' THEN ''

    WHEN ISNUMERIC(b.cmttrantype) = 1

    AND (((CONVERT(int, b.cmttrantype) % 2) = 1

    OR b.cmttrantype IN ('110', '116', '118'))

    AND b.cmttrantype NOT IN ('111', '115', '117')) THEN -1

    WHEN ISNUMERIC(b.cmttrantype) = 1

    AND (((CONVERT(int, b.cmttrantype) % 2) = 0

    OR b.cmttrantype IN ('111', '115', '117'))

    AND b.cmttrantype NOT IN ('110', '116', '118')) THEN 1

    END,

    COALESCE(a.DriverName, f1.DriverFirstName, f.DriverFirstName, f1r.DriverFirstName, fr.DriverFirstName) AS FirstName,

    COALESCE(a.DriverMiddleName, f1.DriverMiddleInitial, f.DriverMiddleInitial, f1r.DriverMiddleInitial, fr.DriverMiddleInitial) AS MiddleInitial,

    COALESCE(a.DriverSurName, f1.DriverSurname, f.DriverSurname, f1r.DriverSurname, fr.DriverSurname) AS Lastname,

    ISNULL(a.FleetCardVehicleNo,ISNULL(un.UnitNumber, '')),

    SUBSTRING(b.PrimaryAccountNumber, 15, 4),

    @acctid,

    e.atid,

    NULL AS invoicenumber, --z.invoicenumber,

    NULL AS invoicedates, --z.invoicedates,

    NULL AS invoicedate,--z.invoicedate,

    NULL AS statementid,--z.statementid,

    FAI.FleetName,

    FAI.FleetAddress,

    FAI.FleetAddress2,

    FAI.FleetContact,

    FAI.FleetFax,

    FII.InstitutionID,

    FII.InstAddress1,

    FII.InstAddress2,

    FII.InstPhone,

    @imagepath,

    ISNULL(n.ContactNameDBA, ISNULL(n1.ContactNameDBA, '')),

    ISNULL(mm.[Gallons_YTD], 0.00),

    ISNULL(mm.MPG_YTD, 0.00),

    ISNULL(mm.[Amount_YTD], 0.00),

    ISNULL(mm.CPM_YTD, 0.00),

    (ISNULL(mm.[Exemptions_YTD], 0.00) * -1),

    ISNULL(mm.MPG_YTD, 0.00),

    (ISNULL(mm.[Disc_YTD], 0.00) * -1),

    ISNULL(mm.CPM_YTD, 0.00),

    ISNULL(mm.Gross_YTD, 0.00),

    0 [mPlusCheck],

    0 [mPermit],

    0 [mTire],

    0 [mMisc],

    0 [mEd],

    0 [mDirect],

    0 [mPayment],

    0 [mSummary],

    0 [mAccountTotals],

    0 [mPromo],

    CAST(0.00 AS money),

    CAST(0.00 AS money),

    CAST(0.00 AS money),

    CAST(0.00 AS money),

    c.BillType,

    CAST('' AS varchar(15)),

    '',

    '',

    0,

    '',

    COALESCE(ml.prevodometer, 0),

    COALESCE(ml.miles, 0),

    COALESCE(mm.[Miles_YTD], 0),

    0.00,

    0.00,

    0.00,

    0.00,

    0.00,

    0.00,

    0.00,

    0.00,

    0.00,

    0.00,

    0.00,

    0.00,

    0.00,

    0.00

    FROM

    THISDATABASE.dbo.ccard_primary AS b WITH (READUNCOMMITTED)

    INNER JOIN THISDATABASE.dbo.ccard_secondary AS a WITH (READUNCOMMITTED)

    ON a.tranid = b.tranid

    INNER JOIN @RD_Fleet_inv_list AS z

    ON z.tranid = b.tranid

    LEFT JOIN THISDATABASE.dbo.CCardLineItems AS c WITH (READUNCOMMITTED)

    ON c.UniqueId = a.TranId

    LEFT JOIN THISDATABASE.dbo.CCARDLOOKUP AS k WITH (READUNCOMMITTED)

    ON k.LutCode = c.ProductCode1

    AND k.LutId = 'ProductCode'

    LEFT JOIN THATDATABASE.dbo.CoreLibLUT AS p WITH (READUNCOMMITTED)

    ON p.LutCode = CONVERT(char(30), b.ProgramName)

    AND p.LutId = 'ProgramMgt'

    LEFT JOIN THISDATABASE.[DBO].TrancodeLookup AS d WITH (READUNCOMMITTED)

    ON d.Lutcode = b.Txncode_Internal

    AND d.lutid = 'TranCode'

    LEFT JOIN THISDATABASE.[DBO].bsegment_primary AS e WITH (READUNCOMMITTED)

    ON e.accountnumber = b.AccountNumber

    AND e.FleetNumber = b.FleetNumber

    LEFT OUTER JOIN THISDATABASE.[DBO].ccardlookup AS cl WITH (READUNCOMMITTED)

    ON cl.LutCode = e.BillingCycle

    AND cl.LUTid = 'BillingCycle'

    LEFT JOIN ANOTHERDATABASE.DBO.usedpluscheks AS u WITH (READUNCOMMITTED)

    ON CONVERT(varchar(24), U.TranID) = b.uniqueid

    LEFT JOIN THISDATABASE.[DBO].Payments AS Pt WITH (READUNCOMMITTED)

    ON CONVERT(varchar(24), Pt.TranId) = b.uniqueid

    LEFT JOIN ANOTHERDATABASE.dbo.DriverNumbers AS f WITH (READUNCOMMITTED)

    ON (f.accountlevel = '0'

    AND a.FleetCardIdDriverNo = f.drivernumber

    AND @AcctID = f.CompanyAcctID)

    AND ((f.CardNumber IS NOT NULL

    AND b.primaryaccountnumber = f.CardNumber)

    OR (f.CardNumber IS NULL))

    AND (b.[posttime] BETWEEN ISNULL(f.activedate, '1900-01-01')

    AND ISNULL(f.inactivedate, '2900-12-31'))

    LEFT JOIN ANOTHERDATABASE.dbo.DriverNumbers AS f1 WITH (READUNCOMMITTED)

    ON (a.FleetCardIdDriverNo = f1.drivernumber

    AND b.primaryaccountnumber = f1.cardnumber

    AND f1.accountlevel = '1'

    AND (b.[posttime] BETWEEN ISNULL(f1.activedate, '1900-01-01')

    AND ISNULL(f1.inactivedate, '2900-12-31')))

    LEFT JOIN ANOTHERDATABASE.dbo.UnitNumbers AS un WITH (READUNCOMMITTED)

    ON b.primaryaccountnumber = un.CardNumber

    AND b.posttime >= un.ActiveDate

    AND b.posttime < ISNULL(un.InactiveDate, GETDATE())

    LEFT JOIN @RD_Fleet_Acct_Info AS FAI

    ON FAI.FleetNumber = b.FleetNumber

    LEFT JOIN @RD_Fleet_Inst_Info AS FII

    ON FII.InstitutionID = FAI.InstitutionID

    LEFT JOIN @RD_Fleet_inv_list AS FIL

    ON FIL.FleetNumber = b.FleetNumber

    LEFT JOIN THISDATABASE.[DBO].nameaddraccounts AS n WITH (READUNCOMMITTED)

    ON CASE WHEN e.parent01aid IS NULL THEN e.AcctID

    ELSE e.parent01aid

    END = n.parent02aid

    AND n.AddressType = '0'

    LEFT JOIN THISDATABASE.[DBO].nameaddraccounts AS n1 WITH (READUNCOMMITTED)

    ON n1.Acctid = N.parent01aid

    AND n1.AddressType = '0'

    LEFT JOIN @Mileage_YTD AS mm

    ON mm.pan = b.primaryaccountnumber

    AND b.trantime = mm.trandate

    LEFT JOIN @Miles AS ml

    ON ml.pan = b.primaryaccountnumber

    AND ml.tranid = CONVERT(varchar(20), b.tranid)

    AND ml.trantime = b.trantime

    LEFT JOIN THISDATABASE.[DBO].SpecificStatementMessages AS st WITH (READUNCOMMITTED)

    ON st.AccountID = FAI.CompanyAcctID

    LEFT JOIN THISDATABASE.dbo.ccard_primary AS br WITH (READUNCOMMITTED)

    ON br.tranid = b.RevTgt

    LEFT JOIN THISDATABASE.dbo.ccard_secondary AS ar WITH (READUNCOMMITTED)

    ON ar.TranId = br.TranId

    LEFT JOIN ANOTHERDATABASE.dbo.DriverNumbers AS fr WITH (READUNCOMMITTED)

    ON (fr.accountlevel = '0'

    AND a.FleetCardIdDriverNo = fr.drivernumber

    AND @AcctID = fr.CompanyAcctID)

    AND ((fr.CardNumber IS NOT NULL

    AND b.primaryaccountnumber = fr.CardNumber)

    OR (fr.CardNumber IS NULL))

    AND (br.[posttime] BETWEEN ISNULL(fr.activedate, '1900-01-01')

    AND ISNULL(fr.inactivedate, '2900-12-31'))

    LEFT JOIN ANOTHERDATABASE.dbo.DriverNumbers AS f1r WITH (READUNCOMMITTED)

    ON (ar.FleetCardIdDriverNo = f1r.drivernumber

    AND br.primaryaccountnumber = f1r.cardnumber

    AND f1r.accountlevel = '1'

    AND (br.[posttime] BETWEEN ISNULL(f1r.activedate, '1900-01-01')

    AND ISNULL(f1r.inactivedate, '2900-12-31')))

    WHERE

    b.cmttrantype NOT IN ('Nch', 'QNA', '*SCR', '110', '113', '121', '122', '88', '89', '127','114','117','26','24')

    AND (NOT(LTRIM(RTRIM(ISNULL(b.Transactionidentifier,'0'))) = '1' and b.cmttrantype in ('49','43')))

    AND ISNULL(ROUND(RepricedTranAmount - ((CASE WHEN c.FedExciseTaxExemptStatus = 'Y' THEN c.FedTaxAmount

    ELSE 0

    END)

    + (CASE WHEN c.StateFuelTaxExemptStatus = 'Y'

    THEN c.StateTaxAmount

    ELSE 0

    END)

    + (CASE WHEN c.CountyFuelTaxExemptStatus = 'Y'

    THEN c.CountyTaxAmount

    ELSE 0

    END)

    + (CASE WHEN c.CityFuelTaxExemptStatus = 'Y' THEN c.CityTaxAmount

    ELSE 0

    END)

    + (CASE WHEN c.LocalSalesTaxExemptStatus = 'Y' THEN c.LocalTax

    ELSE 0

    END)

    + (CASE WHEN c.Misc1ExciseTaxExemptStatus = 'Y'

    THEN c.MiscTaxAmount1

    ELSE 0

    END)

    + (CASE WHEN c.Misc2ExciseTaxExemptStatus = 'Y'

    THEN c.MiscTaxAmount2

    ELSE 0

    END)

    + (CASE WHEN c.StateSalesTaxExemptStatus = 'Y'

    THEN c.StateSalesTaxAmount

    ELSE 0

    END)

    + (CASE WHEN c.CountySalesTaxExemptStatus = 'Y'

    THEN c.CountySalesTaxAmount

    ELSE 0

    END)

    + (CASE WHEN c.CitySalesTaxExemptStatus = 'Y'

    THEN c.CitySalesTaxAmount

    ELSE 0

    END)

    + (CASE WHEN c.MiscSalesTaxExemptStatus = 'Y'

    THEN c.MiscSalesTaxAmount

    ELSE 0

    END) + (CASE WHEN c.FreightExemptStatus = 'Y' THEN c.Freight

    ELSE 0

    END)), 2), b.transactionamount) <> 0.00

    GROUP BY

    c.tranid,

    b.PrimaryAccountNumber,

    b.trantime,

    b.posttime,

    ISNULL(b.AuthDateTime,b.posttime),

    a.taxexemptTotalBalance,

    c.Transactionamount,

    a.originalamount_,

    c.RepricedTranAmount,

    c.StateTaxAmount,

    c.FedTaxAmount,

    c.Freight,

    c.originalAmount_,

    b.cmttrantype,

    a.tranid,

    b.uniqueid,

    a.OdometerReading,

    b.storename,

    c.quantity,

    b.ProgramName,

    b.PORefNumber,

    b.storecity,

    b.storestate,

    c.unitofcost,

    c.FedExciseTaxExemptStatus,

    c.StateFuelTaxExemptStatus,

    c.CountyFuelTaxExemptStatus,

    c.CityFuelTaxExemptStatus,

    c.LocalSalesTaxExemptStatus,

    c.Misc1ExciseTaxExemptStatus,

    c.Misc2ExciseTaxExemptStatus,

    c.StateSalesTaxExemptStatus,

    c.CountySalesTaxExemptStatus,

    c.CitySalesTaxExemptStatus,

    c.MiscSalesTaxExemptStatus,

    c.FreightExemptStatus,

    c.CountyTaxAmount,

    c.CityTaxAmount,

    c.LocalTax,

    c.MiscTaxAmount1,

    c.MiscTaxAmount2,

    c.StateSalesTaxAmount,

    c.CountySalesTaxAmount,

    c.CitySalesTaxAmount,

    c.MiscSalesTaxAmount,

    c.Freight,

    c.[productcode1],

    a.[ApprovalCode],

    b.[txncode_internal],

    b.[tranid],

    b.cmttrantype,

    a.FleetCardIdDriverNo,

    a.[ResponseCode],

    a.EDTransactionFlag,

    a.PermitState,

    a.PermitNumber,

    b.FleetNumber,

    a.TrailerNumber,

    b.DriverLicenseNumber,

    b.TripNumber,

    a.DealerName,

    a.DealerAddressLine1,

    k.LutDescription,

    a.EDTXnFlag,

    a.[TxnFlag],

    b.TransactionDescription,

    b.RevTgt,

    b.tranref,

    --FIL.invoicenumber,--b.InvoiceNumber,

    CONVERT(BIGINT,LTRIM(RTRIM(b.InvoiceNumber))),

    p.lutdescription,

    c.[ProductType],

    d.lutdescription,

    e.Parent01AID,

    e.acctid,

    cl.LutDescription,

    u.CheckNumber,

    u.CheckAmount,

    Pt.CreditType,

    f1.DriverFirstName,

    f.DriverFirstName,

    f1.DriverMiddleInitial,

    f.DriverMiddleInitial,

    f1.DriverSurname,

    f.DriverSurname,

    a.FleetCardVehicleNo,

    un.unitnumber,

    e.atid,

    --z.invoicenumber,

    --z.invoicedates,

    --z.invoicedate,

    --z.statementid,

    FAI.FleetName,

    FAI.FleetAddress,

    FAI.FleetAddress2,

    FAI.FleetContact,

    FAI.FleetFax,

    FII.InstitutionID,

    FII.InstAddress1,

    FII.InstAddress2,

    FII.InstPhone,

    n.ContactNameDBA,

    n1.ContactNameDBA,

    mm.mpg_YTD,

    mm.cpm_YTD,

    c.BillType,

    b.transactionamount,

    ml.prevodometer,

    ml.miles,

    mm.[Gallons_YTD],

    mm.[Amount_YTD],

    mm.[Exemptions_YTD],

    mm.[Disc_YTD],

    mm.[Gross_YTD],

    mm.[Miles_YTD],

    br.storecity,

    br.storestate,

    a.DriverName,

    a.DriverMiddleName,

    a.DriverSurName,

    f1r.DriverFirstName,

    fr.DriverFirstName,

    f1r.DriverMiddleInitial,

    fr.DriverMiddleInitial,

    f1r.DriverSurname,

    fr.DriverSurname,

    br.PORefNumber,

    ar.FleetCardIdDriverNo

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Well it's nice to see the query is optimized.

    Do you get to maintain this, or are you trying to debug something that happened inside of it?

    Who did you pi** off to get this assignment? 🙂

    _____________________________

    Past performance != future results.
    All opinions and suggestions are my own, unless they're really good. Then I most likely read them here...

  • Youch.

    First, I'd toss the query hints. Then, get rid of all the functions on columns in the JOIN criteria and WHERE clause. After that... who knows. I'm not even addressing temp tables or table variables yet, but those will have to get dealt with too. Sheesh. Stuff like that makes you nuts.

    In general, when I'm forced to rebuild an insane query like that, I take it down to one table and then rewrite everything from scratch. The hard part is not simply tuning the query, but ensuring that the data being returned is the same (although, as written, I wouldn't trust a row coming out of the query).

    Good luck.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I hear what you guys are saying. It's mostly trying to talk the reporting guys out of attempting to handling EVERYTHING within the SQL stored-procedure and encourage them to learn more about SSRS so they can take advantage of the power it offers to do the majority of their aggregation(s), presentation, and such.

    Regarding the removal of the query hints - I assume you refer to the locking hints? I normally would agree (as in my previous experience I've never ever used them, however at this company most of the locking is controlled via the vendor application, and queries like this run against a transaction replicated database - when they're not included at the reporting level, they get massive deadlocks during the daily reporting runs.

    I've entertained using different isolation levels but am not overly familiar with the overhead that may be associated with them (it's way down on the to-do list).

    In general, when I'm forced to rebuild an insane query like that, I take it down to one table and then rewrite everything from scratch. The hard part is not simply tuning the query, but ensuring that the data being returned is the same (although, as written, I wouldn't trust a row coming out of the query).

    This is the crux of my headache - it's insanely difficult for me to match the output!

    Thanks for the comments, I needed to vent...

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • So do you know that read uncommitted is the same thing as NOLOCK??? The locking does NOT all happen in the vendors application. Are the users ok with the idea that the reports have missing and/or duplicate data? In short, these reports are NOT accurate and should not be used for making any kind of business decisions. At best, the values are approximate.

    Here are a few articles discussing this hint and providing examples of how to recreate it.

    http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

    http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Of course I know NOLOCK/READUNCOMMITTED is the same thing 🙂 I wasn't meaning to say that ALL locking happens in the vendors app, I was meaning to say that they attempt to control it within their app - I realize SQL Server does a really good job at handling/escalating locks all by itself, and should be left to do so.

    Yes, users are okay with the limitations of the reporting model - I fought for the removal of the locking hints, I lost.

    Thanks for the link Sean, I'll keep them handy the next time someone complains about their reports being wrong and I can use it as additional ammo for the "I told you so" conversation.

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • You are welcome for the links. I keep them handy. 😉

    I don't envy you in trying to clean this one up. It is a long and painful road for sure. I have had the "pleasure" of cleaning up many of these types of queries in the past and it generally requires a lot of time and effort to unravel everything and get it right. Even harder when the logic from the original is suspect.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Row selection is controlled by "only" 4 tables:

    FROM

    THISDATABASE.dbo.ccard_primary AS b WITH (READUNCOMMITTED)

    INNER JOIN THISDATABASE.dbo.ccard_secondary AS a WITH (READUNCOMMITTED)

    ON a.tranid = b.tranid

    INNER JOIN @RD_Fleet_inv_list AS z

    ON z.tranid = b.tranid

    LEFT JOIN THISDATABASE.dbo.CCardLineItems AS c WITH (READUNCOMMITTED)

    ON c.UniqueId = a.TranId

    So that's actually not as bad as it could be. The other JOINs can temporarily be dropped for all preliminary testing. All those LEFT JOINs will kill your performance, so it'll be a huge advantage to test w/o them.

    The insanely bloated "GROUP BY" is another killer: it must be eliminated. I don't think I saw any aggregating function in the query, so it's just a result of "lazy" joins producing duplicate rows. Until you can work out the other issues, DISTINCT will do the same thing but likely with a lot less overhead.

    The lists in the WHERE clause should likely be turned into indexed tables, tested using NOT EXISTS.

    You should take it logically, one step at a time, and you can get through it. [Btw, unless this was a deliberate attempt to cause an issue, be-atching about it just slows you down, and may upset others; be-atch after it's cleaned up, if you feel the need to.]

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • [Btw, unless this was a deliberate attempt to cause an issue, be-atching about it just slows you down, and may upset others; be-atch after it's cleaned up, if you feel the need to.]

    Not at all, it was merely posted to see what everyone's approach would be in going about troubleshooting such a procedure.

    Thanks!

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply