sql - 优化这块存储过程

Question

所以这是我正在调整的存储过程的一部分。我发现这运行得很慢。我大约需要3个小时。我认为数据库可能缺少索引或视图。我相信joins 是它如此缓慢的原因，但我不知道如何拆分连接，因此 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

标签： sqlperformancejoinquery-optimizationsql-tuning

Answer 1

没有执行计划，也没有添加索引的能力，只能进行很多猜测。首先要做的是执行计划——在你提出问题之前，如果这不能帮助你将执行计划包括在你的问题中。

我的第一个猜测是我会改变表格的顺序。这似乎FinancialTransactions是最重要的，所以我将其用作“来自”表：

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这对于单个输出列来说可能是很多开销。