Sql-Server

varchar 和 nvarchar 調整儲存過程 - 在這種情況下如何提高性能？

  • August 12, 2016

我有以下程序，每天呼叫超過一百萬次，我認為可以對其進行調整以更好地使用資源。

ALTER PROCEDURE [DenormV2].[udpProductTaxRateGet]
(
   @itemNo varchar ( 20 ),
   @calculateDate datetime,
   @addressLine1 nvarchar( 50 ),
   @addressLine2 nvarchar( 50 ),
   @addressLine3 nvarchar( 50 ),
   @addressLine4 nvarchar( 50 ),
   @addressLine5 nvarchar( 50 ),
   @addressLine6 nvarchar( 50 ),
   @postalCode nvarchar( 20 ),
   @countryCode varchar( 2 ),
   @addressFormatID int
)
WITH EXECUTE AS 'webUserWithRW'
AS
--see Bocss2.dbo.[fnGetProductTax] for equivalent logic and comments in Bocss
DECLARE @Addresses TABLE (TaxRegionId int NOT NULL)

INSERT INTO @Addresses(TaxRegionId)
SELECT  DISTINCT TaxRegionId
FROM    dbo.[ShipTaxAddress]
WHERE   [CountryCode] = @countryCode
AND     [AddressFormatID] = @addressFormatID
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine1]), ISNULL(@addressLine1, '')) = ISNULL(@addressLine1, '')
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine2]), ISNULL(@addressLine2, '')) = ISNULL(@addressLine2, '')
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine3]), ISNULL(@addressLine3, '')) = ISNULL(@addressLine3, '')
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine4]), ISNULL(@addressLine4, '')) = ISNULL(@addressLine4, '')
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine5]), ISNULL(@addressLine5, '')) = ISNULL(@addressLine5, '')
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine6]), ISNULL(@addressLine6, '')) = ISNULL(@addressLine6, '')
AND     @postalcode Like ISNULL ( CONVERT(nvarchar(20),[MatchPostalCode]), @postalcode)




SELECT DISTINCT ISNULL(pst.TaxCode, '') as TaxCode
    , ISNULL(pst.TaxRate, 0) as TaxRate
FROM    dbo.[ProductShipTax] pst
       INNER JOIN
       @Addresses a
           ON pst.TaxRegionId = a.TaxRegionId
WHERE   pst.[ItemNo] = @itemNo
AND     @calculateDate BETWEEN pst.[DateFrom] AND pst.[DateTo]

GO

此過程將值插入下表中的表變數中：請注意，表中的列是 VARCHAR 並且由於某種原因儲存過程的參數以及程式碼內部，所有內容都轉換為 NVARCHAR。

IF OBJECT_ID('[dbo].[ShipTaxAddress]') IS NOT NULL 
DROP TABLE [dbo].[ShipTaxAddress] 
GO
CREATE TABLE [dbo].[ShipTaxAddress] ( 
[TaxRegionAddressId]  INT                              NOT NULL,
[TaxRegionId]         INT                              NOT NULL,
[CountryCode]         VARCHAR(2)                       NOT NULL,
[AddressFormatId]     INT                              NOT NULL,
[MatchAddressLine1]   VARCHAR(50)                          NULL,
[MatchAddressLine2]   VARCHAR(50)                          NULL,
[MatchAddressLine3]   VARCHAR(50)                          NULL,
[MatchAddressLine4]   VARCHAR(50)                          NULL,
[MatchAddressLine5]   VARCHAR(50)                          NULL,
[MatchAddressLine6]   VARCHAR(50)                          NULL,
[MatchPostalCode]     VARCHAR(20)                          NULL,
CONSTRAINT   [PK_ShipTaxAddress]  PRIMARY KEY CLUSTERED    ([TaxRegionAddressId] asc))

請注意，此表

$$ dbo $$.$$ ShipTaxAddress $$少於 200 行。

這是另一個表：

sp_gettabledef ‘dbo.ProductShipTax’ - 這是我用來獲取表定義的。如果您有興趣，可以在這里分享程式碼。

