SQL 2014 - Tabela temporária mais rápida que variável table
12/03/2015
0
Pessoal, tudo bem?
Estamos homologando um novo servidor com SQL 2014 em cluster. Durante os testes de performance, aconteceu agora bem estranho. Tenho uma rotina composta de diversos SELECT, variáveis TABLE e CTE. Basicamente, o que estou executando é:
USE MASTER
GO
ALTER DATABASE QVDSM3 SET COMPATIBILITY_LEVEL = 100 -- na segunda execução, eu troco para 120
GO
USE QVDSM3
go
DECLARE @nPeriodo = 201501
...
E registro o tempo. Em seguida, mudo a compatibilidade e executo a query novamente.
No caso da execução com compatibilidade para SQL 2008, a query demora zero segundos. Porém, se eu mudo a compatibilidade para 2014, a mesma rotina demora quase 1 minuto para rodar. Pensei que pudesse ser alguma coisa na estrutura de índices ou estatísticas, por isso recriei todos os índices e estatísticas na compatibilidade 2014. Mesmo assim, a query continua demorando 1 minuto para rodar.
Entre algumas modificações, na base da tentativa e erro, alterei todas as variáveis TABLE para tabelas temporárias. Com isso, o tempo da rotina caiu para zero!!!
Resumindo, o que tenho em relação a tempos é:
Compatibilidade SQL 2008: a rotina demora zero segundos usando variáveis TABLE ou tabelas temporárias.
Compatibilidade SQL 2014: a rotina demora SESSENTA segundos usando variáveis TABLE e ZERO segundos usando tabelas temporárias.
A rotina é composta por:
1.Vários SELECTS e CTE que carregam dados para dentro das variáveis TABLE ou tabelas temporárias.
2.Cinco INSERT/SELECT usando tabelas do BD e objetos criados no item 1.
3.Três UPDATE/SELECT usando tabelas do BD e objetos criados no item 1.
Passei então ao plano de manutenção, que é composto de 17 segmentos. Eu validei cada um deles, alterando a rotina e usando:
1) Variável table no SQL 2008 e SQL 2014.
2) Tabela temporária no SQL 2008 e SQL 2014.
3) Variável table em uma janela e tabela temporária em outra janela, ambas no SQL 2008.
4) Variável table em uma janela e tabela temporária em outra janela, ambas no SQL 2014.
Em todos os casos, o plano de manutenção é sempre o mesmo. A variação de custo é mínima para cada comando, o máximo de diferença que apareceu entre os custo foi de 5%, o que não justificaria uma rotina no SQL 2014 demorar 60 vezes mais do que ela mesma no SQL 2008.
Não sei se é adequado considerar este caso como um gargalo de consulta. Como eu mencionei, a rotina roda em zero segundos no SQL 2008. O problema é quando executo a mesma rotina no SQL 2014, onde ela demora mais de um minuto para trazer o mesmo resultado.
Minha pergunta é: porque o uso de variáveis TABLE numa rotina pode deixa-la tão demorada, quando o uso de tabelas temporárias deixa a rotina instantânea?
Estamos homologando um novo servidor com SQL 2014 em cluster. Durante os testes de performance, aconteceu agora bem estranho. Tenho uma rotina composta de diversos SELECT, variáveis TABLE e CTE. Basicamente, o que estou executando é:
USE MASTER
GO
ALTER DATABASE QVDSM3 SET COMPATIBILITY_LEVEL = 100 -- na segunda execução, eu troco para 120
GO
USE QVDSM3
go
DECLARE @nPeriodo = 201501
...
E registro o tempo. Em seguida, mudo a compatibilidade e executo a query novamente.
No caso da execução com compatibilidade para SQL 2008, a query demora zero segundos. Porém, se eu mudo a compatibilidade para 2014, a mesma rotina demora quase 1 minuto para rodar. Pensei que pudesse ser alguma coisa na estrutura de índices ou estatísticas, por isso recriei todos os índices e estatísticas na compatibilidade 2014. Mesmo assim, a query continua demorando 1 minuto para rodar.
Entre algumas modificações, na base da tentativa e erro, alterei todas as variáveis TABLE para tabelas temporárias. Com isso, o tempo da rotina caiu para zero!!!
Resumindo, o que tenho em relação a tempos é:
Compatibilidade SQL 2008: a rotina demora zero segundos usando variáveis TABLE ou tabelas temporárias.
Compatibilidade SQL 2014: a rotina demora SESSENTA segundos usando variáveis TABLE e ZERO segundos usando tabelas temporárias.
A rotina é composta por:
1.Vários SELECTS e CTE que carregam dados para dentro das variáveis TABLE ou tabelas temporárias.
2.Cinco INSERT/SELECT usando tabelas do BD e objetos criados no item 1.
3.Três UPDATE/SELECT usando tabelas do BD e objetos criados no item 1.
Passei então ao plano de manutenção, que é composto de 17 segmentos. Eu validei cada um deles, alterando a rotina e usando:
1) Variável table no SQL 2008 e SQL 2014.
2) Tabela temporária no SQL 2008 e SQL 2014.
3) Variável table em uma janela e tabela temporária em outra janela, ambas no SQL 2008.
4) Variável table em uma janela e tabela temporária em outra janela, ambas no SQL 2014.
Em todos os casos, o plano de manutenção é sempre o mesmo. A variação de custo é mínima para cada comando, o máximo de diferença que apareceu entre os custo foi de 5%, o que não justificaria uma rotina no SQL 2014 demorar 60 vezes mais do que ela mesma no SQL 2008.
Não sei se é adequado considerar este caso como um gargalo de consulta. Como eu mencionei, a rotina roda em zero segundos no SQL 2008. O problema é quando executo a mesma rotina no SQL 2014, onde ela demora mais de um minuto para trazer o mesmo resultado.
Minha pergunta é: porque o uso de variáveis TABLE numa rotina pode deixa-la tão demorada, quando o uso de tabelas temporárias deixa a rotina instantânea?
Ignez Mello
Curtir tópico
+ 0
Responder
Clique aqui para fazer login e interagir na Comunidade :)