sql-server - 另一种在不使用 or 运算符的情况下编写此代码的方法以提高性能
问题描述
这个存储过程是我正在处理的报告的一部分。所以它运行非常缓慢，准确地说是 48 分钟。通过在下面看到的连接中取出 or 运算符，我让它在 6 秒内运行。现在它返回的不是重复项，而是两个表中的数据，这为我的结果集添加了更多行。问题是两个连接是 FinancialTransactions ft ,FinancialTransactions ftRec
我曾尝试使用 CTE。我已将连接拆分并合并所有它们。我无法向查询添加索引或创建任何视图。我还加入了不同的表以及返回更多行的不同视图然后需要。
Before Fix
SELECT DISTINCT
1 as SourceId,
l.LotID as 'LotId',
r.Id as 'RecoveryId',
acc.AccountName as 'Account',
r.BillingCode as BillingCode,
r.ProjectCode as ProjectCode,
a.AssetID as 'SSE Number',
' ' as 'ParentSSENumber',
COALESCE(ft.InvoiceNumber,ftrec.InvoiceNumber) as InvoiceNumber,
COALESCE(ft.InvoicedOn,ftrec.InvoicedOn) as InvoiceDate,
a.Class AS Category,
a.Manufacturer,
a.Product AS Model,
a.ProductNumber AS ModelNum,
a.SerialNumber AS SerialNum,
a.CustomerAssetNumber AS CustAssetTag,
a.Weight,
@WeightMeasure AS WeightMeasurement,
CASE a.ClassID --a.LockedStatus
WHEN 919
THEN CASE hdd.Disposition
WHEN 'Inventory'
THEN
CASE
WHEN -ft.Rate > 0
THEN 'Resale'
ELSE 'Active' END
ELSE hdd.Disposition END
ELSE
CASE --ISNULL(A.LockedStatus, - 1)
WHEN A.LockedStatus = -1 THEN 'InProduction'
WHEN A.LockedStatus = 1 THEN 'Active'
WHEN A.LockedStatus = 2 THEN 'On Hold'
WHEN A.LockedStatus = 3 AND iph_gen.PurchaseType = 7 THEN 'Redeployment'
WHEN A.LockedStatus = 3 AND iph_gen.PurchaseType = 12 THEN 'Donation'
WHEN A.LockedStatus = 3 AND iph_gen.PurchaseType = 11 THEN 'Legal Hold Release'
WHEN A.LockedStatus = 3 AND iph_gen.PurchaseType NOT IN (7,11,12) THEN 'Sold'
WHEN A.LockedStatus = 4 THEN 'Upgrade'
WHEN A.LockedStatus = 5 THEN 'Recycled'
WHEN A.LockedStatus = 6 THEN 'Sold Off-site'
ELSE ''
END
END
AS Disposition,
CASE WHEN a.LockedStatus = 5
THEN ftrec.CreatedOn
ELSE iph_gen.shipDate END AS DispositionDate,
'' AS Comments, --REPLACE(r.AdditionalNotes , CHAR(10), '') as Comments,
isnull(GTu.Name, 'N/A') AS FinalGrade,
' ', ' ',
ISNULL(CASE ftRec.RateTypeID WHEN 1 THEN a.Weight ELSE 1 END * ftRec.Rate,0) AS RecyclingFees,
-- ISNULL(ft.RemarketingPercentage,0),
isnull(ft.Rate,0) AS RemarketingCredit,
--isnull(gt.Name, 'N/A') AS RegisteredGrade,
--' ',
0.00 as LeaseReturnFee,
CASE WHEN iph_gen.PurchaseType = 7 THEN ISNULL(ipds_gen.Price,0)
ELSE 0.00
END AS RedploymentFee,
CASE WHEN ccsg.GroupName LIKE '%Donation%' THEN
CASE WHEN iph_gen.PurchaseType = 12 THEN ISNULL(ipds_gen.Price,0)
ELSE 0.00
END ELSE 0.00
END AS DonationFee
FROM vAssetSimpleView a
INNER JOIN itTransactionProcess AS tp ON tp.TransactionID = a.OrigTransactionID
INNER JOIN itLots AS l ON l.LotID = tp.LotNumber
LEFT JOIN [10.10.1.188,13151].Adviser.dbo.Recovery r on l.LotID = r.MRMLotId
--LEFT JOIN [54.84.218.110,13151].Adviser.dbo.Account acc on r.AccountId = acc.Id
LEFT OUTER JOIN AccountLocationOrderNo AS alon ON alon.LotID = l.LotID
LEFT JOIN AccountLocation AS al ON al.LocationID = l.LocationID
LEFT JOIN Account acc ON al.accountId = acc.AccountID
LEFT JOIN itDocks AS d ON d.DockID = l.DockScheduledOn
LEFT OUTER JOIN Contact AS c ON al.Contact_Onsite = c.ContactID
LEFT OUTER JOIN itGradeTypes AS g ON g.RecID = a.Grade
--JOINS FOR ORDERS
LEFT JOIN itPurchaseDetails ipd_gen on ipd_gen.assetid = a.assetid
LEFT JOIN itPurchaseDetailService ipds_gen on ipd_gen.RecID = ipds_gen.PurchaseDetailRecId
LEFT JOIN ContractCustomServiceGroups ccsg on ccsg.recid = ipds_gen.ServiceGroupId
--AND ipds_donation.ServiceGroupId IN (SELECT RecID FROM [ContractCustomServiceGroups] WHERE GroupName Like '%Donation%')
LEFT JOIN itPurchaseHeader iph_gen on ipd_gen.PurchaseHeaderRecID = iph_gen.RecID
--AND iph_donation.PurchaseType = 12
--END JOINS FOR ORDERS
LEFT OUTER JOIN dbo.Reporting_Inventory_Harddrives hdd ON hdd.AssetID = a.AssetID
--LEFT OUTER JOIN vAssetServicesByType vast ON a.AssetID = vast.AssetRecId
--OUTER APPLY
--(
-- SELECT ph.RecID AS OrderID, v.VendorName, ph.OrderDate,ph.shipdate, cup.FirstName + ' ' + cup.LastName AS SalesRep, pd.LineTotal AS ResalePrice
-- FROM dbo.itPurchaseDetails pd
-- INNER JOIN dbo.itPurchaseHeader ph ON ph.RecID = pd.PurchaseHeaderRecID
-- INNER JOIN dbo.itVendorLocation vl ON vl.LocationID = ph.VendorLocationID
-- INNER JOIN dbo.itVendor v ON v.VendorID = vl.VendorID
-- INNER JOIN dbo.aspnet_custom_UserProfiles cup ON cup.UserID = ph.CreatedBy
-- WHERE ph.Status > 1
-- AND pd.AssetID = a.AssetID
--) AS do
LEFT OUTER JOIN FinancialTransactions ft ON ((ft.FinancialTypeID = 9 AND ft.AssetID = a.AssetID) OR (ft.FinancialTypeID = 8 AND ft.FKeyID = a.OrigTransactionID AND ft.AssetID = 0))
LEFT OUTER JOIN FinancialTransactions ftRec ON ((ftRec.AssetID = a.AssetID) OR (ftRec.FKeyID = a.OrigTransactionID and ftRec.AssetID = 0)) AND ftRec.FinancialTypeID = 1
--OUTER APPLY
--(
-- SELECT ISNULL(SUM(csa.ServicePriceTotal),0) AS total
-- FROM ContractServicesApplied csa
-- WHERE csa.AssetID = a.AssetID
-- AND csa.Completed = 1
--) AS csa
--CROSS APPLY(SELECT ISNULL(SUM(T.PartValue),0) AS UpgradeValue
--FROM dbo.itAssetComponentTransactions AS T
--INNER JOIN dbo.itAssetComponents AS AC
--ON AC.RecID = T.AssetComponentID WHERE
--(T.AssetID = A.AssetId)
--AND (T.TransactionTypeID = 1) AND (AC.DateRemoved IS NULL)) as uv
--LEFT JOIN [dbo].[Reporting_Inventory_Harddrives] rih ON a.AssetId = rih.AssetId
--Tony Added For Recycling Fee Column
LEFT JOIN (SELECT tp.AssetId, -1*ft.Total AS RecyclingTotal
FROM itTransactionProcess tp
INNER JOIN itProcessSteps ps ON tp.NextProcessStepID = ps.ProcessStepID
LEFT OUTER JOIN FinancialTransactions ft ON ft.FKeyID = tp.TransactionID
INNER JOIN itLots il on tp.LotNumber = il.LotID
INNER JOIN AccountLocation al on al.LocationID = il.LocationID
INNER JOIN Account acc on al.AccountID = acc.accountId
WHERE acc.AccountID = @AccountId
AND ps.IsRecyclingProcess = 1
AND ps.IsEndProcess = 1 ) As Recycling ON a.AssetID = Recycling.AssetID
--End Recycling Join
--Tony Added to include Parts section in Asset Detail body of reports
LEFT JOIN itProcessSteps ps ON tp.NextProcessStepID = ps.ProcessStepID
--End Parts Join
--Tony Added for Grade/Condition data
LEFT OUTER JOIN dbo.itGradeTypes GT ON GT.RecID = a.Grade
INNER JOIN itAsset ia ON a.AssetID = ia.AssetRecId
LEFT OUTER JOIN dbo.itGradeTypes GTu ON GTu.RecID = ia.Upgrade_Grade --Join to asset because vAssetSimpleView does not have upgrade_grade
--End Grade Join
OUTER APPLY
(
SELECT TOP 1 CASE WHEN ps.IsRecyclingProcess = 1 THEN 'Recycled' WHEN ps.ProcessStepID = 25 THEN 'Resale' ELSE ps.Name END AS Disposition
FROM itTransactionProcess tp
INNER JOIN itProcessSteps ps on ps.ProcessStepID = tp.CurrentProcessStepID
WHERE tp.AssetID = a.AssetID
AND ps.IsEndProcess = 1
--AND a.AssetTypeID = 1
) AS assetDisp
WHERE acc.accountid = @AccountId
AND ((iph_gen.shipDate >= @StartDate AND iph_gen.ShipDate <= @EndDate) OR (a.LockedStatus = 5 AND ftrec.CreatedOn >= @StartDate AND ftrec.CreatedOn <= @EndDate))
AND ((a.AssetTypeID =1) OR (a.AssetTypeID = 2 and a.ClassID = 919) OR (a.AssetTypeID = 3 and a.ClassID = 919))
--make sure hard drives are part of the asset report
LEFT OUTER JOIN FinancialTransactions ftRec ON ((ftRec.AssetID = a.AssetID) OR (ftRec.FKeyID = a.OrigTransactionID and ftRec.AssetID = 0)) AND ftRec.FinancialTypeID = 1
--OUTER APPLY
After FIX
SELECT DISTINCT
1 as SourceId,
l.LotID as 'LotId',
r.Id as 'RecoveryId',
acc.AccountName as 'Account',
r.BillingCode as BillingCode,
r.ProjectCode as ProjectCode,
a.AssetID as 'SSE Number',
' ' as 'ParentSSENumber',
COALESCE(ft.InvoiceNumber,Ftrec.InvoiceNumber) as InvoiceNumber,
COALESCE(ft.InvoicedOn,Ftrec.Invoicedon) as InvoiceDate,
a.Class AS Category,
a.Manufacturer,
a.Product AS Model,
a.ProductNumber AS ModelNum,
a.SerialNumber AS SerialNum,
a.CustomerAssetNumber AS CustAssetTag,
a.Weight,
@WeightMeasure AS WeightMeasurement,
CASE a.ClassID --a.LockedStatus
WHEN 919
THEN CASE hdd.Disposition
WHEN 'Inventory'
THEN
CASE
WHEN -ft.Rate > 0
THEN 'Resale'
ELSE 'Active' END
ELSE hdd.Disposition END
ELSE
CASE --ISNULL(A.LockedStatus, - 1)
WHEN A.LockedStatus = -1 THEN 'InProduction'
WHEN A.LockedStatus = 1 THEN 'Active'
WHEN A.LockedStatus = 2 THEN 'On Hold'
WHEN A.LockedStatus = 3 AND iph_gen.PurchaseType = 7 THEN 'Redeployment'
WHEN A.LockedStatus = 3 AND iph_gen.PurchaseType = 12 THEN 'Donation'
WHEN A.LockedStatus = 3 AND iph_gen.PurchaseType = 11 THEN 'Legal Hold Release'
WHEN A.LockedStatus = 3 AND iph_gen.PurchaseType NOT IN (7,11,12) THEN 'Sold'
WHEN A.LockedStatus = 4 THEN 'Upgrade'
WHEN A.LockedStatus = 5 THEN 'Recycled'
WHEN A.LockedStatus = 6 THEN 'Sold Off-site'
ELSE ''
END
END
AS Disposition,
CASE WHEN a.LockedStatus = 5
THEN ftRec.CreatedOn
ELSE iph_gen.shipDate END AS DispositionDate,
'' AS Comments, --REPLACE(r.AdditionalNotes , CHAR(10), '') as Comments,
isnull(GTu.Name, 'N/A') AS FinalGrade,
' ', ' ',
ISNULL(CASE ftRec.RateTypeID WHEN 1 THEN a.Weight ELSE 1 END * ftRec.Rate,0) AS RecyclingFees,
-- ISNULL(ft.RemarketingPercentage,0),
isnull(ft.Rate,0) AS RemarketingCredit,
--isnull(gt.Name, 'N/A') AS RegisteredGrade,
--' ',
0.00 as LeaseReturnFee,
CASE WHEN iph_gen.PurchaseType = 7 THEN ISNULL(ipds_gen.Price,0)
ELSE 0.00
END AS RedploymentFee,
CASE WHEN ccsg.GroupName LIKE '%Donation%' THEN
CASE WHEN iph_gen.PurchaseType = 12 THEN ISNULL(ipds_gen.Price,0)
ELSE 0.00
END ELSE 0.00
END AS DonationFee
--Rank () over
-- (
-- PARTITION by [InviovceDate]
-- Order by [SSE Number] DESC
-- ) as rank
FROM vAssetSimpleView a
INNER JOIN itTransactionProcess AS tp ON tp.TransactionID = a.OrigTransactionID
INNER JOIN itLots AS l ON l.LotID = tp.LotNumber
LEFT JOIN [10.10.1.188,13151].Adviser.dbo.Recovery r on l.LotID = r.MRMLotId
--LEFT JOIN [54.84.218.110,13151].Adviser.dbo.Account acc on r.AccountId = acc.Id
LEFT OUTER JOIN AccountLocationOrderNo AS alon ON alon.LotID = l.LotID
LEFT JOIN AccountLocation AS al ON al.LocationID = l.LocationID
LEFT JOIN Account acc ON al.accountId = acc.AccountID
LEFT JOIN itDocks AS d ON d.DockID = l.DockScheduledOn
LEFT OUTER JOIN Contact AS c ON al.Contact_Onsite = c.ContactID
LEFT OUTER JOIN itGradeTypes AS g ON g.RecID = a.Grade
--JOINS FOR ORDERS
LEFT JOIN itPurchaseDetails ipd_gen on ipd_gen.assetid = a.assetid
LEFT JOIN itPurchaseDetailService ipds_gen on ipd_gen.RecID = ipds_gen.PurchaseDetailRecId
LEFT JOIN ContractCustomServiceGroups ccsg on ccsg.recid = ipds_gen.ServiceGroupId
--AND ipds_donation.ServiceGroupId IN (SELECT RecID FROM [ContractCustomServiceGroups] WHERE GroupName Like '%Donation%')
LEFT JOIN itPurchaseHeader iph_gen on ipd_gen.PurchaseHeaderRecID = iph_gen.RecID
--AND iph_donation.PurchaseType = 12
--END JOINS FOR ORDERS
LEFT OUTER JOIN dbo.Reporting_Inventory_Harddrives hdd ON hdd.AssetID = a.AssetID
--LEFT OUTER JOIN vAssetServicesByType vast ON a.AssetID = vast.AssetRecId
--OUTER APPLY
--(
-- SELECT ph.RecID AS OrderID, v.VendorName, ph.OrderDate,ph.shipdate, cup.FirstName + ' ' + cup.LastName AS SalesRep, pd.LineTotal AS ResalePrice
-- FROM dbo.itPurchaseDetails pd
-- INNER JOIN dbo.itPurchaseHeader ph ON ph.RecID = pd.PurchaseHeaderRecID
-- INNER JOIN dbo.itVendorLocation vl ON vl.LocationID = ph.VendorLocationID
-- INNER JOIN dbo.itVendor v ON v.VendorID = vl.VendorID
-- INNER JOIN dbo.aspnet_custom_UserProfiles cup ON cup.UserID = ph.CreatedBy
-- WHERE ph.Status > 1
-- AND pd.AssetID = a.AssetID
--) AS do
LEFT OUTER JOIN FinancialTransactions ft ON ft.FinancialTypeID = 9 AND ft.AssetID = a.AssetID
LEFT OUTER JOIN FinancialTransactions ft1 ON ft.FinancialTypeID = 8 AND ft.FKeyID = a.OrigTransactionID AND ft.AssetID = 0
LEFT OUTER JOIN FinancialTransactions ftRec ON ftRec.AssetID = a.AssetID
LEFT OUTER JOIN FinancialTransactions ftRec1 ON ftRec.FKeyID = a.OrigTransactionID and ftRec.AssetID = 0 AND ftRec.FinancialTypeID = 1
--LEFT OUTER JOIN FinancialTransactions ft ON ((ft.FinancialTypeID = 9 AND ft.AssetID = a.AssetID and (ft.FinancialTypeID = 8 AND ft.FKeyID = a.OrigTransactionID AND ft.AssetID = 0))
--LEFT OUTER JOIN FinancialTransactions ftRec ON ftRec.AssetID = a.AssetID and ftRec.FKeyID = a.OrigTransactionID and ftRec.AssetID = 0 AND ftRec.FinancialTypeID = 1
--OUTER APPLY
--(
-- SELECT ISNULL(SUM(csa.ServicePriceTotal),0) AS total
-- FROM ContractServicesApplied csa
-- WHERE csa.AssetID = a.AssetID
-- AND csa.Completed = 1
--) AS csa
--CROSS APPLY(SELECT ISNULL(SUM(T.PartValue),0) AS UpgradeValue
--FROM dbo.itAssetComponentTransactions AS T
--INNER JOIN dbo.itAssetComponents AS AC
--ON AC.RecID = T.AssetComponentID WHERE
--(T.AssetID = A.AssetId)
--AND (T.TransactionTypeID = 1) AND (AC.DateRemoved IS NULL)) as uv
--LEFT JOIN [dbo].[Reporting_Inventory_Harddrives] rih ON a.AssetId = rih.AssetId
--Tony Added For Recycling Fee Column
LEFT JOIN (SELECT tp.AssetId, -1*ft.Total AS RecyclingTotal
FROM itTransactionProcess tp
INNER JOIN itProcessSteps ps ON tp.NextProcessStepID = ps.ProcessStepID
LEFT OUTER JOIN FinancialTransactions ft ON ft.FKeyID = tp.TransactionID
INNER JOIN itLots il on tp.LotNumber = il.LotID
INNER JOIN AccountLocation al on al.LocationID = il.LocationID
INNER JOIN Account acc on al.AccountID = acc.accountId
WHERE acc.AccountID = @AccountId
AND ps.IsRecyclingProcess = 1
AND ps.IsEndProcess = 1 ) As Recycling ON a.AssetID = Recycling.AssetID
--End Recycling Join
--Tony Added to include Parts section in Asset Detail body of reports
LEFT JOIN itProcessSteps ps ON tp.NextProcessStepID = ps.ProcessStepID
--End Parts Join
--Tony Added for Grade/Condition data
LEFT OUTER JOIN dbo.itGradeTypes GT ON GT.RecID = a.Grade
INNER JOIN itAsset ia ON a.AssetID = ia.AssetRecId
LEFT OUTER JOIN dbo.itGradeTypes GTu ON GTu.RecID = ia.Upgrade_Grade --Join to asset because vAssetSimpleView does not have upgrade_grade
--End Grade Join
OUTER APPLY
(
SELECT TOP 1 CASE WHEN ps.IsRecyclingProcess = 1 THEN 'Recycled' WHEN ps.ProcessStepID = 25 THEN 'Resale' ELSE ps.Name END AS Disposition
FROM itTransactionProcess tp
INNER JOIN itProcessSteps ps on ps.ProcessStepID = tp.CurrentProcessStepID
WHERE tp.AssetID = a.AssetID
AND ps.IsEndProcess = 1
--AND a.AssetTypeID = 1
) AS assetDisp
WHERE acc.accountid = @AccountId
AND ((iph_gen.shipDate >= @StartDate AND iph_gen.ShipDate <= @EndDate) OR (a.LockedStatus = 5 AND ftRec.CreatedOn >= @StartDate AND ftRec.CreatedOn <= @EndDate))
AND ((a.AssetTypeID =1) OR (a.AssetTypeID = 2 and a.ClassID = 919) OR (a.AssetTypeID = 3 and a.ClassID = 919))
我知道这个特定的过程会降低性能。我希望能够以一种既能保持速度又能删除额外数据的方式编写这些连接。我试图添加执行计划，但如果你能提供一些关于如何做到这一点的见解，那将是有帮助的。
解决方案
假设您在 ft 和 ftrec 上是正确的，您可以尝试以下操作：
在顶部添加代码：
DROP TABLE IF EXISTS #ftrec
SELECT *
INTO #ftrec
FROM FinancialTransactions
where AssetID in (select AssetID from vAssetSimpleView where AssetID is not null)
CREATE CLUSTERED INDEX IX_ftrec on #ftrec(AssetID)
并更换
LEFT OUTER JOIN FinancialTransactions ft ON ft.FinancialTypeID = 9
AND ft.AssetID = a.AssetID
LEFT OUTER JOIN FinancialTransactions ftRec ON ftRec.AssetID = a.AssetID
和
LEFT OUTER JOIN #ftrec ft ON ft.FinancialTypeID = 9
AND ft.AssetID = a.AssetID
LEFT OUTER JOIN #ftrec ftRec ON ftRec.AssetID = a.AssetID
这假设 FinancialTransactions 有点小。您可以仅将 * 替换为 ftRec 实际需要的列。性能影响可能出现在#ftrec 的初始查询填充上。如果 FinancialTransactions 上的数据很大，则此解决方案可能不可行。
推荐阅读
- python - 如何在字符串中查找模式
- java - 不包含我的代码的奇怪的 EDT 违规
- javascript - 为每个 Web 应用程序生成唯一的本地存储密钥
- swift - 我想在声明它的函数之外引用一个不可变变量
- c++ - 迭代时从向量和内存中删除一个对象
- assembly - 汇编代码，需要帮助了解 'movzwl 0x40272e(,%rax,4),%esi' 在做什么
- nativescript - Nativescript RadListView 标头消失（Android）
- sql - 将列中的数组与另一个查询的计数结果相乘
- php - PHP检查输入字段是否不为空以及是否有特殊字符
- ruby - Ruby 中的战地游戏