philomathic_life's questions

Gostaria de definir um tipo composto cujos campos são NOT NULL; enquanto, ao mesmo tempo, permite que o valor em si esteja NULLem uma coluna da tabela. Minha primeira tentativa foi definir um DOMAINno tipo composto com uma CHECKrestrição que assegurasse que os campos são NOT NULL; infelizmente, isso impede NULLque ele seja INSERTed na tabela:

BEGIN;
    CREATE TYPE foo AS (x int, y int);
    CREATE DOMAIN non_null_foo AS foo CHECK((VALUE).x IS NOT NULL AND (VALUE).y IS NOT NULL);
    CREATE TABLE bar(y non_null_foo);
    INSERT INTO bar VALUES (NULL);
ROLLBACK;

erros: ERROR: value for domain non_null_foo violates check constraint "non_null_foo_check".

Minha segunda tentativa foi permitir NULLem VALUE, DOMAINmas isso também não funciona, pois agora permite um valor onde todos os campos são NULL:

BEGIN;
    CREATE TYPE foo AS (x int, y int);
    CREATE DOMAIN non_null_foo AS foo CHECK(VALUE IS NULL OR ((VALUE).x IS NOT NULL AND (VALUE).y IS NOT NULL));
    CREATE TABLE bar(y non_null_foo);
    INSERT INTO bar VALUES ((NULL, NULL)); --succeeds
    INSERT INTO bar VALUES ((1, NULL)); --fails
ROLLBACK;

É como se o Postgresql não conseguisse distinguir entre NULLe um valor onde todos os campos são NULL. Há algo que eu esteja esquecendo?

Eu tenho uma tabela que pode ser criada e preenchida com o seguinte código:

CREATE TABLE dbo.Example(GroupKey int NOT NULL, RecordKey varchar(12) NOT NULL);
ALTER TABLE dbo.Example
    ADD CONSTRAINT iExample PRIMARY KEY CLUSTERED(GroupKey ASC, RecordKey ASC);
INSERT INTO dbo.Example(GroupKey, RecordKey)
VALUES (1, 'Archimedes'), (1, 'Newton'), (1, 'Euler'), (2, 'Euler'), (2, 'Gauss'),
       (3, 'Gauss'), (3, 'Poincaré'), (4, 'Ramanujan'), (5, 'Neumann'),
       (5, 'Grothendieck'), (6, 'Grothendieck'), (6, 'Tao');

Para todas as linhas que têm uma distância de colaboração finita com base em RecordKeyoutra linha, gostaria de atribuir um valor exclusivo - não me importa como ou qual tipo de dados é o valor exclusivo.

Um conjunto de resultados correto que atende ao que estou pedindo pode ser gerado com a seguinte consulta:

SELECT 1 AS SupergroupKey, GroupKey, RecordKey
FROM dbo.Example
WHERE GroupKey IN(1, 2, 3)
UNION ALL
SELECT 2 AS SupergroupKey, GroupKey, RecordKey
FROM dbo.Example
WHERE GroupKey = 4
UNION ALL
SELECT 3 AS SupergroupKey, GroupKey, RecordKey
FROM dbo.Example
WHERE GroupKey IN(5, 6)
ORDER BY SupergroupKey ASC, GroupKey ASC, RecordKey ASC;

Para ajudar melhor no que estou perguntando, explicarei por que GroupKeyos s 1–3 têm o mesmo SupergroupKey:

• GroupKey1 contém o RecordKeyEuler que por sua vez está contido em GroupKey2; assim GroupKeys 1 e 2 devem ter o mesmo SupergroupKey.
• Como Gauss está contido em GroupKeys 2 e 3, eles também devem ter o mesmo SupergroupKey. Isso faz com que GroupKeys 1–3 tenha o mesmo SupergroupKey.
• Como GroupKeys 1–3 não compartilham nenhum RecordKeys com os s restantes GroupKey, eles são os únicos com SupergroupKeyvalor 1.

Devo acrescentar que a solução precisa ser genérica. A tabela acima e o conjunto de resultados foram apenas um exemplo.

Termo aditivo

Eu removi o requisito de que a solução não fosse iterativa. Embora eu prefira essa solução, acredito que seja uma restrição irracional. Infelizmente, não consigo usar nenhuma solução baseada em CLR; mas se você quiser incluir essa solução, fique à vontade. Eu provavelmente não vou aceitá-lo como uma resposta embora.

O número de linhas na minha tabela real é tão grande quanto 5 milhões, mas há dias em que o número de linhas será "apenas" em torno de dez mil. Em média, há 8 RecordKeys por GroupKeye 4 GroupKeys por RecordKey. Imagino que uma solução terá uma complexidade de tempo exponencial, mas mesmo assim estou interessado em uma solução.

