需要帮助优化存储过程

Question

最近开始在一家公司工作，发现一个正在爬网的存储过程。目前在这里工作的人都不知道如何优化它，但我觉得它很有趣。想知道是否有人看到了什么明显的东西：

--------- [Debug Block] ------------
SET @DebugMessage = 'Execution Started'
EXEC [dbo].[MarkInProcessDebugPoint] 
     @ProcId = @@PROCID
    ,@DebugType = 0
    ,@Message = @DebugMessage
--------- [Debug Block] ------------
BEGIN TRY
DECLARE @tbl_Downloads  as TABLE (
    Revision bigint,
    AssignedStores varchar (max),
    Id varchar (64),
    DownloadId int
)
-- Get Id, and legacy Id
INSERT @tbl_Downloads (Revision, DownloadId, Id)
   SELECT 
      isnull(DCG.[DOWNLOAD_ID], 0), [Value], 
      isnull(DCG.[GUID], newid())
   FROM 
      dbo.fn_Valid_Split(@Downloads, ';') AS CD 
   LEFT JOIN
      [DIGITAL_CONTENT_GUID] DCG ON CD.Value = DCG.[DOWNLOAD_ID]
INSERT [DIGITAL_CONTENT_GUID]
   SELECT 
      DownloadId, 1, max(Id)
   FROM 
      @tbl_Downloads
   WHERE 
      Revision = 0 and DownloadId > 0
   GROUP BY 
      DownloadId    
-- Create track GUID values if the album is in the list
INSERT [DIGITAL_CONTENT_GUID] (DOWNLOAD_ID, CONTENT_TYPE_ID, [GUID]) 
   SELECT 
      A.DOWNLOAD_ID, 1, NEWID()
   FROM
      (SELECT DISTINCT 
          P.DOWNLOAD_ID
       FROM 
          DOWNLOAD_PACK P (NOLOCK)
       INNER JOIN
          @tbl_Downloads T ON P.DOWNLOAD_PACK_ID = T.DownloadId
       WHERE 
          NOT EXISTS (SELECT 1 
                      FROM [DIGITAL_CONTENT_GUID] G (nolock) 
                      WHERE G.DOWNLOAD_ID = P.DOWNLOAD_ID)
      ) A
--------- [Debug Block] ------------
SELECT @DebugMessage = 'Get Id, and legacy Id' 
EXEC [dbo].[MarkInProcessDebugPoint] 
     @ProcId = @@PROCID
    ,@DebugType = 0
    ,@Message = @DebugMessage
--------- [Debug Block] ------------
SELECT 
    --TA.Revision, 
    [dbo].[Fn_ContentExport_GetAssignedStores_Json]  (TA.DownloadId, 1) AS AssignedStores,
    TA.Id, 
    TA.DownloadId As LegacyId, 
    [dbo].[Fn_ContentExport_GetContentNameSpace]  (CD.Download_Type_Id) AS [Namespace],
    [dbo].[Fn_ContentExport_GetContentTypeName]  (CD.Download_Type_Id) AS [Type],
    T.TITLE AS Title,
    T.DISPLAY_ARTIST_NAME AS Subtitle,
    CD.DESCRIPTION AS [Description],
    [dbo].[Fn_ContentExport_GetContentArtworks]  (TA.DownloadId, CD.Download_Type_Id, 0) AS Artwork,
    [dbo].[Fn_ContentExport_GetTitleArtists_Json]  (CD.TITLE_ID, 0) AS Artists,
    [dbo].[Fn_ContentExport_GetContentTypeData]  (TA.DownloadId, CD.Download_Type_Id, 2240) AS TypeEntity,
    '' AS PublicTags,
    [dbo].[Fn_ContentExport_GetInternalTags_Json] (CD.Global_Content_Id) AS InternalTags,
    CD.SUPPLIER_ID AS SupplierId,
    S.NAME AS SupplierName,
    CD.RELEASE_DATE AS [ReleaseDateTime2],
    CD.VALID_TO_DATE As [ExpiryDateTime2],
    CASE
         WHEN CD.IS_DELETED = 1 THEN 1 
         WHEN CD.IS_DELETED = 0 AND [dbo].[Fn_ContentExport_GetContentIsSearchable] (TA.DownloadId) = 0 THEN 1
         ELSE 0
    END As Archived,
    CD.DATE_CREATED AS CreationDateTime,
    CD.DATE_MODIFIED AS ModifiedDateTime,
    [dbo].[Fn_ContentExport_GetContentTierPriceId]  (CD.Download_Type_Id, TA.DownloadId) As [PricingTierId],
    CD.TITLE_ID As [LegacyTitleId]
    -- [dbo].[Fn_ContentExport_GetContentIsSearchable]  (TA.DownloadId) AS IsSearchable
FROM 
    @tbl_Downloads AS TA
INNER JOIN
    CONTENT_DOWNLOAD CD (NOLOCK) ON TA.DownloadId = CD.DOWNLOAD_ID
INNER JOIN
    TITLE T (NOLOCK) ON T.TITLE_ID = CD.TITLE_ID
INNER JOIN
    SUPPLIER S (NOLOCK) ON CD.SUPPLIER_ID = s.SUPPLIER_ID
WHERE
    CD.DOWNLOAD_TYPE_ID <> 125
END TRY
BEGIN CATCH
    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    DECLARE @ErrorMessage NVARCHAR(2048);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();
    --------- [Debug Block] ------------
    SET @DebugMessage = CAST(@ErrorSeverity AS VARCHAR(100)) + ':' +        CAST(@ErrorState AS VARCHAR(100)) + ':' + @ErrorMessage
    EXEC [dbo].[MarkInProcessDebugPoint] 
         @ProcId = @@PROCID
        ,@DebugType = 1
        ,@Message = @DebugMessage
    --------- [Debug Block] ------------
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
      END CATCH

非常感谢

Answer 1

缺少fn_Valid_Split和@Downloads的内容，所以假设分隔列表中的项目很少（10-20个或类似的项目）。

我会检查的几件事：

• 从计划缓存中检查这些语句中的哪一条实际上会导致问题（高I/O、高CPU时间）。您还应该检查"set statistics io"打开后查询返回的内容。统计信息I/O不会显示函数中的I/O，因此您也需要计划缓存。如果所有联接的表都有正确的联接索引，请至少查看最后一次选择。

• 一定要查看上次选择中的用户定义函数。最好去掉它们，如果不能，请检查是否可以将它们更改为内联表值函数。

• 如果fn_Valid_Split被调用的次数超过了它应该调用的次数（例如每次执行不止一次），则将其从联接中取出，并用它返回的内容填充临时表（或表变量）。您还应该检查Jeff Moden的DelimitedSplit8k，以防您的实现不太好：http://www.sqlservercentral.com/articles/Tally+表/72993/

• 如果@tbl_Downloads中有很多行，请尝试将其更改为临时表，因为这样它将包含统计信息。当DownloadId是临时表时，您也可以向它添加（clustered？）索引。这可能有助于加入。

• 我也绝对建议删除nolock。它会比它"修复"的东西引起更多的头痛。如果存在阻塞，我会尝试正确修复它。