All perguntas(dba)

Tenho três tabelas: Produtos, Produtos estocados, Produtos vendidos.

Produtos e 'produtos estocados' são tabelas diferentes porque existem lotes diferentes do mesmo produto estocados com números de série diferentes. aqui estão as linhas importantes nessas tabelas:

Produtos:

Produtos em estoque:

Produtos vendidos:

BatchSerialNumber em SoldProducts faz referência à mesma coluna em StockedProducts.

Meu problema é quando acaba um lote Estocado, quero deletar o registro dele, mantendo todos os registros de sua venda.

Como eu gerenciaria isso com uma chave estrangeira?

Acho que esse é um tipo de tarefa bastante padrão, mas não vi nada que abranja o desempenho em detalhes, embora a otimização de índice para data e hora comparando o dia da semana e a hora esteja bem próxima. A partir dos meus sensores de temperatura, gostaria de calcular a temperatura média em cada mês do ano.

Estou usando o Postgres 15

A tabela fica assim:

CREATE TABLE public.hygrometer (
    device_id bpchar(2) NOT NULL,
    temperature numeric(3, 1) NOT NULL,
    "timestamp" timestamp NOT NULL
);
CREATE INDEX time_id ON public.hygrometer (device_id bpchar_ops);

Esta foi a primeira vez que usei funções de janela e esta é minha consulta:

select distinct ON (month) 
date_part('month', "timestamp") as month, 
avg(temperature) over (PARTITION by date_part('month', "timestamp") ) as avg_temp
from hygrometer
order by month

Uma dúvida que tenho é se é possível evitar digitar date_part('month', "timestamp")repetidamente na consulta. Suspeito que isso ocorra porque o escopo da expressão no select é diferente daquele na função de janelamento.

Inicialmente, criei dados com granularidade de 7 minutos (75.000 linhas), depois 1 minuto (525.600 linhas) e depois 1 segundo (30 milhões).

Eu criei o seguinte índice parcial.

CREATE INDEX month_idx ON public.hygrometer USING btree (date_part('month'::text, "timestamp"));

Com granularidade de 7 minutos, adicionar um índice é insignificante, com 1 minuto torna-se perceptível, embora o uso DISTINCT ONdê o maior impulso.

No entanto, fiquei surpreso que o desempenho com o maior conjunto de dados é um pouco mais rápido sem um índice, porque o planejador começa a trabalhar em paralelo. Eu li que isso deve ser possível com um índice, mas não consegui persuadir o servidor a fazê-lo, mesmo diminuindo o parallel_cost para quase 0.

Este é o plano de consulta para a tabela grande com um índice:

 Unique  (cost=0.56..1725911.36 rows=12 width=40)
   Output: (date_part('month'::text, "timestamp")), (avg(temperature) OVER (?))
   ->  WindowAgg  (cost=0.56..1647069.04 rows=31536928 width=40)
         Output: (date_part('month'::text, "timestamp")), avg(temperature) OVER (?)
         ->  Index Scan using month_idx on public.hygrometer  (cost=0.56..1095172.80 rows=31536928 width=14)
               Output: date_part('month'::text, "timestamp"), temperature

E este é o planner com o índice removido:

 Unique  (cost=2364029.66..6667763.19 rows=31536928 width=40)
   Output: (date_part('month'::text, "timestamp")), (avg(temperature) OVER (?))
   ->  WindowAgg  (cost=2364029.66..6588920.87 rows=31536928 width=40)
         Output: (date_part('month'::text, "timestamp")), avg(temperature) OVER (?)
         ->  Gather Merge  (cost=2364029.66..6037024.63 rows=31536928 width=14)
               Output: (date_part('month'::text, "timestamp")), temperature
               Workers Planned: 2
               ->  Sort  (cost=2363979.63..2396830.60 rows=13140387 width=14)
                     Output: (date_part('month'::text, "timestamp")), temperature
                     Sort Key: (date_part('month'::text, hygrometer."timestamp"))
                     ->  Parallel Seq Scan on public.hygrometer  (cost=0.00..361151.83 rows=13140387 width=14)
                           Output: date_part('month'::text, "timestamp"), temperature

