August 28, 2013 at 10:01 pm
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
August 28, 2013 at 11:06 pm
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...
August 29, 2013 at 4:40 am
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
August 29, 2013 at 7:26 am
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
August 29, 2013 at 7:42 am
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/
August 29, 2013 at 8:09 am
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
August 29, 2013 at 8:13 am
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/
August 29, 2013 at 9:41 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 29, 2013 at 10:06 am
[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