IF OBJECT_ID('[dbo].[ProductShipTax]') IS NOT NULL 
DROP TABLE [dbo].[ProductShipTax] 
GO
CREATE TABLE [dbo].[ProductShipTax] ( 
[ProductShipTaxID]  INT              IDENTITY(1,1)   NOT NULL,
[DateFrom]          SMALLDATETIME                    NOT NULL,
[DateTo]            SMALLDATETIME                    NOT NULL,
[TaxRate]           DECIMAL(18,4)                    NOT NULL,
[ItemNo]            VARCHAR(20)                      NOT NULL,
[TaxCode]           VARCHAR(20)                          NULL,
[TaxRegionId]       INT                              NOT NULL,
CONSTRAINT   [PK_ProductShipTax]  PRIMARY KEY CLUSTERED    ([ProductShipTaxID] asc))

GO

CREATE NONCLUSTERED INDEX [IX_ProductShipTax_ITemNo_DateFrom_DateTo] 
  ON [dbo].[ProductShipTax] ([ItemNo] asc, [DateFrom] asc, [DateTo] asc)

CREATE NONCLUSTERED INDEX [idx_ProductShipTax__K7_K5_K2_K3_K1_K4_6_INCL] 
  ON [dbo].[ProductShipTax] ([TaxRegionId] asc, [ItemNo] asc, [DateFrom] asc, [DateTo] asc, [ProductShipTaxID] asc, [TaxRate] asc)
  INCLUDE ([TaxCode])

這是這個過程的XML執行計劃，按照下面的語句執行：

   exec dbo.udpProductTaxRateGet 
@itemNo=N'35638956',
@calculateDate='Aug  8 2016  1:01:46:760PM',
@addressLine1=N'',
@addressLine2=N'',
@addressLine3=N'114 FORGE LN',
@addressLine4=N'',
@addressLine5=N'FEASTERVILLE TREVOSE',
@addressLine6=N'PA',
@postalcode=N'190537838',
@countryCode=N'US',
@addressFormatID=2

我從哪裡開始？

這就是我改進此過程的方式：

我創建了以下索引：

   CREATE INDEX IDX_ShipTaxAddress_ShipTaxAddress
   ON dbo.[ShipTaxAddress] (CountryCode,
                            AddressFormatID,
                            MatchPostalCode)
   INCLUDE (TaxRegionId,
            [MatchAddressLine1],
            [MatchAddressLine2],
            [MatchAddressLine3],
            [MatchAddressLine4],
            [MatchAddressLine5],
            [MatchAddressLine6])
   GO

    CREATE NONCLUSTERED INDEX IX_ProductShipTax_ITemNo_DateFrom_DateTo 
    ON [dbo].[ProductShipTax] (  [ItemNo] ASC  
                               , [DateFrom] ASC  
                               , [DateTo] ASC  )   
   INCLUDE (TaxRegionId ,TaxCode,TaxRate)
   WITH (DROP_EXISTING=ON)

我已將表中的相關列從 VARCHAR 更改為 NVARCHAR，以消除轉換的需要。表變成了這樣：

IF OBJECT_ID('[dbo].[ShipTaxAddress]') IS NOT NULL 
DROP TABLE [dbo].[ShipTaxAddress] 
GO
CREATE TABLE [dbo].[ShipTaxAddress] ( 
[TaxRegionAddressId]  INT                              NOT NULL,
[TaxRegionId]         INT                              NOT NULL,
[CountryCode]         VARCHAR(2)                       NOT NULL,
[AddressFormatId]     INT                              NOT NULL,
[MatchAddressLine1]   NVARCHAR(50)                         NULL,
[MatchAddressLine2]   NVARCHAR(50)                         NULL,
[MatchAddressLine3]   NVARCHAR(50)                         NULL,
[MatchAddressLine4]   NVARCHAR(50)                         NULL,
[MatchAddressLine5]   NVARCHAR(50)                         NULL,
[MatchAddressLine6]   NVARCHAR(50)                         NULL,
[MatchPostalCode]     NVARCHAR(20)                         NULL,
CONSTRAINT   [PK_ShipTaxAddress]  
PRIMARY KEY NONCLUSTERED ([TaxRegionAddressId] asc))
GO