Eu esperaria que uma varredura de índice paralela fosse possível aqui. Eu me pergunto se um tipo diferente de índice (hash?) Pode fazer mais sentido. O que mais é possível? Presumivelmente, uma visão materializada usando contendo o mês. Obrigado por seus comentários e sugestões.

ATUALIZAR

Parece que um índice de hash é a estratégia errada e será simplesmente ignorado.

Como aponta @jjanes: não há necessidade de usar uma função de janela aqui, pois isso é possível com um agregado simples.

Esta é a consulta:

select  
date_part('month', "timestamp") as month, 
avg(temperature)
from hygrometer
group by month
order by month

E este é o plano:

 Finalize GroupAggregate  (cost=427831.20..427834.36 rows=12 width=40)
   Output: (date_part('month'::text, "timestamp")), avg(temperature)
   Group Key: (date_part('month'::text, hygrometer."timestamp"))
   ->  Gather Merge  (cost=427831.20..427834.00 rows=24 width=40)
         Output: (date_part('month'::text, "timestamp")), (PARTIAL avg(temperature))
         Workers Planned: 2
         ->  Sort  (cost=426831.18..426831.21 rows=12 width=40)
               Output: (date_part('month'::text, "timestamp")), (PARTIAL avg(temperature))
               Sort Key: (date_part('month'::text, hygrometer."timestamp"))
               ->  Partial HashAggregate  (cost=426830.78..426830.96 rows=12 width=40)
                     Output: (date_part('month'::text, "timestamp")), PARTIAL avg(temperature)
                     Group Key: date_part('month'::text, hygrometer."timestamp")
                     ->  Parallel Seq Scan on public.hygrometer  (cost=0.00..361134.27 rows=13139302 width=14)
                           Output: date_part('month'::text, "timestamp"), temperature

O uso do índice não é imediatamente aparente, mas removê-lo é mais lento e o plano é diferente:

 Finalize GroupAggregate  (cost=2364783.98..6363725.60 rows=31534324 width=40)
   Output: (date_part('month'::text, "timestamp")), avg(temperature)
   Group Key: (date_part('month'::text, hygrometer."timestamp"))
   ->  Gather Merge  (cost=2364783.98..5693621.21 rows=26278604 width=40)
         Output: (date_part('month'::text, "timestamp")), (PARTIAL avg(temperature))
         Workers Planned: 2
         ->  Partial GroupAggregate  (cost=2363783.96..2659418.25 rows=13139302 width=40)
               Output: (date_part('month'::text, "timestamp")), PARTIAL avg(temperature)
               Group Key: (date_part('month'::text, hygrometer."timestamp"))
               ->  Sort  (cost=2363783.96..2396632.21 rows=13139302 width=14)
                     Output: (date_part('month'::text, "timestamp")), temperature
                     Sort Key: (date_part('month'::text, hygrometer."timestamp"))
                     ->  Parallel Seq Scan on public.hygrometer  (cost=0.00..361134.27 rows=13139302 width=14)
                           Output: date_part('month'::text, "timestamp"), temperature

Você pode vê-lo usado para reduzir o tamanho da amostra, ao agrupar e classificar. Uma melhoria de 3 a 4 vezes em tempo real e o índice é muito rápido de construir. Com cerca de um terço do tamanho da mesa, isso é razoável para esse tipo de melhoria.

Alterar o parâmetro sql92_security requer que o banco de dados seja reciclado. Isso está claro. No entanto, estou me perguntando se uma reciclagem contínua é suficiente? ou esse parâmetro requer uma parada/início completo do banco de dados de todas as instâncias juntas?

(Rolling recycle apenas reciclando cada instância no RAC 1 por vez para minimizar a interrupção ..)

Estou usando um grande número de TEMP TABLESe quero ter certeza de que não estou vazando armazenamento.

Eu crio e uso a tabela em uma transação com CREATE TEMP TABLE mytable ... ON COMMIT DROP.

Estou tendo problemas para entender a documentação do CREATE TEMPORARY TABLE. Ele afirma:

O daemon autovacuum não pode acessar e, portanto, não pode aspirar ou analisar tabelas temporárias. Por esse motivo, as operações apropriadas de vácuo e análise devem ser executadas por meio de comandos SQL de sessão.

Vou ON COMMIT DROPrecuperar espaço ou preciso realmente correr VACUUM mytable?