Há uma infinidade de informações disponíveis detalhando por que um IDENTITYcampo deve ser usado como chave primária e índice clusterizado de uma tabela para a maioria das situações; ainda estou tendo dificuldade em decidir se minha situação particular é uma exceção.

Eu tenho uma tabela com cerca de 250 colunas - não quero iniciar um debate de normalização - e cerca de 100 milhões de linhas. A tabela é compactada por página. Em dias de pico, cerca de 1 milhão de linhas são inseridas na tabela sequencialmente com 0 contenção de outras conexões. A combinação de um char(2) NOT NULLcampo e um int NOT NULLcampo é garantidamente única e nunca muda. Existem apenas cerca de 10 valores exclusivos para o char(2)campo.

Meu argumento para uma IDENTITYcoluna sendo usada como o índice clusterizado é baseado apenas no desempenho. Como as inserções ocorrerão sequencialmente, não devo me preocupar com a contenção de travas; assim as inserções devem ser mais rápidas, pois não haverá necessidade de pesquisar o índice. Por outro lado, uma chave composta de 6 bytes é bastante pequena; e vou acabar tornando-a a chave primária de qualquer maneira (com o char(2)campo primeiro). Além disso, haverá algumas outras tabelas que são carregadas em massa com base nas novas linhas nesta tabela (provavelmente por meio de um índice filtrado). Se eu usar uma IDENTITYcoluna, provavelmente usarei essa coluna como o índice clusterizado e a chave primária dessas outras tabelas (sem a IDENTITYpropriedade) e não trarei a chave composta "natural".

Fazer um índice não clusterizado na chave composta anula o aumento de velocidade esperado de usar uma IDENTITYcoluna como o índice clusterizado, pois esse índice terá que ser pesquisado quando as inserções forem concluídas?

Editar

Com base nos comentários, alterei o título e estou fazendo uma pergunta corrigida cuja resposta pode ser usada para responder à pergunta original acima. Como a manutenção de um índice clusterizado exclusivo se compara à manutenção de um índice não clusterizado exclusivo em relação às inserções? Um índice clusterizado não sequencial sofre de forma semelhante a um índice não clusterizado não sequencial?

Gostaria de saber mais sobre o que acontece nos bastidores com uma transação de confirmação automática ao executar uma consulta entre servidores em um servidor vinculado.

