Reutilização de código de classificação para banco de dados multilíngue

Olhando para o que você tem até agora, é bom que [utils].[OrderByLanguage]seja uma função com valor de tabela em linha (ITVF), mas ainda parece ser uma subconsulta correlacionada onde, para cada linha api_utils.GetTable(@LangID), passa em todas as linhas api_utils.GetTable(@LangID)para classificá-la (ou seja, a ORDER BYcláusula.

Aplicar agrupamentos em tempo de execução pode ser bastante caro, pois é necessário gerar as chaves de classificação para esses valores naquele momento. Para obter o melhor desempenho, a criação de um índice gerará as chaves de classificação antecipadamente e até as colocará na ordem correta. Mas lidar com várias localidades é realmente complicado. Criar uma cópia da coluna de origem para cada localidade pode exigir uma enorme quantidade de espaço adicional em disco (e E/S), dependendo do tamanho das strings, de quantas localidades / agrupamentos são necessários e quantas linhas haverá os próximos 3 - 5 anos. Felizmente, você pode criar colunas computadas não persistentes para serem essas cópias (que não ocupam espaço) e indexá-las (o que ocupa espaço). Embora isso provavelmente não seja viável se houvesse 10 localidades, uma coluna de base de pelo menosNVARCHAR(200), e 1 milhão (ou mais) de linhas, para sua situação deve funcionar bem. Com essa abordagem, a parte dinâmica estará na escolha de qual coluna selecionar (alcançável via SQL dinâmico ou IFinstruções, dependendo da situação). Mas, como você pode ver no exemplo a seguir (ative "Incluir Plano de Execução Real"), ambas as consultas filtradas (últimas 2 consultas) obtêm Buscas de Índice nos índices pretendidos e retornam os resultados esperados:

Você pode ver uma demonstração ao vivo disso em 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

No entanto, dado o código para api.GetTableisso, pode não ser a melhor abordagem. Se você quiser manter a estrutura atual (tanto quanto possível), poderá fazer o seguinte:

1. Converter api.GetTableem um procedimento armazenado
2. Use um OUTPUTparâmetro para não precisar lidar com um conjunto de resultados
3. @Sortdeve ser uma tabela temporária, não uma variável de tabela
4. Crie a #Sorttabela temporária com todas as colunas necessárias, exceto /[Name] ) title: ID, categorye [system].

5. Em uma série de IFdeclarações, adicione a [title]coluna, mas com o Collation adequado:

   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;
   
   ...
   

6. Altere FROM api_utils.GetTable(@LangID) tna @Result=consulta principal para ser:FROM #Sort t
7. Altere ORDER BY (SELECT s.RowNumber...na @Result=consulta principal para ser:ORDER BY t.[title]

Isso exigirá a reaplicação do Collation em cada execução, mas:

1. não requer nenhuma alteração nas tabelas base
2. ele irá reutilizar os dados retornados por api.GetTable(sem chamada dupla)
3. ele usará estatísticas na [Name]coluna
4. não será uma subconsulta correlacionada (woo hoo!)
5. ele oferece a opção de criar um índice na [Name]coluna depois que ela for preenchida.

Reutilização de código

Ao mudar para procedimentos armazenados e tabelas temporárias, podemos realmente manter o objetivo de reutilização de código. O código para adicionar a coluna de classificação à tabela temporária local pode ser abstraído para outro procedimento armazenado. Embora a criação de uma tabela temporária em uma chamada de subprocedimento não ajude, pois essa tabela temporária desaparecerá quando a chamada de subprocedimento terminar, as alterações feitas em uma tabela temporária que existe antes da chamada de subprocedimento sobreviverão à conclusão dessa chamada . Por exemplo:

Configurar

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

Teste

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

Ao colocar os agrupamentos específicos apenas neste procedimento armazenado, você deve ter apenas um local para atualizar ao adicionar um novo idioma ao suporte.

Juntando tudo

Com todos os itens acima em mente, acabamos com o seguinte procedimento armazenado contendo uma única chamada para api_utils.GetTable(), sem subconsulta correlacionada e api_utils.AddSortColumn (que você precisa criar) pode ser usado em outros procedimentos armazenados e em outras tabelas base:

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;

Outra abordagem é ter apenas um gerador de código. Isso é se você precisar de desempenho máximo, pois é apenas cerca de 14% mais rápido que a solução marcada como a resposta para meus parâmetros fornecidos (dados 14% somados rapidamente quando você o tem em vários lugares e em várias partes do mesmo código) .

# Place the following in your code then run this script and it will update for you
# without the `#` comment symbols. Replace `title` with whatever it field it should
# be sorting by.

#-- Start-Collation-Sort {sort-by:title}
#
#-- End-Collation-Sort

$text = @'
-- Start-Collation-Sort {sort-by:$1,table:$2}
-- This is generated code. Use ./CollationSortGenerator.ps1 to change
-- this code and all the other files which contain this code.
  CASE @LangID
    WHEN 1 THEN ROW_NUMBER() OVER (ORDER BY $2.$1) -- en
    WHEN 3 THEN ROW_NUMBER() OVER (ORDER BY $2.$1) -- en-ca
    WHEN 6 THEN ROW_NUMBER() OVER (ORDER BY $2.$1) -- 'en-nz'
    WHEN 5 THEN ROW_NUMBER() OVER (ORDER BY $2.$1) -- 'en-za'
    WHEN 2 THEN ROW_NUMBER() OVER (ORDER BY $2.$1 COLLATE Modern_Spanish_CI_AI) -- es
    WHEN 4 THEN ROW_NUMBER() OVER (ORDER BY $2.$1 COLLATE French_CI_AI) -- 'fr-ca'
  END
-- `End-Collation-Sort
'@

$regex =
@'
(?m)^\s*-- Start-Collation-Sort {sort-by:(.+),table:(.+)}
[^`]*
-- `End-Collation-Sort
'@

Get-ChildItem -Path *.sql -recurse |
    Where-Object { (Select-String "-- Start-Collation-Sort"  $_ -Quiet -CaseSensitive -SimpleMatch) } |
    ForEach-Object {
        Write-Output ("Updating file ... " + ($_ | Resolve-Path -Relative ))

        (Get-Content $_ -Raw) -replace $regex, $text |
        Set-Content $_
    }