Se eu precisar executar VACUUM, não sei como, pois a tabela temporária não está disponível após o término da transação.

Eu tenho um campo de texto em um banco de dados PostgreSQL no qual os dados XML são armazenados. Agora queria saber se faz diferença armazenar o XML na versão minificada ou bonita.

De acordo com http://bytesizematters.com , o tamanho do texto em si é diferente:

Agora, estou me perguntando se isso refletirá no tamanho e no desempenho do banco de dados. Talvez o PostgreSQL já esteja otimizando tanto os campos de texto que isso não importa.

Inesperadamente, não encontrei nada sobre esse assunto na internet. Portanto, se você tiver alguns links sobre isso, ficaria muito feliz em ler e aprender mais sobre otimização de banco de dados.

Isso parece ser uma variação de um problema comum em que uma pequena alteração na cláusula LIMIT em uma consulta altera o plano de consulta para um que tenha desempenho muito inferior. Neste caso, tenho duas tabelas em um banco de dados PostgreSQL 13:

1. assetsque tem ~140 linhas;
2. asset_dataque tem aproximadamente 141 milhões de linhas.

Minha consulta é a seguinte:

SELECT
    a.dp as dp,
    a.at as at,
    a.dt as dt,
    a.it as it,
    a.ai as ai,
    ad.idt as idt,
    ad.data as data
FROM assets a
JOIN asset_data ad ON a.id = ad.asset_id_fk
WHERE 
    a.dp = 'kr' 
    AND a.at = 'fr' 
    AND a.dt = 'oh' 
    AND a.it = '1m' 
    AND a.ai = 'st'
ORDER BY idt desc
LIMIT 8000

Existe um índice em idt.

Quando o limite chega, 8000ele simplesmente usa o idtíndice para fazer a classificação; quando o limite 9000ele realiza a ordenação após o join.

O desempenho líquido vai de 3 segundos a quase 12 minutos.

Depois de ler alguns desses tipos de perguntas, tentei um VACUUM ANALYZEe isso mudou o plano de consulta, mas não de forma alguma que importasse.

Atualizações:

1. Também tentei definir as estatísticas para as colunas idte , mas não funcionou e não é óbvio para mim que deveria ter funcionado.asset_id_fk1000

2. idt não é único por si só

3. idt + asset_id_fk é único e tem uma restrição correspondente

4. Apenas uma linha é retornada da tabela de ativos

5. Existe um índice na combinação de asset_id_fk e idt

6. Existe um índice apenas no idt

Algum conselho sobre a maneira apropriada de corrigir isso?

Instrução CREATE TABLE:

CREATE TABLE IF NOT EXISTS public.asset_data
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    dp character varying(40) COLLATE pg_catalog."default",
    at character varying(40) COLLATE pg_catalog."default",
    it character varying(10) COLLATE pg_catalog."default",
    ai character varying(40) COLLATE pg_catalog."default",
    idt timestamp without time zone NOT NULL,
    data jsonb NOT NULL,
    inserted timestamp without time zone DEFAULT now(),
    updated timestamp without time zone DEFAULT now(),
    dt character varying(20) COLLATE pg_catalog."default",
    asset_id_fk integer NOT NULL DEFAULT 1,
    CONSTRAINT asset_data_pkey PRIMARY KEY (id),
    CONSTRAINT asset_data_1_idx UNIQUE (dp, at, it, idt, dt, ai),
    CONSTRAINT idx_asset_data_2_unique UNIQUE (asset_id_fk, idt)
)

CREATE INDEX IF NOT EXISTS asset_data_it_aif_idx
    ON public.asset_data (idt, asset_id_fk);

CREATE INDEX IF NOT EXISTS idx_asset_data_asset_fk
    ON public.asset_data (asset_id_fk);

CREATE INDEX IF NOT EXISTS idx_asset_data_asset_fk_idt_index
    ON public.asset_data (asset_id_fk, idt);

CREATE INDEX IF NOT EXISTS idx_asset_data_idt_idx
    ON public.asset_data (idt);

EXPLICAR ANALISAR com limite de 8000:

Limit  (cost=0.57..4170458.87 rows=8000 width=173) (actual time=0.160..3677.025 rows=8000 loops=1)
  Buffers: shared hit=61448 read=14657 dirtied=756
  ->  Nested Loop  (cost=0.57..507975375.06 rows=974426 width=173) (actual time=0.159..3675.290 rows=8000 loops=1)
        Join Filter: (a.id = ad.asset_id_fk)
        Rows Removed by Join Filter: 474053
        Buffers: shared hit=61448 read=14657 dirtied=756
        ->  Index Scan Backward using idx_asset_data_idt_idx on asset_data ad  (cost=0.57..505855992.43 rows=141291824 width=146) (actual time=0.051..3437.070 rows=482053 loops=1)
              Buffers: shared hit=61446 read=14657 dirtied=756
        ->  Materialize  (cost=0.00..5.27 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=482053)
              Buffers: shared hit=2
              ->  Seq Scan on assets a  (cost=0.00..5.26 rows=1 width=35) (actual time=0.052..0.067 rows=1 loops=1)
                    Filter: (((dp)::text = 'kr'::text) AND ((at)::text = 'fr'::text) AND ((dt)::text = 'oh'::text) AND ((it)::text = '1m'::text) AND ((ai)::text = 'st'::text))
                    Rows Removed by Filter: 144
                    Buffers: shared hit=2
Settings: effective_cache_size = '1507160kB'
Planning:
  Buffers: shared hit=4
Planning Time: 0.445 ms
Execution Time: 3679.005 ms

EXPLICAR ANALISAR com limite de 9000:

Limit  (cost=4269756.79..4269779.29 rows=9000 width=173) (actual time=700091.606..700094.588 rows=9000 loops=1)
  Buffers: shared hit=133 read=1538340, temp read=74205 written=112013
  ->  Sort  (cost=4269756.79..4272192.85 rows=974426 width=173) (actual time=700091.604..700093.738 rows=9000 loops=1)
        Sort Key: ad.idt DESC
        Sort Method: external merge  Disk: 304680kB
        Buffers: shared hit=133 read=1538340, temp read=74205 written=112013
        ->  Nested Loop  (cost=0.57..4200885.77 rows=974426 width=173) (actual time=1.190..693283.441 rows=1687735 loops=1)
              Buffers: shared hit=133 read=1538340
              ->  Seq Scan on assets a  (cost=0.00..5.26 rows=1 width=35) (actual time=0.032..0.050 rows=1 loops=1)
                    Filter: (((dp)::text = 'kr'::text) AND ((at)::text = 'fr'::text) AND ((dt)::text = 'oh'::text) AND ((it)::text = '1m'::text) AND ((ai)::text = 'st'::text))
                    Rows Removed by Filter: 144
                    Buffers: shared hit=2
              ->  Index Scan using idx_asset_data_asset_fk on asset_data ad  (cost=0.57..4190011.91 rows=1086860 width=146) (actual time=1.151..691172.001 rows=1687735 loops=1)
                    Index Cond: (asset_id_fk = a.id)
                    Buffers: shared hit=131 read=1538340
Settings: effective_cache_size = '1507160kB'
Planning:
  Buffers: shared hit=4
Planning Time: 0.317 ms
Execution Time: 700245.659 ms

Atualização 2:

Depois de mudar para a seguinte consulta:

SELECT a.dp, a.at, a.dt, a.it, a.ai, ad.idt, ad.data
FROM  (
   SELECT a.id, a.dp, a.at, a.dt, a.it, a.ai
   FROM   assets a
   WHERE  a.dp = 'kr' 
   AND    a.at = 'fr' 
   AND    a.dt = 'oh' 
   AND    a.it = '1m' 
   AND    a.ai = 'st'
   LIMIT  1  -- make sure the planner understands
   ) a
JOIN   asset_data ad ON ad.asset_id_fk = a.id
ORDER  BY ad.idt DESC
LIMIT  8000;

A única diferença é que o plano de consulta alterna entre 9.000 e 10.000:

Limit  (cost=0.57..4206410.37 rows=9000 width=173) (actual time=0.139..432.265 rows=9000 loops=1)
  Buffers: shared hit=82159
  ->  Nested Loop  (cost=0.57..507975395.07 rows=1086860 width=173) (actual time=0.138..431.358 rows=9000 loops=1)
        Join Filter: (a.id = ad.asset_id_fk)
        Rows Removed by Join Filter: 533354
        Buffers: shared hit=82159
        ->  Index Scan Backward using idx_asset_data_idt_idx on asset_data ad  (cost=0.57..505856012.43 rows=141291824 width=146) (actual time=0.049..164.873 rows=542354 loops=1)
              Buffers: shared hit=82158
        ->  Materialize  (cost=0.00..5.28 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=542354)
              Buffers: shared hit=1
              ->  Subquery Scan on a  (cost=0.00..5.27 rows=1 width=35) (actual time=0.032..0.035 rows=1 loops=1)
                    Buffers: shared hit=1
                    ->  Limit  (cost=0.00..5.26 rows=1 width=35) (actual time=0.032..0.033 rows=1 loops=1)
                          Buffers: shared hit=1
                          ->  Seq Scan on assets a_1  (cost=0.00..5.26 rows=1 width=35) (actual time=0.031..0.032 rows=1 loops=1)
                                Filter: (((dp)::text = 'kr'::text) AND ((at)::text = 'fr'::text) AND ((dt)::text = 'oh'::text) AND ((it)::text = '1m'::text) AND ((ai)::text = 'st'::text))
                                Rows Removed by Filter: 96
                                Buffers: shared hit=1
Settings: effective_cache_size = '1507160kB'
Planning Time: 0.322 ms
Execution Time: 432.904 ms

Limit  (cost=4278529.50..4278554.50 rows=10000 width=173) (actual time=702389.569..702392.909 rows=10000 loops=1)
  Buffers: shared hit=350 read=1538221, temp read=74241 written=112019
  ->  Sort  (cost=4278529.50..4281246.65 rows=1086860 width=173) (actual time=702389.568..702392.118 rows=10000 loops=1)
        Sort Key: ad.idt DESC
        Sort Method: external merge  Disk: 304704kB
        Buffers: shared hit=350 read=1538221, temp read=74241 written=112019
        ->  Nested Loop  (cost=0.57..4200885.78 rows=1086860 width=173) (actual time=1.267..695545.975 rows=1687836 loops=1)
              Buffers: shared hit=350 read=1538221
              ->  Limit  (cost=0.00..5.26 rows=1 width=35) (actual time=0.071..0.074 rows=1 loops=1)
                    Buffers: shared hit=1
                    ->  Seq Scan on assets a  (cost=0.00..5.26 rows=1 width=35) (actual time=0.071..0.071 rows=1 loops=1)
                          Filter: (((dp)::text = 'kr'::text) AND ((at)::text = 'fr'::text) AND ((dt)::text = 'oh'::text) AND ((it)::text = '1m'::text) AND ((ai)::text = 'st'::text))
                          Rows Removed by Filter: 96
                          Buffers: shared hit=1
              ->  Index Scan using idx_asset_data_asset_fk on asset_data ad  (cost=0.57..4190011.91 rows=1086860 width=146) (actual time=1.190..693414.817 rows=1687836 loops=1)
                    Index Cond: (asset_id_fk = a.id)
                    Buffers: shared hit=349 read=1538221
Settings: effective_cache_size = '1507160kB'
Planning Time: 0.301 ms
Execution Time: 702526.728 ms

Exemplo de situação

Vamos imaginar a seguinte situação: Um usuário pode fazer pedidos . Os pedidos são compostos de itens . As remessas são enviadas para o usuário. As remessas são compostas de itens .

De forma a otimizar a cadeia de abastecimento, é permitido que artigos de encomendas diferentes sejam embalados num envio comum , desde que as encomendas estejam todas associadas ao mesmo utilizador

Tenho dificuldade em entender qual é a melhor maneira de modelar a situação para acomodar graciosamente a restrição que está em itálico no parágrafo anterior sem duplicar dados ou recorrer a verificações em todo o lugar.

minha primeira intuição

Com zero duplicação de dados, posso criar facilmente o seguinte esquema:

CREATE TABLE users(
  id serial primary key
)
CREATE TABLE orders(
  id serial primary key,
  user_id int not null references users
)
CREATE TABLE shipments(
  id serial primary key
)
CREATE TABLE items(
  id serial primary key,
  order_id int not null references orders,
  shipment_id int references shipments
)

Embora implemente as relações 1-N entre pedidos e itens e remessas e itens que desejo, isso não impede que o seguinte seja feito:

INSERT INTO orders(id,user_id) VALUES (1,1);
INSERT INTO orders(id,user_id) VALUES (2,2);
INSERT INTO shipments(id) VALUES (1)
INSERT INTO items(order_id,shipment_id) VALUES (1,1);
INSERT INTO items(order_id,shipment_id) VALUES(2,1);

O que resulta na situação da remessa nº 1 com dois itens associados a usuários diferentes (através de seus respectivos pedidos).

Encontrei duas soluções para meus problemas, ambas funcionam e nenhuma delas parece boa para mim:

1. Escreva uma função definida pelo usuário que retorne true para um determinado item_id e shipping_id se a adição do item não violar a regra de um usuário por remessa e adicione uma restrição de verificação chamando essa função. Parece uma espécie de ladeira escorregadia onde você perde a boa abordagem declarativa do SQL para entrar em algum tipo de inferno de callback imperativo, e eu instintivamente não gosto disso.
2. Duplique o user_id nas tabelas de itens e remessas e escreva duas chaves estrangeiras compostas na tabela de itens que faz referência aos pedidos, garantindo que ninguém possa violar a regra de um usuário por remessa. Agora que duplicamos os dados, também tenho que prestar atenção à regra implícita de um usuário por pedido até então:

ALTER TABLE items ADD user_id int not null references users;
ALTER TABLE shipments ADD user_id int not null references users;
ALTER TABLE orders ADD UNIQUE (id,user_id); -- For some reason postgres wants this ?
ALTER TABLE shipments ADD UNIQUE (id, user_id);
ALTER TABLE items ADD FOREIGN KEY (order_id,user_id) references orders(id,user_id);
ALTER TABLE items ADD FOREIGN KEY (shipment_id,user_id) references shipments(id,user_id);

Mantendo a pontuação

A solução 1 mantém um esquema limpo, mas apresenta complexidade processual. Também ouvi que as funções escalares definidas pelo usuário não são muito boas em termos de desempenho.

A solução 2 definitivamente parece mais robusta para mim, mas não gosto de ter duplicado (triplicado, para ser sincero) os dados de user_id e o requisito adicionado.

pergunta real

• Qual é a prática preferencial para fazer o que estou tentando fazer, que é garantir que um registro referenciado (usuários por meio de pedidos) seja exclusivo no subconjunto de linhas definido por outro registro referenciado (remessas) de outra tabela?
• Se não houver uma solução claramente melhor, quais são as outras soluções possíveis?
• Quais são os outros prós e contras das duas abordagens que descrevi e que certamente deixei passar?
• O problema que estou enfrentando tem um nome ou jargão que agora posso usar?

Parece uma situação simples e acho que os usuários mais experientes de RDBMSs acharão isso trivial ... problemas vêm essencialmente da minha incapacidade de articulá-lo nos termos adequados ... Estou com vergonha de fazer uma pergunta tão estúpida que provavelmente é uma duplicata, mas meu google-fu falhou comigo desta vez!

Estou trabalhando com SQL Server e tenho duas tabelas, table1e table2. Ambas as tabelas têm uma DATETIMEcoluna indicada como dt. Preciso unir essas tabelas com base não apenas em certas condições, mas também para garantir que as linhas correspondentes tenham a mesma data (a hora não é considerada aqui) da dtcoluna.

Aqui está a consulta que estou usando atualmente:

select *
From table1 a inner join table2 b
    on a.id = b.a_id
    and convert(date, a.dt) = convert(date, b.dt)

Essa consulta funciona para obter os resultados de que preciso, mas estou preocupado com seu desempenho, especialmente à medida que as tabelas aumentam de tamanho.

Eu estou querendo saber se existem maneiras mais eficazes para alcançar o mesmo resultado? Existem técnicas ou recursos do SQL Server que possam me ajudar a otimizar essa consulta, principalmente a parte de comparação de datas?

Qualquer conselho ou sugestão seria muito apreciada. Agradeço antecipadamente.

O código que tenho abaixo me fornece a saída básica que exijo de uma tabela aninhada em outra tabela, veja a captura de tela

No entanto, não consegui obter o resultado correto sem adicionar a linha de código

Set @BodyTxt =(select REPLACE(replace (@BodyTxt,'&lt;','<'),'&gt;','>'))

