Sql-Server

多語言數據庫的排序程式碼重用

  • July 14, 2017

我正在嘗試對多語言表中的結果進行排序。我想讓排序算法來自一個函式，但它至少會增加 8% 的性能。所以，我不太確定該怎麼做。因此，對於排序，我使用了一篇關於如何對多語言表進行排序的文章中描述的方法，如下所示：

select UnicodeData,Collation
from (
   select
       ML.UnicodeData,
       ML.Collation,
       RN =
           CASE
               when Collation = 'he-IL' then ROW_NUMBER() OVER (order by unicodedata Collate Hebrew_CI_AS                  )
               when Collation = 'en-US' then ROW_NUMBER() OVER (order by unicodedata Collate SQL_Latin1_General_CP1_CI_AS  )
               when Collation = 'kn-IN' then ROW_NUMBER() OVER (order by unicodedata Collate Indic_General_100_CI_AS       )
               when Collation = 'hi-IN' then ROW_NUMBER() OVER (order by unicodedata Collate Indic_General_100_CI_AS       )
               when Collation = 'ar-EG' then ROW_NUMBER() OVER (order by unicodedata Collate Arabic_CI_AS                  )
               when Collation = 'cs'    then ROW_NUMBER() OVER (order by unicodedata Collate Czech_CI_AS                   )
           END
   from MultipleLanguages ML
) T
order by RN

除了我將collation程式碼抽象為它自己的函式之外，如下所示：

CREATE FUNCTION [utils].[OrderByLanguage]
( @LanguageID tinyint
, @IDName utils.ID_Name READONLY
) RETURNS TABLE AS RETURN
SELECT
     t.ID
   , CASE @LanguageID
       WHEN 1 THEN ROW_NUMBER() OVER (ORDER BY t.[Name]) -- en
       WHEN 3 THEN ROW_NUMBER() OVER (ORDER BY t.[Name]) -- en-ca
       WHEN 6 THEN ROW_NUMBER() OVER (ORDER BY t.[Name]) -- 'en-nz'
       WHEN 5 THEN ROW_NUMBER() OVER (ORDER BY t.[Name]) -- 'en-za'
       WHEN 2 THEN ROW_NUMBER() OVER (ORDER BY t.[Name] COLLATE Modern_Spanish_CI_AI) -- es
       WHEN 4 THEN ROW_NUMBER() OVER (ORDER BY t.[Name] COLLATE French_CI_AI) -- 'fr-ca'
   END RowNumber
FROM @IDName t

但是當我呼叫這個函式時，我必須對錶值函式進行這個尷尬的雙重呼叫。

CREATE FUNCTION api.GetTable
( @LanguageCode VARCHAR(10)
) RETURNS NVARCHAR(MAX)
AS BEGIN

   DECLARE
         @Result NVARCHAR(MAX)
       , @LangID tinyint
   DECLARE @Sort utils.ID_Name

   SET @LangID = api_utils.GetLanguageID(@LanguageCode)

   INSERT INTO @Sort (ID, [Name])
   SELECT
         t.ID
       , t.title
   FROM api_utils.GetTable(@LangID) t

   SET @Result = (
       SELECT
           CONVERT(VARCHAR(10), t.ID) id,
           t.category,
           t.[system],
           t.title,
           JSON_QUERY(utils.ToRawJsonArray((
               SELECT x.[Description]
               FROM api_utils.GetKeywords(t.ID, @LangID) x
               ORDER BY x.[Description]
               FOR JSON AUTO), 'Description')
           ) keywords
       FROM api_utils.GetTable(@LangID) t
       ORDER BY (SELECT s.RowNumber
                 FROM utils.OrderByLanguage(@LangID, @Sort) s
                 WHERE s.ID = t.ID)
       FOR JSON AUTO, ROOT('titles')
       )

   RETURN @Result

END

因此，您可以看到我必須呼叫該函式api_utils.GetTable兩次。據我所知，抽像出排序規則的唯一另一種方法是放入實際的排序算法，然後有一個腳本來搜尋所有程式碼庫，並在我需要添加另一種語言時添加另一種排序語言。有沒有其他方法可以做到這一點？別人做了什麼？最佳做法是什麼？這方面的性能並不是絕對關鍵，但保持精簡是很好的，所以不需要太長時間，因為它已經是一個密集的呼叫。

提前致謝！

更新

在評論中回答@srutzky 的問題：

  1. api_utils.GetTable 返回了多少數據？

從表中返回了大約 150 條記錄。

  1. 為什麼在第一次將結果轉儲到@Sort 時呼叫 api_utils.GetTable 兩次？

該@Sort表是記憶體優化的使用者定義表 ( UDT)。由於我將表格傳遞給utils.OrderByLanguage函式，因此它必須是UDT. 這意味著我需要從內聯函式中獲取數據api_utils.GetTable兩次。我不確定它是否會導致呼叫api_utils.GetTable兩次的性能問題。也許SQL Server足夠聰明來記憶體結果？再次測試INSERT查詢成本為 38%。因此，查詢成本的相當大的一部分。

將類別和系統列添加到 @Sort 並在第一次呼叫中將它們拉回來然後在 FROM 子句中使用 @Sort 不是更快嗎？

由於它UDT對於呼叫該函式的所有不同過程都是通用的，utils.OrderByLanguage因此很難對不同過程將使用的未知數量的列進行概括。

3）這必須是一個函式還是可以是一個儲存過程？

你在談論api_utils.GetTable嗎？我更喜歡api_utils.GetTable保留一個功能，因為它更容易使用和測試。我稱之為api_utils.GetTable.Stored Procedure

如果你在談論utils.OrderByLanguage我不介意它是否是一個stored procedure. 我不確定這會有什麼幫助。所以，如果有的話請告訴我！

更新接受的答案

我發現添加索引並沒有提高性能。我還想我不妨將sort列放在原始#sort表中，因為無論如何它必須是相同的。這減少了我的 SSDT 項目中的警告數量。然後我只是alter像這樣在列上做一個：

ALTER TABLE #AlterSort ALTER COLUMN [sort] nvarchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS

看看你到目前為止所擁有的，它[utils].[OrderByLanguage]是一個內聯表值函式（ITVF）很好，但它似乎仍然是一個相關的子查詢，其中的每一行都api_utils.GetTable(@LangID)傳入所有行以api_utils.GetTable(@LangID)對其進行排序（即ORDER BY子句。

在執行時應用排序規則可能會非常昂貴，因為它必須在那一刻為這些值生成排序鍵。為了獲得最佳性能，創建索引將提前生成排序鍵，甚至將它們按正確的順序排列。但是處理多個語言環境確實很棘手。為每個語言環境創建源列的副本可能需要大量額外的磁碟空間（和 I/O），具體取決於字元串的大小、需要多少語言環境/排序規則以及將超過多少行未來 3 - 5 年。幸運的是，您可以創建非持久計算列作為那些副本（不佔用任何空間）並索引那些（確實佔用空間）。雖然如果有 10 個語言環境，這可能不可行，但基列至少為NVARCHAR(200)，以及 100 萬（或更多）行，對於您的情況，它應該可以正常工作。使用這種方法，動態部分將是選擇從哪個列中選擇（可以通過動態 SQL 或IF語句實現，具體取決於情況）。但正如您在以下範例中看到的（打開“包括實際執行計劃”），兩個過濾查詢（最後 2 個查詢）都在預期索引上獲取 Index Seeks 並返回預期結果：

你可以在 dbfiddle.uk 上看到一個現場展示。

SET NOCOUNT ON;
-- DROP TABLE #T;
CREATE TABLE #T
(
 [ID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
 [Col1] VARCHAR(10) COLLATE Latin1_General_100_CI_AS,
 [Col2] AS ([Col1] COLLATE Latin1_General_100_CS_AS)
);

CREATE INDEX [IX_#T_Col1] ON [#T] ([Col1]);
CREATE INDEX [IX_#T_Col2] ON [#T] ([Col2]);

INSERT INTO #T ([Col1]) VALUES ('a'), ('A');

SELECT * FROM #T; -- 2 rows

SELECT [ID] FROM #T WHERE [Col1] = 'A'; -- 2 rows

SELECT [ID] FROM #T WHERE [Col2] = 'A'; -- 1 row

但是，鑑於此程式碼api.GetTable可能不是最好的方法。如果您想保留目前結構（盡可能），那麼您可以執行以下操作：

  1. 轉換api.GetTable為儲存過程
  2. 使用OUTPUT參數，因此您無需處理結果集
  3. @Sort應該是臨時表，而不是表變數
  4. 使用除/ ): 、和之外的#Sort所有列創建臨時表。[Name]``title``ID``category``[system]
  5. 在一系列IF語句中，添加[title]列，但使用正確的排序規則：
IF (@LanguageID = 2)
BEGIN
 ALTER TABLE #Sort ADD [title] NVARCHAR(2000) COLLATE Modern_Spanish_CI_AI
END;

IF (@LanguageID = 4)
BEGIN
 ALTER TABLE #Sort ADD [title] NVARCHAR(2000) COLLATE French_CI_AI
END;

...
  1. FROM api_utils.GetTable(@LangID) t將主@Result=查詢更改為：FROM #Sort t
  2. ORDER BY (SELECT s.RowNumber...將主@Result=查詢更改為：ORDER BY t.[title]

這將需要在每次執行時重新應用排序規則，但是：

  1. 它不需要對基表進行任何更改
  2. 它將重新使用api.GetTable（無雙重呼叫）返回的數據
  3. 它將使用[Name]列上的統計資訊
  4. 它不會是一個相關的子查詢（哇哦！）
  5. [Name]它使您可以選擇在填充後在列上創建索引。

程式碼重用

通過切換到儲存過程和臨時表，我們實際上可以實現程式碼重用的目標。將排序列添加到本地臨時表的程式碼可以抽象為另一個儲存過程。雖然在子過程呼叫中創建臨時表沒有幫助，因為一旦該子過程呼叫結束，該臨時表將消失，對在子過程呼叫之前存在的臨時表所做的更改將在該呼叫完成後繼續存在. 例如：

設置

CREATE PROCEDURE #AddSortColumn
(
 @LanguageID TINYINT
)
AS
SET NOCOUNT ON;

DECLARE @IsColumnAdded BIT = 0;

IF (@LanguageID = 2)
BEGIN
 ALTER TABLE #Sort ADD [title] NVARCHAR(2000) COLLATE Modern_Spanish_CI_AI;
 SET @IsColumnAdded = 1;
END;

IF (@LanguageID = 4)
BEGIN
 ALTER TABLE #Sort ADD [title] NVARCHAR(2000) COLLATE French_CI_AI;
 SET @IsColumnAdded = 1;
END;

IF (@IsColumnAdded = 0)
BEGIN
 RAISERROR(N'Invalid @LanguageID: %d', 16, 1, @LanguageID);
 RETURN;
END;
GO

測試

CREATE TABLE #Sort (ID INT);

EXEC tempdb.dbo.sp_help '#Sort'; -- only 1 column

EXEC #AddSortColumn @LanguageID = 4;

EXEC tempdb.dbo.sp_help '#Sort'; -- now there are 2 columns

通過僅將特定排序規則放置在這一個儲存過程中，在添加要支持的新語言時，您應該只有這個位置可以更新。

把它們放在一起

考慮到以上所有內容，我們最終得到以下儲存過程，其中包含對 的單個呼叫api_utils.GetTable()，沒有相關的子查詢，並且 api_utils.AddSortColumn（您需要創建）可用於其他儲存過程和其他基表：

CREATE PROCEDURE api.GetTableAsJSON
(
 @LanguageCode VARCHAR(10),
 @JSONifiedTable NVARCHAR(MAX) OUTPUT
)
AS
SET NOCOUNT ON;

   DECLARE @LangID TINYINT;

   CREATE TABLE #Sort
   (
     [ID] INT NOT NULL PRIMARY KEY,
     [category] ...,
     [system] ...
   );

   SET @LangID = api_utils.GetLanguageID(@LanguageCode)

   EXEC api_utils.AddSortColumn @LanguageID = @LangID; -- add [Title] to #Sort

   INSERT INTO #Sort ([ID], [category], [system], [Title])
     SELECT t.[ID], t.[category], t.[system], t.[title]
     FROM   api_utils.GetTable(@LangID) t

   -- Optional index; try with and without to see which is better.
   --CREATE INDEX [IX_#Sort_Title] ON #Sort ([Title]);

   SET @JSONifiedTable = (
       SELECT
           CONVERT(VARCHAR(10), t.ID) AS [id],
           t.[category],
           t.[system],
           t.title,
           JSON_QUERY(utils.ToRawJsonArray((
               SELECT x.[Description]
               FROM api_utils.GetKeywords(t.[ID], @LangID) x
               ORDER BY x.[Description]
               FOR JSON AUTO), 'Description')
           ) AS [keywords]
       FROM #Sort t
       ORDER BY t.[Title]
       FOR JSON AUTO, ROOT('titles')
       );

   RETURN;
END;

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

相關問答

Sql-Server

使用不同的區分大小寫排序規則時的 SQL Server PATINDEX 問題/錯誤

  • June 28, 2022
檢視問答
Sql-Server

這個語句是什麼意思：SELECT … COLLATE SQL_Latin1_General_CP1_CI_AS AS−‌ −Log我是T_oB和C重新開始____−−−‌−大號這G一世ns噸這乙和Cr和一種噸和d−−-‌- Logins…

  • July 2, 2021
檢視問答
Sql-Server

如何在 SQL Server 中加入全域臨時表？

  • June 11, 2020
檢視問答
Sql-Server

用錯誤字元整理問題

  • April 21, 2020
檢視問答
Sql-Server

如何將 SQL Server Unicode / NVARCHAR 字元串設置為表情符號或補充字元？

  • January 20, 2020
檢視問答
Sql-Server

如何將 Unicode 字元詳細插入 varchar 數據庫？

  • January 6, 2020
檢視問答