Persistir ou não colunas calculadas no sql server

realizando testes de insert para a tabela com colunas calculadas e não persistidas tivemos os resultados, criando a tabela com 3 colunas calculadas porém não persistidas

CREATE TABLE [dbo].[Viagem_Fuso](

[Viagem] [int] NULL,

[Entrega] [varchar](30) NULL,

[Data] [datetime] NULL,

[Fuso] [smallint] NULL,

[Data_UTC]  AS (dateadd(minute, -((1)*[Fuso]),[Data])),

[Data_UTC-3]  AS (dateadd(minute, -((1)*[Fuso]+(180)),[Data])),

[Data_UTC-4]  AS (dateadd(minute, -((1)*[Fuso]+(240)),[Data]))

) ON [PRIMARY]



GO

 

utilizando o script abaixo para inserir 10 mil registros na tabela

declare @data datetime2 = getdate();

print @data;

insert into viagem_fuso values

(1,'teste', @data, -120)

go 10000

declare @data datetime2 = getdate();

print @data;

Tempo de insert  foi de 30 segundos para inserir 10 mil registros
E o tempo de leitura total foi de 196 milesegundos.

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 0 ms.

Table ‘Viagem_Fuso’. Scan count 1, logical reads 60, physical reads 0, read-ahead reads 76, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 16 ms,  elapsed time = 196 ms.

 

Os Resultados para a mesma tabela com as colunas calculadas e campos persistidos fisicamente;

 

CREATE TABLE [dbo].[Viagem_Fuso2](

[Viagem] [int] NULL,

[Entrega] [varchar](30) NULL,

[Data] [datetime] NULL,

[Fuso] [smallint] NULL,

[Data_UTC]  AS (dateadd(minute, -((1)*[Fuso]),[Data])) persisted ,

[Data_UTC-3]  AS (dateadd(minute, -((1)*[Fuso]+(180)),[Data])) persisted ,

[Data_UTC-4]  AS (dateadd(minute, -((1)*[Fuso]+(240)),[Data])) persisted

) ON [PRIMARY]

Neste novo cenário o tempo de insert foi 29seg

E o tempo de leitura foi de apenas 6milesegundos:

 

set statistics io on;
set statistics time on;

select * from viagem_fuso2

Table ‘Viagem_Fuso2’. Scan count 1, logical reads 91, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

SQL Server Execution Times:

CPU time = 16 ms,  elapsed time = 6 ms.

 

Conclusão, colunas persistidas consomem um pouco mais de storage, memória(buffer cache), mas apresenta um ganho significativo na redução da utilização de processador no momento da leitura, e no momento do insert o impacto não é significativo.

Lidando com o erro “Query processor could not produce a query plan”

Atendendo a uma demanda de cliente, onde no cenário precisamos realizar uma sumarização de dados das últimas operações de clientes consolidando tudo dentro de um determinado período.

Ocorria que a consulta iria percorrer a maior tabela do cliente, a tabela chega a representar mais de 80% do tamanho do banco de dados do cliente, portanto era muito importante que nossa consulta, trabalhasse de maneira obrigatória com um índice filtrado, pois nossa consulta desejava apenas um range de informação da tabela, e foi criado um índice apenas para aquele período, para justamente não corrermos o risco de um plano de execução mau elaborado realizando um fullscan no database e fazendo com que não apenas a consulta demorasse muito como também iria comprometer todo o hardware da produção do cliente.

Neste exemplo, a big table é a tabela  Vendas (exemplo hipotético)

Após montar um script (exemplo hipotético simples), abaixo, o sql server resultou em erro:

declare @data_venda datetime = '2017-05-01';

with clientes as (
       select cod_cliente from clientes
       where 
       ultima_acao > @data_venda
       )
,ultima_venda_cliente  as (select  distinct v.cod_cliente, max(v.data_venda) over( partition by v.cod_cliente order by v.data_venda desc)
              from vendas v with(nolock, index(idx_filtrado_vendas_cliente_data))
              join clientes vp  on vp.cod_cliente = c.cod_cliente
              where
              v.data_venda >= @data_venda
              )
select * from ultima_venda_cliente
Msg 8622, Level 16, State 1, Line 6
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

Para quem não é persistente, acreditaria pela mensagem de erro que não é possível forçar nesta consulta a utilização de um índice filtrado, pois a mensagem de erro manda submeter a consulta novamente sem hint e afirmando que a utilização do hint é a causa do problema.

A solução é tão simples que quando realizei, quase não acreditei, e foi puramente por acaso que descobri, porque em uma rápida pesquisa na net nada encontrei documentado a respeito.

Basta não trabalhar com parâmetros dentro da CTE onde forço a utilização do índices e está tudo resolvido.

declare @data_venda datetime = '2017-05-01';

with clientes as (
       select cod_cliente from clientes
       where 
       ultima_acao > @data_venda
       )
,ultima_venda_cliente  as (select  distinct v.cod_cliente, max(v.data_venda) over( partition by v.cod_cliente order by v.data_venda desc)
              from vendas v with(nolock, index(idx_filtrado_vendas_cliente_data))
              join clientes vp  on vp.cod_cliente = c.cod_cliente
              where
              v.data_venda >=  '2017-05-01'-- aqui não pode ter parametros @data_venda
              )
select * from ultima_venda_cliente
Translate »