我改變了程序：

ALTER PROCEDURE [DenormV2].[udpProductTaxRateGet]
(
   @itemNo varchar ( 20 ),
   @calculateDate datetime,
   @addressLine1 nvarchar( 50 ),
   @addressLine2 nvarchar( 50 ),
   @addressLine3 nvarchar( 50 ),
   @addressLine4 nvarchar( 50 ),
   @addressLine5 nvarchar( 50 ),
   @addressLine6 nvarchar( 50 ),
   @postalCode nvarchar( 20 ),
   @countryCode varchar( 2 ),
   @addressFormatID int
)
WITH EXECUTE AS 'webUserWithRW'
AS
--see Bocss2.dbo.[fnGetProductTax] for equivalent logic and comments in Bocss


SELECT @postalcode    = CASE WHEN @postalcode   = N'' THEN NULL ELSE @postalcode   END
SELECT @addressLine1  = CASE WHEN @addressLine1 = N'' THEN NULL ELSE @addressLine1 END
SELECT @addressLine2  = CASE WHEN @addressLine2 = N'' THEN NULL ELSE @addressLine2 END
SELECT @addressLine3  = CASE WHEN @addressLine3 = N'' THEN NULL ELSE @addressLine3 END
SELECT @addressLine4  = CASE WHEN @addressLine4 = N'' THEN NULL ELSE @addressLine4 END
SELECT @addressLine5  = CASE WHEN @addressLine5 = N'' THEN NULL ELSE @addressLine5 END
SELECT @addressLine6  = CASE WHEN @addressLine6 = N'' THEN NULL ELSE @addressLine6 END



SELECT TOP 1  ISNULL(pst.TaxCode, '') as TaxCode
           , ISNULL(pst.TaxRate, 0) as TaxRate
FROM    dbo.[ProductShipTax] pst
WHERE EXISTS (

   SELECT  TaxRegionId
   FROM    dbo.[ShipTaxAddress]
   WHERE   [CountryCode] = @countryCode
   AND     [AddressFormatID] = @addressFormatID

   AND ([MatchAddressLine1] = @AddressLine1 OR ([MatchAddressLine1] IS NULL AND @AddressLine1 IS NULL) )
   AND ([MatchAddressLine2] = @AddressLine2 OR ([MatchAddressLine2] IS NULL AND @AddressLine2 IS NULL) )
   AND ([MatchAddressLine3] = @AddressLine3 OR ([MatchAddressLine3] IS NULL AND @AddressLine3 IS NULL) )
   AND ([MatchAddressLine4] = @AddressLine4 OR ([MatchAddressLine4] IS NULL AND @AddressLine4 IS NULL) )
   AND ([MatchAddressLine5] = @AddressLine5 OR ([MatchAddressLine5] IS NULL AND @AddressLine5 IS NULL) )
   AND ([MatchAddressLine6] = @AddressLine6 OR ([MatchAddressLine6] IS NULL AND @AddressLine6 IS NULL) )

   AND (@postalcode = [MatchPostalCode]     OR ([MatchPostalCode]   IS NULL AND @postalcode   IS NULL) )
   AND  TaxRegionId = pst.TaxRegionId     

)
AND pst.[ItemNo] = @itemNo
AND @calculateDate BETWEEN pst.[DateFrom] AND pst.[DateTo]

GO

比較以下內容時：

USE US16HSMMProduct_ORIGINAL
GO
exec dbo.udpProductTaxRateGet 
       @itemNo=N'31997299',
       @calculateDate='Aug  8 2016  1:01:46:760PM',
       @addressLine1=N'',
       @addressLine2=N'',
       @addressLine3=N'',
       @addressLine4=N'',
       @addressLine5=N'',
       @addressLine6=N'FL',
       @postalcode=N'',
       @countryCode=N'US',
       @addressFormatID=2
