Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Curious thought on how to approach making a query like this "better' Expand / Collapse
Author
Message
Posted Wednesday, August 28, 2013 10:01 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 3,998, Visits: 7,172
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"
Post #1489512
Posted Wednesday, August 28, 2013 11:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 1:25 PM
Points: 129, Visits: 301
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...
Post #1489519
Posted Thursday, August 29, 2013 4:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:01 AM
Points: 14,029, Visits: 28,404
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1489635
Posted Thursday, August 29, 2013 7:26 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 3,998, Visits: 7,172
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"
Post #1489714
Posted Thursday, August 29, 2013 7:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
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)
Post #1489723
Posted Thursday, August 29, 2013 8:09 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 3,998, Visits: 7,172
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"
Post #1489738
Posted Thursday, August 29, 2013 8:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
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)
Post #1489742
Posted Thursday, August 29, 2013 9:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:17 PM
Points: 2,268, Visits: 3,428
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1489781
Posted Thursday, August 29, 2013 10:06 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 3,998, Visits: 7,172
[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"
Post #1489788
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse