All perguntas(dba)

Por hábito, nunca uso SELECT *em código de produção (só uso com consultas de sucata ad-hoc, normalmente ao aprender o esquema de um objeto). Mas me deparei com um caso agora em que estou tentado a usá-lo, mas me sentiria barato se o fizesse.

Meu caso de uso está dentro de um procedimento armazenado onde é criada uma tabela temporária local que deve sempre corresponder à tabela subjacente usada para criá-la, sempre que o procedimento armazenado for executado. A tabela temporária é preenchida muito mais tarde, portanto, um truque rápido para criar a tabela temporária sem ser detalhado seria SELECT * INTO #TempTable FROM RealTable WHERE 1 = 0especialmente para uma tabela com centenas de colunas.

Se o consumidor do meu procedimento armazenado for agnóstico para conjuntos de resultados dinâmicos, há algum problema comigo vendendo meus serviços para SELECT *?

Eu tenho duas tabelas com colunas de chave identicamente nomeadas, digitadas e indexadas. Um deles possui um índice clusterizado exclusivo , o outro possui um índice não exclusivo .

A configuração do teste

Script de configuração, incluindo algumas estatísticas realistas:

DROP TABLE IF EXISTS #left;
DROP TABLE IF EXISTS #right;

CREATE TABLE #left (
    a       char(4) NOT NULL,
    b       char(2) NOT NULL,
    c       varchar(13) NOT NULL,
    d       bit NOT NULL,
    e       char(4) NOT NULL,
    f       char(25) NULL,
    g       char(25) NOT NULL,
    h       char(25) NULL
    --- and a few other columns
);

CREATE UNIQUE CLUSTERED INDEX IX ON #left (a, b, c, d, e, f, g, h)

UPDATE STATISTICS #left WITH ROWCOUNT=63800000, PAGECOUNT=186000;

CREATE TABLE #right (
    a       char(4) NOT NULL,
    b       char(2) NOT NULL,
    c       varchar(13) NOT NULL,
    d       bit NOT NULL,
    e       char(4) NOT NULL,
    f       char(25) NULL,
    g       char(25) NOT NULL,
    h       char(25) NULL
    --- and a few other columns
);

CREATE CLUSTERED INDEX IX ON #right (a, b, c, d, e, f, g, h)

UPDATE STATISTICS #right WITH ROWCOUNT=55700000, PAGECOUNT=128000;

A reprodução

Quando eu uno essas duas tabelas em suas chaves de cluster, espero uma junção MERGE de um para muitos, assim:

SELECT *
FROM #left AS l
LEFT JOIN #right AS r ON
    l.a=r.a AND
    l.b=r.b AND
    l.c=r.c AND
    l.d=r.d AND
    l.e=r.e AND
    l.f=r.f AND
    l.g=r.g AND
    l.h=r.h
WHERE l.a='2018';

Este é o plano de consulta que eu quero:

(Não importa os avisos, eles têm a ver com estatísticas falsas.)

No entanto, se eu alterar a ordem das colunas na junção, assim:

SELECT *
FROM #left AS l
LEFT JOIN #right AS r ON
    l.c=r.c AND     -- used to be third
    l.a=r.a AND     -- used to be first
    l.b=r.b AND     -- used to be second
    l.d=r.d AND
    l.e=r.e AND
    l.f=r.f AND
    l.g=r.g AND
    l.h=r.h
WHERE l.a='2018';

... isto acontece:

O operador Sort parece ordenar os fluxos de acordo com a ordem declarada da junção, ou seja c, a, b, d, e, f, g, h, que adiciona uma operação de bloqueio ao meu plano de consulta.

Coisas que eu olhei

• Eu tentei alterar as colunas para NOT NULL, mesmos resultados.
• A tabela original foi criada com ANSI_PADDING OFF, mas criá-la com ANSI_PADDING ONnão afeta este plano.
• Eu tentei INNER JOINem vez de LEFT JOIN, nenhuma alteração.
• Eu descobri em um 2014 SP2 Enterprise, criei uma reprodução em um 2017 Developer (CU atual).
• A remoção da cláusula WHERE na coluna de índice principal gera o bom plano, mas afeta os resultados.. :)

Por fim, chegamos à questão

• Isso é intencional?
• Posso eliminar a classificação sem alterar a consulta (que é o código do fornecedor, então prefiro não...). Eu posso mudar a tabela e os índices.

Isso já foi solicitado no Stack Overflow , mas apenas para o MySQL. Estou usando o PostgreSQL. Infelizmente (e surpreendentemente) o PostgreSQL não parece ter algo como CHECKSUM table.

Uma solução PostgreSQL seria boa, mas uma genérica seria melhor. Encontrei http://www.besttechtools.com/articles/article/sql-query-to-check-two-tables-have-identical-data , mas não entendo a lógica usada.

Background: Eu reescrevi algum código gerador de banco de dados, então preciso verificar se o código antigo e o novo produzem resultados idênticos.

O SQL Server está consumindo 87,5% da RAM do meu servidor. Isso recentemente causou muitos gargalos de desempenho, como lentidão. Eu pesquisei esta questão. Uma solução comum que encontrei na internet é definir o limite máximo para o SQL Server. Isso foi feito e muitas melhorias são obtidas. Eu quero saber porque se o valor máximo de memória não está definido porque o SQL Server continua consumindo os recursos

TL;DR

Como essa pergunta continua recebendo visualizações, vou resumi-la aqui para que os recém-chegados não tenham que sofrer com a história:

JOIN table t ON t.member = @value1 OR t.member = @value2 -- this is slow as hell
JOIN table t ON t.member = COALESCE(@value1, @value2)    -- this is blazing fast
-- Note that here if @value1 has a value, @value2 is NULL, and vice versa

Eu percebo que isso pode não ser um problema de todos, mas ao destacar a sensibilidade das cláusulas ON, isso pode ajudá-lo a olhar na direção certa. De qualquer forma, o texto original está aqui para futuros antropólogos:

Texto original

Considere a seguinte consulta simples (apenas 3 tabelas envolvidas)

    SELECT

        l.sku_id AS ProductId,
        l.is_primary AS IsPrimary,
        v1.category_name AS Category1,
        v2.category_name AS Category2,
        v3.category_name AS Category3,
        v4.category_name AS Category4,
        v5.category_name AS Category5

    FROM category c4
    JOIN category_voc v4 ON v4.category_id = c4.category_id and v4.language_code = 'en'

    JOIN category c3 ON c3.category_id = c4.parent_category_id
    JOIN category_voc v3 ON v3.category_id = c3.category_id and v3.language_code = 'en'

    JOIN category c2 ON c2.category_id = c3.category_id
    JOIN category_voc v2 ON v2.category_id = c2.category_id and v2.language_code = 'en'

    JOIN category c1 ON c1.category_id = c2.parent_category_id
    JOIN category_voc v1 ON v1.category_id = c1.category_id and v1.language_code = 'en'

    LEFT OUTER JOIN category c5 ON c5.parent_category_id = c4.category_id
    LEFT OUTER JOIN category_voc v5 ON v5.category_id = c5.category_id and v5.language_code = @lang

    JOIN category_link l on l.sku_id IN (SELECT value FROM #Ids) AND
    (
        l.category_id = c4.category_id OR
        l.category_id = c5.category_id
    )

    WHERE c4.[level] = 4 AND c4.version_id = 5

Esta é uma consulta bastante simples, a única parte confusa é a última junção de categoria, é assim porque a categoria de nível 5 pode ou não existir. No final da consulta, estou procurando informações de categoria por ID do produto (ID do SKU), e é aí que entra a tabela muito grande category_link. Finalmente, a tabela #Ids é apenas uma tabela temporária contendo 10.000 IDs.

Quando executado, recebo o seguinte plano de execução real:

Como você pode ver, quase 90% do tempo é gasto nos Loops Aninhados (Inner Join). Aqui estão informações extras sobre esses loops aninhados:

Observe que os nomes das tabelas não correspondem exatamente porque editei os nomes das tabelas de consulta para facilitar a leitura, mas é muito fácil corresponder (ads_alt_category = category). Existe alguma maneira de otimizar essa consulta? Observe também que em produção, a tabela temporária #Ids não existe, é um parâmetro com valor de tabela dos mesmos 10.000 IDs passados ​​para o procedimento armazenado.

Informação adicional:

• índices de categoria em category_id e parent_category_id
• índice category_voc em category_id, language_code
• índice category_link no sku_id, category_id

Editar (resolvido)

Conforme apontado pela resposta aceita, o problema era a cláusula OR no category_link JOIN. No entanto, o código sugerido na resposta aceita é muito lento, mais lento até que o código original. Uma solução muito mais rápida e também muito mais limpa é simplesmente substituir a condição JOIN atual pela seguinte:

JOIN category_link l on l.sku_id IN (SELECT value FROM @p1) AND l.category_id = COALESCE(c5.category_id, c4.category_id)

Este ajuste de minuto é a solução mais rápida, testada contra a junção dupla da resposta aceita e também testada contra o CROSS APPLY, conforme sugerido por valverij.

Aumentar a largura de uma coluna (nvarchar) necessariamente derrubará a tabela?

Em outras palavras, a largura pode ser alterada em um ambiente de produção com usuários ativos?

Achei que, se o tamanho estivesse aumentando (em vez de diminuir), isso não seria um problema.

Eu gostaria de limitar as linhas e colunas que retornam do

SHOW TABLE STATUS

comando no MySQL 5.1. Existe uma maneira de obter essa mesma informação por meio de uma SELECTinstrução para que eu possa manipular os resultados de maneira normal?

Existe uma maneira sistemática de forçar o PostgreSQL a carregar uma tabela específica na memória, ou pelo menos lê-la do disco para que seja armazenada em cache pelo sistema?

Eu tenho um modelo de banco de dados com uma tabela de usuários e uma tabela de funções. Quero controlar o acesso (direitos) a até 10 elementos diferentes. O acesso pode ser concedido a uma função ou a um único usuário. Abaixo está a definição da tabela de usuários, funções e itens:

CREATE TABLE users
(
  id serial NOT NULL PRIMARY KEY,
  username character varying UNIQUE,
  password character varying,
  first_name character varying,
  last_name character varying,
  ...
);

CREATE TABLE roles
(
  id serial NOT NULL PRIMARY KEY,
  name character varying NOT NULL,
  description character varying,
  ...
);

CREATE TABLE element_1
(
  id serial NOT NULL PRIMARY KEY,
  name character varying NOT NULL,
  description character varying,
  ...
);

...

Agora eu tenho duas maneiras diferentes de projetar os direitos. Uma tabela com uma coluna de tipo de direitos ou 10 tabelas de direitos - uma para cada elemento ao qual desejo controlar o acesso.

Quais são os prós e contras de uma tabela de direitos versus uma tabela de direitos por elemento? - ou é a maneira mais adequada de fazer isso?

Quero procurar uma string nos nomes das colunas presentes em um banco de dados.

Estou trabalhando em um projeto de manutenção e alguns dos bancos de dados com os quais lido têm mais de 150 tabelas, então estou procurando uma maneira rápida de fazer isso.

O que você recomenda?