Eu ingenuamente penso que, ao executar uma transação de confirmação automática, o compilador/SQL Server/outra coisa apenas precede todas as instruções com a BEGIN TRANSACTIONe anexa todas as instruções com a, COMMIT TRANSACTIONpois tudo está tecnicamente incluído em uma transação ( É uma prática ruim sempre criar uma transação ? ). Tenho certeza de que isso está incorreto e a fonte da minha confusão sobre por que executar um servidor cruzado UPDATEsem declarar explicitamente BEGIN TRANSACTIONfunciona, mas declarando explicitamente que não. De acordo com a Microsoft ( https://learn.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-2017 ), um explícito BEGIN TRANSACTIONsobre umUPDATEa consulta que faz referência a uma tabela em um servidor vinculado é escalada para uma transação distribuída; e como as transações distribuídas não estão configuradas no servidor vinculado, recebo um erro. Como a configuração de confirmação automática evita isso? Como não é escalado para uma transação distribuída? A configuração de confirmação automática envia os dados para o servidor vinculado, mas não "escuta" uma resposta do servidor vinculado por meio do Microsoft Distributed Transaction Coordinator (MS DTC); assim, se ocorrer um erro, ele falha "silenciosamente"?

A confirmação automática não escala para uma transação distribuída:

UPDATE l
SET l.RecordKey = s.RecordKey
FROM LinkedServer.ExampleDatabase.dbo.ExampleTable AS l
INNER JOIN ServerWithActiveConnection.ExampleDatabase.dbo.ExampleTable AS s
    ON l.Value1 = s.Value1;

A transação explícita escala para uma transação distribuída (e erros no meu caso):

BEGIN TRANSACTION
UPDATE l
SET l.RecordKey = s.RecordKey
FROM LinkedServer.ExampleDatabase.dbo.ExampleTable AS l
INNER JOIN ServerWithActiveConnection.ExampleDatabase.dbo.ExampleTable AS s
    ON l.Value1 = s.Value1;
COMMIT TRANSACTION;

Devo acrescentar que as instruções foram executadas no SQL Server Management Studio.

Eu tenho uma tabela muito pequena com 12 linhas que podem ser criadas com a seguinte instrução:

CREATE TABLE dbo.SmallTable(ScoreMonth tinyint NOT NULL PRIMARY KEY,
                            ScoreGoal float NOT NULL
                           );

Eu tenho outra tabela com ≈100M de linhas que pode ser criada com as seguintes declarações:

CREATE TABLE dbo.SlowCrossApply(RecordKey nvarchar(12) NOT NULL,
                                Score1 decimal(3, 2) NOT NULL,
                                Score2 decimal(3, 2) NOT NULL,
                                Score3 decimal(3, 2) NOT NULL,
                                Score4 decimal(3, 2) NOT NULL,
                                Score5 decimal(3, 2) NOT NULL,
                                Score6 decimal(3, 2) NOT NULL,
                                FromToday bit NOT NULL
                               );

ALTER TABLE dbo.SlowCrossApply ADD CONSTRAINT i01PK PRIMARY KEY CLUSTERED(RecordKey ASC)
    WITH(FILLFACTOR = 90, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
         DATA_COMPRESSION = PAGE
        );

CREATE NONCLUSTERED INDEX i02TodayRecords ON dbo.SlowCrossApply(FromToday)
    INCLUDE (Score1, Score2, Score3, Score4, Score5, Score6)
    WHERE FromToday = 1
    WITH(FILLFACTOR = 100, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
         DATA_COMPRESSION = PAGE
        );

i02TodayRecordstem ≈1M linhas nele. Quando executo a seguinte consulta - tive dificuldade em formatá-la para parecer limpo e evitar uma barra de rolagem horizontal - leva mais de 5 minutos para terminar:

SELECT b.RecordKey,
       COALESCE(NULLIF(ROUND(((0.95 * (ROW_NUMBER() OVER(PARTITION BY a.Prefix
                                                         ORDER BY b.Score6 ASC
                                                        ) - 1
                                      )
                              )
                              / COALESCE(NULLIF(COUNT(*) OVER(PARTITION BY a.Prefix) - 1, 0
                                               ), 1
                                        )
                             ) + 0.005, 2
                            ), 0.96
                      ), 0.95
               ) AS NewScore
FROM (SELECT LEFT(s.RecordKey, 2) AS Prefix,
             CAST(ROUND(sm.ScoreGoal * COUNT(*), 0) AS int) AS Quant
      FROM dbo.SlowCrossApply AS s
      CROSS JOIN dbo.SmallTable AS sm
      WHERE s.FromToday = 1 AND sm.ScoreMonth = MONTH(GETDATE())
      GROUP BY LEFT(s.RecordKey, 2), sm.ScoreGoal
     ) AS a
CROSS APPLY (SELECT TOP(a.Quant) s2.RecordKey, s2.Score6
             FROM dbo.SlowCrossApply AS s2
             WHERE s2.FromToday = 1 AND s2.Score6 > 0 AND LEFT(s2.RecordKey, 2) = a.Prefix
             ORDER BY s2.Score6 DESC
            ) AS b;

A subconsulta externa retorna apenas 10 linhas; e se eu fornecer uma dica para usar i02TodayRecordsou colocar os resultados da subconsulta externa em uma variável de tabela, leva menos de 1 segundo. O resultado final retorna pouco mais de 8.000 linhas.

O plano de execução mostra que 64% do custo é devido a um spool de índice ansioso no índice clusterizado na Cross Applyporção.

Eu sei que a dica de índice funciona (pelo menos por enquanto), mas espero evitar usar uma. Idealmente, eu também não seguiria a rota da variável de tabela. Existe algo que eu possa fazer para que o otimizador de consulta "saiba" utilizar i02TodayRecords? Percebo que há muito mais informações que provavelmente são importantes e farei o possível para fornecer essas informações, se solicitadas.

Algumas informações potencialmente úteis: os índices têm menos de 1% de fragmentação. As estatísticas de ambos os índices foram atualizadas por meio de um FULLSCAN, e o banco de dados está configurado para ter parametrização simples e sniffing de parâmetros — infelizmente, não posso alterar essas configurações. Em relação a este último, o otimizador de consulta não substituiu nenhum valor por parâmetros, ao contrário de outras consultas simples que executei, nas quais fui forçado a usar uma dica para utilizar um índice filtrado específico.

Eu tenho duas tabelas (juntamente com um índice não clusterizado) que podem ser criadas com os comandos abaixo:

CREATE TABLE GroupTable
(
  GroupKey int NOT NULL PRIMARY KEY, 
  RecordCount int NOT NULL,
  GroupScore float NOT NULL
);

CREATE TABLE RecordTable
(
  RecordKey varchar(10) NOT NULL, 
  GroupKey int NOT NULL,
  PRIMARY KEY(RecordKey, GroupKey)
);

CREATE UNIQUE INDEX ixGroupRecord ON RecordTable(GroupKey, RecordKey);

Embora tecnicamente minhas tabelas sejam um pouco diferentes e eu esteja participando de algumas outras, esse é um proxy adequado para minha situação.

• Eu gostaria de selecionar todos os GroupKeysque não são subconjuntos de outro GroupKey.
• Para um determinado superconjunto, gostaria de obter o máximo GroupScorede todos os seus subconjuntos (incluindo ele mesmo).
• No caso em que a GroupKeycontém exatamente o mesmo RecordKeysque outro GroupKey(s), apenas um deles GroupKeysé capturado (não importa qual).
• Qualquer GroupKeyum que tenha exatamente o mesmo RecordKeysque outro GroupKey(s)também terá o mesmo GroupScore.
• Não relacionados GroupKeyspodem ter a mesma pontuação também.

O seguinte é um exemplo para ilustrar o que estou perguntando:

              GroupTable                          RecordTable

GroupKey    RecordCount   GroupScore         RecordKey    GroupKey
------------------------------------         ---------------------
  1              3            6.2                A          1
  29             2            9.8                A          29
  95             3            6.2                A          95
  192            4            7.1                A          192
                                                 B          1
                                                 B          29
                                                 B          95
                                                 B          192
                                                 C          1
                                                 C          95
                                                 D          192
                                                 E          192

Eu gostaria que a saída fosse a seguinte:

GroupKey    RecordCount    GroupScore
-------------------------------------
  1              3             9.8
  192            4             9.8

GroupTabletem cerca de 75 milhões de linhas e RecordTablecerca de 115 milhões de linhas; no entanto, após as junções e o WHEREpredicado, tende a haver cerca de 20 mil linhas em um determinado dia.

Peço desculpas se esta pergunta é trivial, mas por algum motivo estou realmente lutando com isso.

Background : Uma vez por dia são criadas várias tabelas temporárias que calculam uma variedade de valores agregados diferentes. Todos eles contêm o mesmo identificador exclusivo (o campo no qual eu faria uma PRIMARY KEY). Cada tabela tem cerca de 100 mil linhas com apenas 2 a 8 colunas. Vários JOINs e UNION ALLs são então feitos nas tabelas temporárias. Todos os campos em todas as tabelas temporárias são usados. Além disso, na maioria das vezes, todas as linhas também são usadas - na pior das hipóteses, metade das linhas é usada em algum lugar e a outra metade é usada em outro lugar. Quando a consulta é concluída, os resultados são salvos no disco para que as pessoas possam acessar os dados pelo resto do dia.

Pergunta : Qual das seguintes abordagens deve ser a melhor e mais rápida:

• Não possuem índices nas tabelas temporárias.

• Ter um índice clusterizado em todas as tabelas temporárias (por meio da declaração PRIMARY KEY no identificador exclusivo).

• Tenha um índice não clusterizado no identificador exclusivo e inclua as colunas restantes em todas as tabelas temporárias.

• Os dois últimos marcadores juntos.

Pensamentos : Ao executar as quatro opções simultaneamente, cada uma tinha um custo de consulta de 25% (relativo ao lote); no entanto, quando o primeiro marcador é executado, o plano de execução (na consulta SELECT) indica que devo criar um índice não clusterizado no identificador exclusivo, incluindo as colunas restantes para cada uma das tabelas temporárias.

Estou um pouco perplexo com isso. Se eu efetivamente usar todos os campos e linhas em todas as tabelas temporárias, por que isso sugeriria isso? Um heap ou um índice clusterizado não seria melhor?

Para mim, um heap minimizaria a sobrecarga na criação de um índice e na classificação dos dados; e como eu basicamente preciso de cada linha, não há mal nenhum em fazer uma varredura de tabela.

Um índice clusterizado, por outro lado, deve melhorar a tabela temporária que é criada por meio de um JOIN em duas das outras tabelas temporárias, bem como melhorar a consulta SELECT final que depende de JOINs e UNION ALLs.

Nota relacionada : Em uma tabela grande com 400 colunas e 70 milhões de linhas - não me pergunte por que temos uma quantidade absurda de colunas - uma consulta que pegou apenas a PRIMARY KEY foi ≈50 vezes mais rápida ao utilizar um índice não clusterizado não relevante sobre o índice agrupado.

Se alguém tiver alguma dica, agradeço.