go
USE US16HSMMProduct_AFTER_CHANGES
GO
exec DenormV2.udpProductTaxRateGet
       @itemNo=N'31997299',
       @calculateDate='Aug  8 2016  1:01:46:760PM',
       @addressLine1=N'',
       @addressLine2=N'',
       @addressLine3=N'',
       @addressLine4=N'',
       @addressLine5=N'',
       @addressLine6=N'FL',
       @postalcode=N'',
       @countryCode=N'US',
       @addressFormatID=2
go

我們得到這個： 在此處輸入圖像描述

這是舊程序的執行計劃

這是新程序的執行計劃

  1. 擺脫對 nvarchar 的轉換。您的表正在使用 varchar，請將您的參數也更改為 varchar。
  2. 擺脫這樣的邏輯：
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine1]), ISNULL(@addressLine1, '')) = ISNULL(@addressLine1, '')

當您對 where 子句中的列使用函式時，SQL 不能正常工作。而是這樣做（記住我們正在擺脫 nvarchar 參數。

AND ([MatchAddressLine1] = @AddressLine1
    OR ([MatchAddressLine1] IS NULL and @AddressLine1 IS NULL) )

請注意，您目前的邏輯將返回一行，其中一個為 NULL，另一個為 ‘’。如果您仍然需要該邏輯，則必須再添加兩個 OR 選項，但它仍然可以工作。優化器可以更好地使用這種類型的邏輯。 3. 您也可以從表變數更改為臨時表。您可以在此處看到一個顯著差異。 4. 最後一個選項是您可以完全擺脫臨時表/變數並使用 CTE。

WITH Addresses AS (
   SELECT  DISTINCT TaxRegionId
   FROM    dbo.[ShipTaxAddress]
   WHERE   [CountryCode] = @countryCode
   AND     [AddressFormatID] = @addressFormatID
   AND     ([MatchAddressLine1] = @addressLine1
       OR ([MatchAddressLine1] IS NULL and @AddressLine1 IS NULL) )
   AND     ([MatchAddressLine2] = @addressLine2
       OR ([MatchAddressLine2] IS NULL and @AddressLine2 IS NULL) )
   AND     ([MatchAddressLine3] = @addressLine3
       OR ([MatchAddressLine3] IS NULL and @AddressLine3 IS NULL) )
   AND     ([MatchAddressLine4] = @addressLine4
       OR ([MatchAddressLine4] IS NULL and @AddressLine4 IS NULL) )
   AND     ([MatchAddressLine5] = @addressLine5
       OR ([MatchAddressLine5] IS NULL and @AddressLine5 IS NULL) )
   AND     ([MatchAddressLine6] = @addressLine6
       OR ([MatchAddressLine6] IS NULL and @AddressLine6 IS NULL) )
   AND    (@postalcode IS NULL OR [MatchPostalCode] = @postalcode)
)
SELECT DISTINCT ISNULL(pst.TaxCode, '') as TaxCode
    , ISNULL(pst.TaxRate, 0) as TaxRate
FROM    dbo.[ProductShipTax] pst
       INNER JOIN Addresses a
           ON pst.TaxRegionId = a.TaxRegionId
WHERE   pst.[ItemNo] = @itemNo
AND     @calculateDate BETWEEN pst.[DateFrom] AND pst.[DateTo]

您應該檢查我的程式碼並確保邏輯正確，但我相信它會正確。還可以使用類似的東西SET STATISTICS IO ON來獲得它之前和之後執行多長時間的好時間（以毫秒為單位）。發生了比你的程式碼比我的程式碼更快的事情。

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

相關問答

Sql-Server

查詢調整 - SQL Server

  • February 22, 2012
檢視問答
Sql-Server

SQL 連接的性能

  • March 23, 2022
檢視問答
Sql-Server

如果 CTE 在查詢中定義並且從未使用過，它會發出聲音嗎？

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

TOP 如何（以及為什麼）影響執行計劃？

  • November 27, 2021
檢視問答
Sql-Server

非聚集索引更新未顯示在執行計劃中

  • August 12, 2021
檢視問答
Sql-Server

為什麼我的查詢在環境 A 中執行得很快，而在環境 B 中卻很慢？

  • June 30, 2021
檢視問答