Sql-Server

提高性能 - 如何在 sql server 2005 中將表作為參數傳遞？

  • August 17, 2016

我一直在監視我的網路伺服器中儲存過程的執行情況，以便調整它們以獲得最佳性能。

在此處輸入圖像描述

我將把每天執行次數最多的過程稱為 myprocedure，它的程式碼在這裡：

create  procedure [dbo].[myprocedure] (  
   @Tier1 varchar(10),  
   @LanguageID int,  
   @SeasonItemID VARCHAR(5) = NULL,
   @ListNoteTypeCode varchar(max),  
   @CacheDuration int output )  
   with execute as 'myuser'  
as

declare @NoteTypeCodeTable table (  
   NoteTypeCode varchar(50) )  
declare @ListNoteTypeCodeXml xml  

set @ListNoteTypeCodeXml = convert(xml, @ListNoteTypeCode)  

insert  into @NoteTypeCodeTable ( NoteTypeCode )  
       select  BulletPoint.NoteTypeCode.value('.', 'varchar(50)') as NoteTypeCode  
       from    @ListNoteTypeCodeXml.nodes('/BulletPoint/NoteTypeCode') as BulletPoint ( NoteTypeCode )  


select  pbp.Tier1, pbp.LanguageId, pbp.NoteText, pbp.NoteTypeCode,  
       pbp.NoteGroup, pbp.SortOrder  
from    dbo.ProductBulletPoint pbp  
join    @NoteTypeCodeTable ntc on pbp.NoteTypeCode = ntc.NoteTypeCode  
where   Tier1 = @Tier1 and  
       LanguageId = @LanguageID and
       (
       SeasonItemId = @SeasonItemID  
       or
       @SeasonItemID is null
       )

select  @CacheDuration = Duration  
from    dbo.CacheDuration  
where   [Key] = 'Product'

GO

此過程接收一個參數@ListNoteTypeCode，該參數在過程內部用於將所有可能的值（大多數情況下是一個值）轉換為表變數。

表變數，稍後用於連接。

如何提高此儲存過程的性能？帶有 xml 部分的位是 CPU 密集型的，是否有另一種方法可以將表傳遞給過程，從而充分利用 CPU？

我仍然必須執行良好的跟踪並確定此過程在@ListNoteTypeCode 中產生多個值的頻率，但無論如何我仍然需要找到解決方案。

我已經設置了一個伺服器端跟踪，以辨識通常呼叫此過程的可能方式。踪跡可以在這裡看到。

到目前為止，我只看到了以下變體（@ListNoteTypeID 沒有價值）：

declare @p5 int  
set @p5=NULL  
exec dbo.udpProductBulletPointSelectByTier1NoteTypeCode 
@Tier1=N'AR740',
@LanguageID=1,
@SeasonItemID=N'16HSM',
@ListNoteTypeCode=N'',
@CacheDuration=@p5 output  
select @p5

在 XML 中生成以下查詢計劃：

查詢計劃程序 dbo.udpProductBulletPointSelectByTier1NoteTypeCode

如果我發現任何不同的東西，我會更新這個問題。

我正在使用 sql server 2005 企業版。

新程序

根據srutzky的回答以及許多評論和其他答案，我提出了以下新程序：

CREATE PROCEDURE [DENORMV2].[udpProductBulletPointSelectByTier1NoteTypeCode] (  
   @Tier1 VARCHAR(10),  
   @LanguageID INT,  
   @SeasonItemID VARCHAR(5) = NULL,
   @ListNoteTypeCode XML,  
   @CacheDuration INT OUTPUT )  
   WITH EXECUTE AS 'webUserWithRW'  