porque o código original estava sendo escapado para "<" e ">".

Existe uma maneira mais limpa ou melhor de fazer isso?

Observe que o uso do dB mail é apenas uma maneira rápida de ver a saída HTML (existe uma maneira melhor?). Se você usar meu código, precisará configurar o dB mail e alterar o perfil e o endereço de email

 /***********************************************/
 /*** set up test data                          */
 /***********************************************/

 DROP TABLE IF EXISTS dbo.Units
 DROP TABLE IF EXISTS dbo.Document
CREATE TABLE Units(
DocNum int,
DocRow int,
Serial varchar(255),
Element varchar(255),
ManDate datetime)

INSERT dbo.Units
Values (50009,0,'Serial1','Element1','2021-01-04 00:00:00'),
        (50009,0,'Serial2','Element2','2021-01-04 00:00:00'),
        (50009,1,'Serial4','Element2','2021-01-06 00:00:00'),
        (50006,0,'Serial3','Element2','2021-01-07 00:00:00')
CREATE TABLE Document(
DocNum int,
DocRow int,
Part varchar(255),
Qty int,
DocDate datetime)

INSERT dbo.Document
Values (50009,0,'PartA',2,'2023-07-04 00:00:00'),
        (50008,0,'PartC',1,'2023-06-28 00:00:00'),
        (50007,0,'PartB',4,'2023-06-24 00:00:00'),
        (50006,0,'PartA',1,'2023-01-04 00:00:00')
/******* End of Test Data set up       *************/

declare  @BodyTxt nvarchar(max)

Set @BodyTxt =(
SELECT  
    (SELECT 'Table I' FOR XML PATH(''),TYPE) AS 'caption',
    (SELECT 'Doc' AS th, 'Line' AS th,'Part' as th,'Qty' as th,'Unit Details' as th FOR XML raw('tr'),ELEMENTS, TYPE) AS 'thead',
    --(SELECT 'sum' AS th, 'twenty' AS th FOR XML raw('tr'),ELEMENTS, TYPE) AS 'tfoot',
    (SELECT DocNum AS td, DocRow AS td,Part AS td,Qty AS td, (Select (Select Serial as td,Element as td,convert (varchar ,ManDate, 6) as td from dbo.Units U Where U.DocNum=D.DocNum and U.DocRow=D.DocRow FOR XML RAW('tr'), ELEMENTS, TYPE ) AS 'tbody'
  FOR XML PATH(''), ROOT('table')) AS td FROM dbo.Document D FOR XML RAW('tr'), ELEMENTS, TYPE
    ) AS 'tbody'
  FOR XML PATH(''), ROOT('table'))
Set @BodyTxt =(select REPLACE(replace (@BodyTxt,'&lt;','<'),'&gt;','>'))


EXEC msdb.dbo.sp_send_dbmail  
                                @profile_name = 'Send via 365'  
                                ,@recipients   = '[email protected]'
                                
                                ,@subject      = 'Test table'
                                ,@body         =  @BodyTxt
                                ,@body_format = 'HTML';

O aplicativo está armazenando dados em cache de forma agressiva na memória e, para oferecer suporte à consistência (evitando a persistência de dados obsoletos), está fazendo algo como:

-- typical table structure:
create table t1 (
    id            varchar(16) primary key,
    version_stamp int4,
    ....
)

-- typical update statement
update t1 set
   version_stamp = version_stamp + 1,
   col1 = ?,
   col2 = ?,
...
where id = ? and version_stamp = ?

Se a atualização mencionada acima informar que nenhuma linha foi atualizada, isso significa que o aplicativo tentou persistir dados obsoletos e uma exceção foi lançada, e a ideia principal é prevenir ou, pelo menos, minimizar tais casos. Para isso, a aplicação realiza as seguintes consultas (por requisição, transação ou chamada de método):

select version_stamp from t1
  where id = ?

Se nenhuma linha foi retornada, isso significa que a linha foi excluída, se retornada version_stampfor diferente da version_stampmantida na memória, isso significa que estamos lidando com dados obsoletos.

A questão é: vale a pena definir chaves primárias como:

create unique index on t1(id) include(version_stamp)

Ou não nesse caso. O RPS típico para essas consultas é de cerca de 10k por segundo.