Há uns dias, em conversa com colegas sobre o meu post em que recomendava o uso de constraints, surgiu uma pergunta pertinente:
“Então não bastava termos usado uns índices e o assunto ficaria resolvido?”
A minha resposta foi “Não. Mas não te fies na minha resposta, testa e vê com os teus próprios olhos, ou melhor ainda, com o teu management studio.”
Vamos lá revisitar o código do outro post com algumas melhorias para tirarmos as dúvidas.
Começamos pelas tais tabelas que só interessam para demos e a vista a juntar tudo
CREATE TABLE [dbo].[Dates2015](
id bigint primary key identity(1,1) not null
, dt_ref date not null
, TextCol nvarchar(50) not null
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Dates2016](
id bigint primary key identity(1,1) not null
, dt_ref date not null
, TextCol nvarchar(50) not null
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Dates2017](
id bigint primary key identity(1,1) not null
, dt_ref date not null
, TextCol nvarchar(50) not null
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Dates2018](
id bigint primary key identity(1,1) not null
, dt_ref date not null
, TextCol nvarchar(50) not null
) ON [PRIMARY]
GO
CREATE VIEW [dbo].[DatesAll]
AS
SELECT *
FROM
dbo.Dates2015
union all
SELECT *
from
dbo.Dates2016
union all
SELECT *
from
dbo.Dates2017
union all
SELECT *
from
dbo.Dates2018
GO
Introduzimos agora uns valores nas tabelas para que elas não se sintam vazias, desprovidas de realidade…
insert into Dates2015
( Dt_ref, TextCol)
select DATEADD(day, ROUND(((DATEDIFF(day, '20150101', '20151231')-1) * RAND()), 0), '20150101')
, CAST(newid() as varchar(50))
go 10000
insert into Dates2016
( Dt_ref, TextCol)
select DATEADD(day, ROUND(((DATEDIFF(day, '20160101', '20161231')-1) * RAND()), 0), '20160101')
, CAST(newid() as varchar(50))
go 10000
insert into Dates2017
( Dt_ref, TextCol)
select DATEADD(day, ROUND(((DATEDIFF(day, '20170101', '20171231')-1) * RAND()), 0), '20170101')
, CAST(newid() as varchar(50))
go 10000
insert into Dates2018
( Dt_ref, TextCol)
select DATEADD(day, ROUND(((DATEDIFF(day, '20180101', '20181231')-1) * RAND()), 0), '20180101')
, CAST(newid() as varchar(50))
go 10000
Agora vamos lá verificar o estado sem índices nem constraints
set statistics time, io on
select top 100 *
from [dbo].[DatesAll]
where dt_ref >= '2018-01-05'
Os resultados não interessam, mas o plano de execução e os resultados do tempo e i/o são esclarecedores
Vemos no plano de execução que andamos pelas 4 tabelas
Notamos bem que varremos as 4 tabelas e, de início parece estranho mas se pensarmos bem é o mais natural, faz menos reads na tabela onde sabemos ser a única que tem dados.
Os mais atentos notaram no missing index recomendation na tabela Dates2017, mas realmente ele pede-nos índices nas 3 tabelas onde teve mais problemas. Mais uma machadada para quem gosta de “seguir à risca” o que o optimizer sugere.
Quando há pouco disse que “é o mais natural”… é o mais natural porquê?
Vejamos o que o nosso amigo SQL Server sabe. Tem uma query a uma vista que é a concatenação de 4 tabelas. Não sabe grande coisa sobre essas tabelas. Vai à vista e expande o código, aparece-lhe a primeira tabela que é a Dates2015, varre-a toda à procura de 100 linhas que respondam à pergunta que lhes fizemos. Depois vai à seguinte… e à seguinte… e finalmente encontra uma onde consegue retirar de lá as 100 linhas sem ter que procurar mais.
“Ó António, estás-me a dizer que a ordem das tabelas na vista interessa?” Claro… mas como sempre… nada como experimentarmos com uma query que sabemos ser satisfeita com a primeira tabela da vista.
select top 100 *
from [dbo].[DatesAll]
where dt_ref <= '2015-10-05'
Ora aqui está. 4 reads na Dates2015 e não precisou de ir às outras 3 tabelas, apesar de ter pensado nisso aquando da optimização.
E, já que por aqui andamos, vamos fazer um join que sabemos ser impossível.
select *
from Dates2015 a
inner join Dates2016 b
on a.Dt_ref = b.Dt_ref
Pois é, nós sabemos mas “ele” não sabe, e por isso anda à procura.
E agora vamos ao que interessa neste post, a resposta à pergunta “e com índices, pá”?
create nonclustered index ncidx on Dates2015 (Dt_ref)
create nonclustered index ncidx on Dates2016 (Dt_ref)
create nonclustered index ncidx on Dates2017 (Dt_ref)
create nonclustered index ncidx on Dates2018 (Dt_ref)
select top 100 *
from [dbo].[DatesAll]
where dt_ref >= '2018-01-05'
Nada de muito óbvio, à parte de agora ser um index seek.
Agora já se vê alguma diferença. Quase não há reads, afinal andamos pelo índice e não pela tabela e ele, apesar de não saber tudo, já sabe procurar melhor e mais rápido.
E com o caso do join impossível? Também melhora?
Infelizmente não há muito que se tenha melhorado. “Não fez nada”, diríamos.
Pois não, não fez, porque o nosso amigo “select * ” não deixou. Havia pura e simplesmente muito trabalho a ir buscar os campos adicionais que não estavam no nosso índice e o optimizer preferiu usar a tabela toda. Bookmark lookups são ainda piores. Haveremos de ver isso em outro post.
Vamos lá repetir isto apenas a ir buscar a data.
select a.dt_ref
from Dates2015 a
inner join Dates2016 b
on a.Dt_ref = b.Dt_ref
Em vez de um gastador Hash Match temos um Merge Join e mais rapidamente chegamos ao resultado.
E se criarmos as nossas constraints? Aquelas que ajudam muita gente…
alter table [dbo].[Dates2015]
add constraint dt_2015
check ( dt_ref >= '2015-01-01'
and
dt_ref <= '2015-12-31' )
alter table [dbo].[Dates2016]
add constraint dt_2016
check ( dt_ref >= '2016-01-01'
and
dt_ref <= '2016-12-31' )
alter table [dbo].[Dates2017]
add constraint dt_2017
check ( dt_ref >= '2017-01-01'
and
dt_ref <= '2017-12-31' )
alter table [dbo].[Dates2018]
add constraint dt_2018
check ( dt_ref >= '2018-01-01'
and
dt_ref <= '2018-12-31' )
vamos lá repetir as nossa queries
select top 100 *
from [dbo].[DatesAll]
where dt_ref >= '2018-01-05'
E aos joins?
select *
from Dates2015 a
inner join Dates2016 b
on a.Dt_ref = b.Dt_ref
“Então não bastava termos usado uns índices e o assunto ficaria resolvido?”
Não.
