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.
