Quais são os bons princípios na determinação do que é mais rápido e porquê? Penso que a Microsoft optimizaria a inserção em , igualmente rápida, para uma programação cuidadosa.
Os princípios que tento seguir ao analisar algo como esta questão são:
- Evite fazer suposições desnecessárias.
- Leia a documentação oficial.
- Teste a carga de trabalho. A quantidade de testes depende da rapidez com que preciso que o código seja.
Tenho conhecimento de duas peças de documentação que abordam a sua pergunta. O primeiro é um post de blog dizendo que SELECT INTO para tabelas temporárias tem um comportamento diferente para escritas ávidas a partir do SQL Server 2014. Ou seja, por design. Portanto, não creio que seja correcto dizer que a diferença é mínima para tabelas pequenas. Se alguma coisa, a optimização descrita no post do blog parece concebida para tabelas menores:
A alteração no SQL Server 2014 é para relaxar a necessidade de descarregar estas páginas, tão rapidamente, para os ficheiros de dados TEMPDB. Ao fazer uma selecção em … #tmp … ou criar índice COM GESTÃO EM TEMPDB o SQL Server reconhece agora que esta pode ser uma operação de curta duração. As páginas associadas a tal operação podem ser criadas, carregadas, consultadas e lançadas numa janela de tempo muito pequena.
Por exemplo: Poderá ter um procedimento armazenado que corre em 8ms. Nesse procedimento armazenado, seleccione em … #tmp … depois use o #tmp e largue-o quando o procedimento armazenado estiver completo.
P>Prior to the SQL Server 2014 change the select into may have written all the pages accumulated to disk. O SQL Server 2014, ansioso comportamento de escrita, já não força estas páginas para o disco tão rapidamente como as versões anteriores. Este comportamento permite que as páginas sejam armazenadas em RAM (buffer pool), consultadas e que a tabela caia (removida do buffer pool e devolvida à lista livre) sem nunca ir para o disco, uma vez que a memória longa está disponível. Ao evitar a E/S física quando possível o desempenho do TEMPDB, o funcionamento em massa é significativamente aumentado e reduz também o impacto nos recursos do caminho de E/S.
A segunda peça de documentação explica que a inserção paralela em tabelas temporárias com INSERT INTO ... SELECT está disponível sem uma TABLOCK dica no SQL Server 2016 mas requer uma TABLOCK dica no SP1 e em versões futuras.
O problema é primeiro fixado no SQL Server 2016 Service Pack 1 . Depois de aplicar o SQL Server 2016 SP1, Parallel INSERTs em INSERT…SELECT a tabelas temporárias locais é desactivado por defeito, o que reduz a contenção na página PFS e melhora o desempenho global para carga de trabalho concorrente. Se INSERTs paralelos a tabelas temporárias locais for desejado, os utilizadores devem usar a dica TABLOCK enquanto inserem na tabela temporária local.
Voltando à sua declaração original, não se pode deduzir logicamente qual das duas será mais rápida. O que é mais rápido depende de como a Microsoft concebeu o software e das características da sua carga de trabalho. Fazer suposições sobre o tempo necessário para criar as definições das colunas simplesmente não ajuda. Os testes são úteis. Se os seus testes sugerem que SELECT INTO é mais rápido do que isso. Para que conste, também trabalho no carregamento do armazém de dados com um olho atento ao desempenho e ainda não vi a diferença entre as duas abordagens ser algo que valha a pena preocupar.