AS

       SELECT  pbp.Tier1, pbp.LanguageId, pbp.NoteText, pbp.NoteTypeCode,  
               pbp.NoteGroup, pbp.SortOrder  
       FROM    dbo.ProductBulletPoint pbp  

       WHERE   Tier1 = @Tier1 
         AND   LanguageId = @LanguageID 
         AND   (      SeasonItemId = @SeasonItemID  
                 OR
                      @SeasonItemID is null
               )

         AND pbp.NoteTypeCode IN (

                SELECT  NoteTypeCode=BulletPoint.NoteTypeCode.value('./text()[1]', 'varchar(50)')
                  FROM  @ListNoteTypeCode.nodes('/BulletPoint/NoteTypeCode') AS BulletPoint ( NoteTypeCode )

         )

SELECT  @CacheDuration = Duration  
FROM    dbo.CacheDuration  
WHERE   [Key] = 'Product'
GO

當我執行以下命令時，為了比較兩個過程：

SET STATISTICS IO ON
SET STATISTICS TIME ON


use US16HSMMProduct_ORIGINAL
go

declare @p5 int  set @p5=86400  
exec dbo.udpProductBulletPointSelectByTier1NoteTypeCode 
@Tier1=N'WW099',
@LanguageID=3,
@SeasonItemID=N'16AUT',
@ListNoteTypeCode=N'<BulletPoint><NoteTypeCode>GarmentComposition</NoteTypeCode><NoteTypeCode>FootwearAccessoryComposition</NoteTypeCode></BulletPoint>',
@CacheDuration=@p5 output  select @p5

use US16HSMMProduct_AFTER_CHANGES
go
declare @p5 int  set @p5=86400 
exec DenormV2.udpProductBulletPointSelectByTier1NoteTypeCode
@Tier1=N'WW099',
@LanguageID=3,
@SeasonItemID=N'16AUT',
@ListNoteTypeCode=N'<BulletPoint><NoteTypeCode>GarmentComposition</NoteTypeCode><NoteTypeCode>FootwearAccessoryComposition</NoteTypeCode></BulletPoint>',
@CacheDuration=@p5 output  select @p5

我從 SQL Sentry 計劃資源管理器中得到這個（部分視圖）：

在此處輸入圖像描述

這是來自sql server（部分視圖）

在此處輸入圖像描述

**僅根據您的程式碼，**我建議將.value()函式中的 XQuery 更改./text()[1]為.. 這樣做會讓您更接近基於屬性的 XML 的效率：

select  BulletPoint.NoteTypeCode.value('./text()[1]', 'varchar(50)') as NoteTypeCode

其他次要注意事項：

  • @ListNoteTypeCode將數據類型更改為XML不需要該CONVERT步驟。
  • 如果您不能傳入 XML 類型，至少將@ListNoteTypeCode數據類型更改為NVARCHAR(MAX)不需要進行隱式轉換，因為XML是 UTF-16，與NVARCHAR/相同NCHAR。

主要考慮：如果ProductBulletPoint.NoteTypeCode列的數據類型是真的VARCHAR（似乎是因為執行計劃中沒有隱式轉換），那麼：

  • 理想情況下，您不應該對程式碼進行字元串比較。程式碼應該在查找表中定義，您可以將其轉換為數字鍵，該鍵將是儲存在ProductBulletPoint表中的值，就像@LanguageID. 所以應該有一個NoteTypeCodes類似的表：
CREATE TABLE dbo.NoteTypeCodes
(
 NoteTypeCodeID TINYINT NOT NULL
                CONSTRAINT [PK_NoteTypeCodes] PRIMARY KEY,
 NoteTypeCode   VARCHAR(50) NOT NULL
);

然後，最好讓應用程式碼在 ID 列表中傳遞，這樣您只需要使用一個簡單的字元串拆分器將它們拆分成@NoteTypeCodeTable一個單獨的TINYINT列。否則，您需要拆分程式碼名稱和 JOIN 以NoteTypeCodes獲取要插入的 ID 值@NoteTypeCodeTable。

  • 如果您不能將NoteTypeCode列更改為NoteTypeCodeID TINYINT，那麼至少考慮在該列上使用二進制排序規則（即，_BIN2如果可用，則以結尾），因為高度懷疑是否需要不區分大小寫和/或文化- 感知程式碼比較。您可以使用以下方法更改列：
