optimizing this piece of a stored procedure

Question

So this is a piece of a stored procedure that I'm tuning. I have found that this runs very slow. I takes about 3 hours. I think database may lack indexes or views. I believe that the joins are the reason that it's so slow, but I don't know a way to split the joins up so Sql dosn't have a hard time processing them. I thought temp table. If you could help thanks.

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

I assume this is a view vAssetSimpleView. Is it possible to join to physical table than view ? Also if you could share your execution plan along with rows in those table and that would be helpful to people to give answers. Also you can use join hints if you can, see the details in this link. https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-join?view=sql-server-2017 — Avi, Jan 18 '19 at 00:58
[an actual execution plan](https://learn.microsoft.com/en-us/sql/relational-databases/performance/display-an-actual-execution-plan?view=sql-server-2017) would really help. Also see https://www.brentozar.com/pastetheplan/ — Paul Maxwell, Jan 18 '19 at 01:50

score 0 · Answer 1 · answered Jan 18 '19 at 01:40

With no execution plan, and no ability to add indexes, there is just a lot of guessing one can perform. The very first place to go is the execution plan - before you raise a question, then if that does not help you include the execution plan with your question.

My first guess is that I would alter the sequence of tables. It appears that FinancialTransactions is the most important, so I would use that as the "from" table:

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
;

Additionally I might also try using a CTE this might have an effect on performance.

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
;

I would NOT use NOLOCK. It is not a performance tuning item, it simply permits the query to include uncommitted data ("dirty reads").

Also, as already mentioned in comments by Avi try to locate va.Class from a source table instead of the view vAssetSimpleView which is probably a lot of overhead for a single output column.

optimizing this piece of a stored procedure

1 Answers1