SQL Server - Como as páginas de dados são armazenadas ao usar um índice clusterizado

Question

Der Kommissar

Asked: 2015-04-23 10:02:18 +0800 CST2015-04-23 10:02:18 +0800 CST 2015-04-23 10:02:18 +0800 CST

Selecione todos os registros, junte-se à tabela A se a associação existir, a tabela B se não

772

Então aqui está o meu cenário:

Estou trabalhando na localização para um projeto meu e normalmente faria isso no código C#, mas quero fazer isso no SQL um pouco mais, pois estou tentando melhorar um pouco meu SQL.

Ambiente: SQL Server 2014 Standard, C# (.NET 4.5.1)

Nota: a linguagem de programação em si deve ser irrelevante, estou incluindo apenas para completar.

Então eu meio que consegui o que eu queria, mas não na medida que eu queria. Já faz um tempo (pelo menos um ano) desde que eu fiz qualquer SQL JOIN, exceto os básicos, e isso é bastante complexo JOIN.

Aqui está um diagrama das tabelas relevantes do banco de dados. (Há muito mais, mas não é necessário para esta parte.)

Diagrama de banco de dados

Todos os relacionamentos descritos na imagem estão completos no banco de dados - as restrições PKe FKestão todas configuradas e operacionais. Nenhuma das colunas descritas é nullcapaz. Todas as tabelas têm o esquema dbo.

Agora, eu tenho uma consulta que quase faz o que eu quero: ou seja, dado ANY Id of SupportCategoriese ANY Id of Languages, ela retornará:

Se houver uma tradução correta para esse idioma para essa string (ou seja StringKeyId-> StringKeys.Idexiste, e em LanguageStringTranslations StringKeyId, LanguageId, e StringTranslationIdcombinação existe, então ele carrega StringTranslations.Textpara esse StringTranslationId.

Se a combinação LanguageStringTranslations StringKeyId, LanguageIde NÃO existir, ele carrega o valor. O é um dado .StringTranslationIdStringKeys.NameLanguages.Idinteger

Minha consulta, seja uma bagunça, é a seguinte:

SELECT CASE WHEN T.x IS NOT NULL THEN T.x ELSE (SELECT
    CASE WHEN dbo.StringTranslations.Text IS NULL THEN dbo.StringKeys.Name ELSE dbo.StringTranslations.Text END AS Result
FROM dbo.SupportCategories
    INNER JOIN dbo.StringKeys
        ON dbo.SupportCategories.StringKeyId = dbo.StringKeys.Id
    INNER JOIN dbo.LanguageStringTranslations
        ON dbo.StringKeys.Id = dbo.LanguageStringTranslations.StringKeyId
    INNER JOIN dbo.StringTranslations
        ON dbo.StringTranslations.Id = dbo.LanguageStringTranslations.StringTranslationId
WHERE dbo.LanguageStringTranslations.LanguageId = 38 AND dbo.SupportCategories.Id = 0) END AS Result FROM (SELECT (SELECT
    CASE WHEN dbo.StringTranslations.Text IS NULL THEN dbo.StringKeys.Name ELSE dbo.StringTranslations.Text END AS Result
FROM dbo.SupportCategories
    INNER JOIN dbo.StringKeys
        ON dbo.SupportCategories.StringKeyId = dbo.StringKeys.Id
    INNER JOIN dbo.LanguageStringTranslations
        ON dbo.StringKeys.Id = dbo.LanguageStringTranslations.StringKeyId
    INNER JOIN dbo.StringTranslations
        ON dbo.StringTranslations.Id = dbo.LanguageStringTranslations.StringTranslationId
WHERE dbo.LanguageStringTranslations.LanguageId = 5 AND dbo.SupportCategories.Id = 0) AS x) AS T

O problema é que ele não é capaz de me fornecer TODOS os SupportCategoriese seus respectivos StringTranslations.Textse existir, OU seus StringKeys.Namese não existirem. É perfeito em fornecer qualquer um deles, mas não em todos. Basicamente, é para impor que, se um idioma não tiver uma tradução para uma chave específica, o padrão é usar o StringKeys.Nameque é de StringKeys.DefaultLanguageIdtradução. (Idealmente, nem isso faria, mas, em vez disso, carregaria a tradução para StringKeys.DefaultLanguageId, o que eu mesmo posso fazer se apontar na direção certa para o restante da consulta.)

Eu gastei muito tempo nisso, e eu sei que se eu fosse apenas escrever em C# (como eu costumo fazer) já estaria feito. Eu quero fazer isso no SQL e estou tendo problemas para obter a saída que eu gosto.

A única ressalva é que quero limitar o número de consultas reais aplicadas. Todas as colunas são indexadas e, como eu gosto delas por enquanto, e sem testes de estresse reais, não posso indexá-las ainda mais.

Edit: Outra observação, estou tentando manter o banco de dados o mais normalizado possível, então não quero duplicar as coisas se puder evitá-lo.

Dados de exemplo

Fonte

dbo.SupportCategories (Inteira):

Id  StringKeyId
0   0
1   1
2   2

dbo.Languages (185 registros, mostrando apenas dois como exemplo):

Id  Abbreviation    Family  Name    Native
38  en  Indo-European   English English
48  fr  Indo-European   French  français, langue française

dbo.LanguagesStringTranslations (Inteira):

StringKeyId LanguageId  StringTranslationId
0   38  0
1   38  1
2   38  2
3   38  3
4   38  4
5   38  5
6   38  6
7   38  7
1   48  8 -- added as example

dbo.StringKeys (Inteira):

Id  Name    DefaultLanguageId
0   Billing 38
1   API 38
2   Sales   38
3   Open    38
4   Waiting for Customer    38
5   Waiting for Support 38
6   Work in Progress    38
7   Completed   38

dbo.StringTranslations (Inteira):

Id  Text
0   Billing
1   API
2   Sales
3   Open
4   Waiting for Customer
5   Waiting for Support
6   Work in Progress
7   Completed
8   Les APIs -- added as example

Saída de corrente

Dada a consulta exata abaixo, ele gera:

Result
Billing

Saída Desejada

Idealmente, eu gostaria de poder omitir o específico SupportCategories.Ide obter todos eles, assim (independentemente se o idioma 38 Englishfoi usado, ou 48 French, ou QUALQUER outro idioma no momento):

Id  Result
0   Billing
1   API
2   Sales

Exemplo Adicional

Dado que eu deveria adicionar uma localização para French(ou seja, adicionar 1 48 8a LanguageStringTranslations), a saída mudaria para (nota: este é apenas um exemplo, obviamente eu adicionaria uma string localizada a StringTranslations) (atualizado com exemplo em francês):

Result
Les APIs

Saída Desejada Adicional

Dado o exemplo acima, a seguinte saída seria desejada (atualizada com o exemplo em francês):

Id  Result
0   Billing
1   Les APIs
2   Sales

(Sim, eu sei que tecnicamente isso está errado do ponto de vista da consistência, mas é o que seria desejado na situação.)

Editar:

Pequena atualização, alterei a estrutura da dbo.Languagestabela, eliminei a Id (int)coluna dela e a substituí por Abbreviation(que agora é renomeada para Id, e todas as chaves estrangeiras e relacionamentos relativos atualizados). Do ponto de vista técnico, esta é uma configuração mais apropriada na minha opinião devido ao fato de que a tabela está limitada aos códigos ISO 639-1, que são exclusivos para começar.

Tl; dr

Então: a pergunta, como eu poderia modificar essa consulta para retornar tudo eSupportCategories depois retornar StringTranslations.Textpara isso StringKeys.Id, Languages.Idcombinação ouStringKeys.Name se NÃO existisse?

Meu pensamento inicial é que eu poderia de alguma forma converter a consulta atual para outro tipo temporário como outra subconsulta e envolver essa consulta em outra SELECTinstrução e selecionar os dois campos que quero ( SupportCategories.Ide Result).

Se eu não encontrar nada, farei apenas o método padrão que normalmente uso, que é carregar tudo SupportCategoriesno meu projeto C# e, em seguida, executar a consulta que tenho acima manualmente em cada arquivo SupportCategories.Id.

Obrigado por todas e quaisquer sugestões/comentários/críticas.

Além disso, peço desculpas por ser absurdamente longo, só não quero nenhuma ambiguidade. Estou frequentemente no StackOverflow e vejo perguntas que carecem de substância, não queria cometer esse erro aqui.

2 respostas

Voted

Aaron Bertrand · Answer 1 · 2015-04-23T11:21:42+08:00

Best Answer

Aaron Bertrand

2015-04-23T11:21:42+08:002015-04-23T11:21:42+08:00

Aqui está a primeira abordagem que eu criei:

DECLARE @ChosenLanguage INT = 48;

SELECT sc.Id, Result = MAX(COALESCE(
   CASE WHEN lst.LanguageId = @ChosenLanguage      THEN st.Text END,
   CASE WHEN lst.LanguageId = sk.DefaultLanguageId THEN st.Text END)
)
FROM dbo.SupportCategories AS sc
INNER JOIN dbo.StringKeys AS sk
  ON sc.StringKeyId = sk.Id
LEFT OUTER JOIN dbo.LanguageStringTranslations AS lst
  ON sk.Id = lst.StringKeyId
  AND lst.LanguageId IN (sk.DefaultLanguageId, @ChosenLanguage)
LEFT OUTER JOIN dbo.StringTranslations AS st
  ON st.Id = lst.StringTranslationId
  --WHERE sc.Id = 1
  GROUP BY sc.Id
  ORDER BY sc.Id;

Basicamente, obtenha as strings em potencial que correspondem ao idioma escolhido e obtenha todas as strings padrão e, em seguida, agregue para que você escolha apenas uma por Id- priorize o idioma escolhido e, em seguida, use o padrão como substituto.

Você provavelmente pode fazer coisas semelhantes com UNION/ EXCEPT, mas suspeito que isso quase sempre levará a várias verificações nos mesmos objetos.

17

Paul White · Answer 2 · 2015-04-29T05:39:09+08:00

Uma solução alternativa que evita o INe o agrupamento na resposta de Aaron:

DECLARE 
    @SelectedLanguageId integer = 48;

SELECT 
    SC.Id,
    SC.StringKeyId,
    Result =
        CASE
            -- No localization available
            WHEN LST.StringTranslationId IS NULL
            THEN SK.Name
            ELSE
            (
                -- Localized string
                SELECT ST.[Text]
                FROM dbo.StringTranslations AS ST
                WHERE ST.Id = LST.StringTranslationId
            )
        END
FROM dbo.SupportCategories AS SC
JOIN dbo.StringKeys AS SK
    ON SK.Id = SC.StringKeyId
LEFT JOIN dbo.LanguageStringTranslations AS LST
    WITH (FORCESEEK) -- Only for low row count in sample data
    ON LST.StringKeyId = SK.Id
    AND LST.LanguageId = @SelectedLanguageId;

Conforme observado, a FORCESEEKdica é necessária apenas para obter o plano com aparência mais eficiente devido à baixa cardinalidade da LanguageStringTranslationstabela com os dados de amostra fornecidos. Com mais linhas, o otimizador escolherá uma busca de índice naturalmente.

O plano de execução em si tem uma característica interessante:

Plano de execução

A propriedade Pass Through na última junção externa significa que uma pesquisa na StringTranslationstabela só é executada se uma linha foi encontrada anteriormente na LanguageStringTranslationstabela. Caso contrário, o lado interno dessa junção será ignorado completamente para a linha atual.

Tabela DDL

CREATE TABLE dbo.Languages
(
    Id integer NOT NULL,
    Abbreviation char(2) NOT NULL,
    Family nvarchar(96) NOT NULL,
    Name nvarchar(96) NOT NULL,
    [Native] nvarchar(96) NOT NULL,

    CONSTRAINT PK_dbo_Languages
        PRIMARY KEY CLUSTERED (Id)
);

CREATE TABLE dbo.StringTranslations
(
    Id bigint NOT NULL,
    [Text] nvarchar(128) NOT NULL,

    CONSTRAINT PK_dbo_StringTranslations
    PRIMARY KEY CLUSTERED (Id)
);

CREATE TABLE dbo.StringKeys
(
    Id bigint NOT NULL,
    Name varchar(64) NOT NULL,
    DefaultLanguageId integer NOT NULL,

    CONSTRAINT PK_dbo_StringKeys
    PRIMARY KEY CLUSTERED (Id),

    CONSTRAINT FK_dbo_StringKeys_DefaultLanguageId
    FOREIGN KEY (DefaultLanguageId)
    REFERENCES dbo.Languages (Id)
);

CREATE TABLE dbo.SupportCategories
(
    Id integer NOT NULL,
    StringKeyId bigint NOT NULL,

    CONSTRAINT PK_dbo_SupportCategories
        PRIMARY KEY CLUSTERED (Id),

    CONSTRAINT FK_dbo_SupportCategories
    FOREIGN KEY (StringKeyId)
    REFERENCES dbo.StringKeys (Id)
);

CREATE TABLE dbo.LanguageStringTranslations
(
    StringKeyId bigint NOT NULL,
    LanguageId integer NOT NULL,
    StringTranslationId bigint NOT NULL,

    CONSTRAINT PK_dbo_LanguageStringTranslations
    PRIMARY KEY CLUSTERED 
        (StringKeyId, LanguageId, StringTranslationId),

    CONSTRAINT FK_dbo_LanguageStringTranslations_StringKeyId
    FOREIGN KEY (StringKeyId)
    REFERENCES dbo.StringKeys (Id),

    CONSTRAINT FK_dbo_LanguageStringTranslations_LanguageId
    FOREIGN KEY (LanguageId)
    REFERENCES dbo.Languages (Id),

    CONSTRAINT FK_dbo_LanguageStringTranslations_StringTranslationId
    FOREIGN KEY (StringTranslationId)
    REFERENCES dbo.StringTranslations (Id)
);

Dados de amostra

INSERT dbo.Languages
    (Id, Abbreviation, Family, Name, [Native])
VALUES
    (38, 'en', N'Indo-European', N'English', N'English'),
    (48, 'fr', N'Indo-European', N'French', N'français, langue française');

INSERT dbo.StringTranslations
    (Id, [Text])
VALUES
    (0, N'Billing'),
    (1, N'API'),
    (2, N'Sales'),
    (3, N'Open'),
    (4, N'Waiting for Customer'),
    (5, N'Waiting for Support'),
    (6, N'Work in Progress'),
    (7, N'Completed'),
    (8, N'Les APIs'); -- added as example

INSERT dbo.StringKeys
    (Id, Name, DefaultLanguageId)
VALUES
    (0, 'Billing', 38),
    (1, 'API', 38),
    (2, 'Sales', 38),
    (3, 'Open', 38),
    (4, 'Waiting for Customer', 38),
    (5, 'Waiting for Support', 38),
    (6, 'Work in Progress', 38),
    (7, 'Completed', 38);

INSERT dbo.SupportCategories
    (Id, StringKeyId)
VALUES
    (0, 0),
    (1, 1),
    (2, 2);

INSERT dbo.LanguageStringTranslations
    (StringKeyId, LanguageId, StringTranslationId)
VALUES
    (0, 38, 0),
    (1, 38, 1),
    (2, 38, 2),
    (3, 38, 3),
    (4, 38, 4),
    (5, 38, 5),
    (6, 38, 6),
    (7, 38, 7),
    (1, 48, 8); -- added as example

Selecione todos os registros, junte-se à tabela A se a associação existir, a tabela B se não

Dados de exemplo

Editar:

Tl; dr

Tabela DDL

Dados de amostra

conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

Selecione todos os registros, junte-se à tabela A se a associação existir, a tabela B se não

Dados de exemplo

Editar:

Tl; dr

2 respostas

Tabela DDL

Dados de amostra

relate perguntas