Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4909 Visits: 7365
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; They'll drag you down to their level and beat you with experience" ;-)
ChrisCarsonSQL
ChrisCarsonSQL
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 460
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...
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17629 Visits: 32268
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4909 Visits: 7365
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; They'll drag you down to their level and beat you with experience" ;-)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16632 Visits: 17024
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/

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4909 Visits: 7365
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; They'll drag you down to their level and beat you with experience" ;-)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16632 Visits: 17024
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3950 Visits: 6686
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4909 Visits: 7365
[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; They'll drag you down to their level and beat you with experience" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search