ALTER TABLE [dbo].[ProductBulletPoint]
 ALTER COLUMN [NoteTypeCode] {current_datatype}
 COLLATE Latin1_General_100_BIN2 {current_NULL_or_NOT-NULL_setting};

您可能需要刪除並重新創建任何使用該[NoteTypeCode]列的索引。但是這個一次性修復意味著您不需要COLLATE Latin1_General_100_BIN2使用此列添加任何查詢，因為排序優先級會處理它。

但是，您必須在通過呼叫LOWER傳入值插入/更新程式碼值時對其進行規範化。您可以在每個執行INSERTand/or的儲存過程中執行此操作UPDATE，或者您可以通過AFTER INSERT, UPDATE觸發器在一個位置自動處理它。然後你只需要記住LOWER在連接和 WHERE 條件中使用的值。

  • 該建議很可能也同樣適用於SeasonItemId列和@SeasonItemID輸入參數。

**根據發布的 XML 執行計劃以及您在進行跟踪時發現的內容，**似乎可能是最有影響的因素是正在呼叫儲存過程並將@ListNoteTypeCode其設置為空字元串。這導致沒有行插入到@NoteTypeCodeTable表變數中。並且，因為@NoteTypeCodeTable用作過濾器（因為它是 Inner Joined to ProductBulletPoint），所以不會從主查詢返回任何行。因此，如果將這個儲存過程設置為空字元串，則 此儲存過程所做的任何工作都是徒勞的。@ListNoteTypeCode

鑑於呼叫此儲存過程的頻率，您需要執行以下操作之一：

  1. 由於@ListNoteTypeCode是必需參數且空字元串不是有效值，因此這可能表示應用程式碼中存在錯誤。如果這是一個錯誤，請修復應用程式碼以在@ListNoteTypeCode為空時不呼叫此儲存過程。
  2. 如果由於某種原因，應用程式碼呼叫此儲存過程@ListNoteTypeCode為空是有效的，那麼當傳入該空值時，跳過實際處理並簡單地返回一個空結果集（這對所有人來說都是一樣的）呼叫者，因此如果任何程式碼在傳入空字元串時期望獲得結果集，則不會中斷任何應用程式碼）：
create procedure [dbo].[myprocedure] (
...
as

IF (@ListNoteTypeCode <> '')
BEGIN
 declare @NoteTypeCodeTable table ( NoteTypeCode varchar(50) )
 ...
 select  pbp.Tier1, pbp.LanguageId, pbp.NoteText, pbp.NoteTypeCode,
         pbp.NoteGroup, pbp.SortOrder
 from    dbo.ProductBulletPoint pbp
 join    @NoteTypeCodeTable ntc on...
 ...
END;
ELSE
BEGIN
 select  pbp.Tier1, pbp.LanguageId, pbp.NoteText, pbp.NoteTypeCode,
         pbp.NoteGroup, pbp.SortOrder
 from    dbo.ProductBulletPoint pbp
 WHERE   1 = 0;
END;

select  @CacheDuration = Duration
...

引用自：https://dba.stackexchange.com/questions/145329

相關問答

Sql-Server

如何證明以下 T-SQL 對性能不利？

  • September 16, 2013
檢視問答
Sql-Server

為什麼添加 TOP 1 會顯著降低性能？

  • December 29, 2021
檢視問答
Sql-Server

SQL Server：記憶體壓力、使用者連接數和 CPU 使用率

  • March 6, 2021
檢視問答
Sql-Server

查詢計劃中有關記憶體“過度授予”的警告 - 如何找出導致它的原因？

  • January 26, 2021
檢視問答
Sql-Server

儲存過程與內聯 SQL

  • August 7, 2020
檢視問答
Sql-Server

@parameter IS NULL 可以減慢查詢的執行速度嗎？

  • June 5, 2020
檢視問答