优化存储过程的这一部分

Question

所以这是我正在调整的存储过程的一部分。我发现这运行得很慢。我大约需要3个小时。我认为数据库可能缺少索引或视图。我相信join是它如此缓慢的原因，但我不知道拆分连接的方法，因此 Sql 不会很难处理它们。我想临时表。如果你能帮忙，谢谢。

SELECT ftrec.AccountName,
       cast(ftrec.AssetId as varchar(50)) as AssetId,
       va.Class as Category,
       gt.Name as RegisteredGrade,
       gtu.Name as FinishedGrade,
       'Recycling' as SKU,
       DATENAME(month,ftrec.CreatedOn) + ' ' + cast(DATEPART(yyyy,ftrec.CreatedOn) as varchar(50)) AS ServiceCompleteMonth,
       ISNULL(CASE ftRec.RateTypeID 
              WHEN 1 THEN a.Weight 
              ELSE 1 END * ftRec.Rate,0) as ServiceTotal,
       itwrec.Name as 'Warehouse',
       asu.UserName as 'TechnicianName'
FROM itAsset a (nolock)
inner JOIN vAssetSimpleView va on a.AssetRecId = va.AssetId
inner JOIN dbo.itGradeTypes gtu ON gtu.RecID = a.Upgrade_Grade
inner JOIN itGradeTypes gt ON va.Grade = gt.RecId
inner JOIN FinancialTransactions ftRec (nolock) ON ((ftRec.AssetID = a.AssetRecID) OR (ftRec.FKeyID = a.OrigTransactionID and ftRec.AssetID = 0)) AND ftRec.FinancialTypeID = 1 
INNER  JOIN ittransactionprocess tpRec (nolock) on ftRec.fkeyid = tpRec.transactionid 
inner JOIN dbo.itLotTrips lt ON a.OrigLotNumber = lt.LotNumber
inner JOIN dbo.itDocks d ON lt.DockID = d.DockID
inner JOIN dbo.itWarehouse itwrec ON d.WarehouseID = itwrec.WarehouseID
--LEFT JOIN itWarehouse itwrec (nolock) on ftrec.WarehouseID = itwrec.WarehouseID
inner JOIN Aspnet_users asu (nolock) ON asu.userid = tprec.ModifiedBy
WHERE ftrec.CreatedOn > @StartDate  AND ftrec.CreatedOn < @EndDate
  AND a.LockedStatus = 5
--and ftrec.Assetid = 285912

Answer 1

由于没有执行计划，也没有添加索引的能力，人们可以执行很多猜测。首先要去的地方是执行计划 - 在您提出问题之前，如果这无助于您将执行计划包含在您的问题中。

我的第一个猜测是我会改变表格的顺序。看来FinancialTransactions是最重要的，所以我会将其用作"from"表：

SELECT
    ftrec.AccountName
  , CAST( ftrec.AssetId AS varchar(50) )                                                                AS AssetId
  , va.Class                                                                                            AS Category
  , gt.Name                                                                                             AS RegisteredGrade
  , gtu.Name                                                                                            AS FinishedGrade
  , 'Recycling'                                                                                         AS SKU
  , DATENAME( MONTH, ftrec.CreatedOn ) + ' ' + CAST( DATEPART( yyyy, ftrec.CreatedOn ) AS varchar(50) ) AS ServiceCompleteMonth
  , ISNULL( CASE ftRec.RateTypeID WHEN 1 THEN a.Weight ELSE 1 END * ftRec.Rate, 0 )                     AS ServiceTotal
  , itwrec.Name                                                                                         AS 'Warehouse'
  , asu.UserName                                                                                        AS 'TechnicianName'
FROM FinancialTransactions ftrec
INNER JOIN itAsset a ON (
       (ftRec.AssetID = a.AssetRecID)
    OR 
       (ftRec.FKeyID = a.OrigTransactionID AND ftRec.AssetID = 0)
    )
INNER JOIN vAssetSimpleView va ON a.AssetRecId = va.AssetId
INNER JOIN dbo.itGradeTypes gtu ON gtu.RecID = a.Upgrade_Grade
INNER JOIN itGradeTypes gt ON va.Grade = gt.RecId
INNER JOIN ittransactionprocess tpRec ON ftRec.fkeyid = tpRec.transactionid
INNER JOIN dbo.itLotTrips lt ON a.OrigLotNumber = lt.LotNumber
INNER JOIN dbo.itDocks d ON lt.DockID = d.DockID
INNER JOIN dbo.itWarehouse itwrec ON d.WarehouseID = itwrec.WarehouseID
--LEFT JOIN itWarehouse itwrec on ftrec.WarehouseID = itwrec.WarehouseID
INNER JOIN Aspnet_users asu ON asu.userid = tprec.ModifiedBy
WHERE ftrec.CreatedOn > @StartDate
AND ftrec.CreatedOn < @EndDate
    --and ftrec.Assetid = 285912
AND a.LockedStatus = 5
;

此外，我也可能尝试使用可能对性能产生影响的 CTE。

WITH ftRec
AS (
    SELECT
        AccountName
      , AssetId
      , CreatedOn
      , RateTypeID
    FROM FinancialTransactions
    WHERE CreatedOn > @StartDate
    AND CreatedOn < @EndDate
    --and Assetid = 285912
)
SELECT
    ftrec.AccountName
  , CAST( ftrec.AssetId AS varchar(50) )                                                                AS AssetId
  , va.Class                                                                                            AS Category
  , gt.Name                                                                                             AS RegisteredGrade
  , gtu.Name                                                                                            AS FinishedGrade
  , 'Recycling'                                                                                         AS SKU
  , DATENAME( MONTH, ftrec.CreatedOn ) + ' ' + CAST( DATEPART( yyyy, ftrec.CreatedOn ) AS varchar(50) ) AS ServiceCompleteMonth
  , ISNULL( CASE ftRec.RateTypeID WHEN 1 THEN a.Weight ELSE 1 END * ftRec.Rate, 0 )                     AS ServiceTotal
  , itwrec.Name                                                                                         AS 'Warehouse'
  , asu.UserName                                                                                        AS 'TechnicianName'
FROM ftrec
INNER JOIN itAsset a ON (
       (ftRec.AssetID = a.AssetRecID)
    OR 
       (ftRec.FKeyID = a.OrigTransactionID AND ftRec.AssetID = 0)
    )
INNER JOIN vAssetSimpleView va ON a.AssetRecId = va.AssetId
INNER JOIN dbo.itGradeTypes gtu ON gtu.RecID = a.Upgrade_Grade
INNER JOIN itGradeTypes gt ON va.Grade = gt.RecId
INNER JOIN ittransactionprocess tpRec ON ftRec.fkeyid = tpRec.transactionid
INNER JOIN dbo.itLotTrips lt ON a.OrigLotNumber = lt.LotNumber
INNER JOIN dbo.itDocks d ON lt.DockID = d.DockID
INNER JOIN dbo.itWarehouse itwrec ON d.WarehouseID = itwrec.WarehouseID
--LEFT JOIN itWarehouse itwrec on ftrec.WarehouseID = itwrec.WarehouseID
INNER JOIN Aspnet_users asu ON asu.userid = tprec.ModifiedBy
WHERE a.LockedStatus = 5
;

我不会使用NOLOCK。它不是性能调优项，它只是允许查询包含未提交的数据（"脏读"）。

此外，正如 Avi 在评论中已经提到的，尝试从源表中查找va.Class而不是视图vAssetSimpleView这对于单个输出列来说可能是很大的开销。