SQL Server Performance do SELECT INTO vs INSERT INTO para tabelas temporárias

Por Jignesh Raiyani | Actualizado: 2017-01-04 | Comentários (9) | Relacionados: Mais > T-SQL

div>

Problem

SQL Server inclui SELECT…INTO e INSERT…INTO código para inserção de dados em tabelas temporárias. Pode fornecer algumas amostras e linha qual a opção com melhor desempenho?

Solução

ServidorSQL inclui as duas opções para tabelas temporárias:

• Tabela temporária local
• Tabela temporária global

P>É necessário acrescentar prefixo ‘#’ para tabelas temporárias locais e ‘##’ para tabelas temporárias globais. Estes objectos serão criados na base de dados do sistema TempDB. As tabelas temporárias são armazenadas na base de dados TempDB com um nome de objecto único e criadas a um nível de ligação ou sessão. Estas tabelas são visíveis e acessíveis apenas dentro da sessão. Podemos abandonar a tabela temporária usando o comando DROP TABLE ou a tabela temporária será abandonada automaticamente quando a sessão se desconectar. Além disso, o SQL Server mantém as estatísticas das tabelas temporárias.

A criação e eliminação de tabelas temporárias requer acesso e modificação das páginas do mapa de atribuição TempDB (IAM, SGAM e PES). As tabelas temporárias são armazenadas em cache em comparação com as tabelas permanentes baseadas em disco. Com o cache de tabelas temporárias, o SQL Server não deixará cair fisicamente a tabela, mas irá truncá-la e manter as páginas IAM e de dados em cache. Quando a tabela é criada mais tarde, o SQL Server irá reutilizar as páginas anteriores, o que reduz o número de modificações de página necessárias.

As tabelas temporárias podem ser criadas de duas maneiras:

• TABELA DE CRÉDITO
• SELECT INTO

Generalmente falando, o desempenho de ambas as opções é semelhante para um pequeno conjunto de dados. Os dados são inseridos rapidamente na tabela temporária, mas se a quantidade de dados for grande, então podemos experimentar um fraco desempenho na consulta. Isto acontece normalmente com tabelas temporárias quando inserimos um grande número de filas.

SQL Server INSERT INTO Exemplo

SET STATISTICS IO ONSET STATISTICS TIME ONDECLARE @ids NVARCHAR(MAX) = N'115676,115678,115679,115680,115681'DECLARE @input_xml XMLCREATE TABLE #list_to_table(Id BIGINT)SELECT @input_xml = Cast('<root><x>'+ Replace(@ids, ',', '</x><x>')+ '</x></root>' AS XML) INSERT INTO #list_to_tableSELECT f.x.value('.', 'BIGINT') AS IdFROM @input_xml.nodes('/root/x') f(x)DROP TABLE #list_to_table

SQL Server parse e tempo de compilação:

CPU time = 0 ms, elapsed time = 2 ms.
Table '#list_to_table____000000000015'. Scan count 0, logical reads 5, 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 = 0 ms, elapsed time = 0 ms.(5 row(s) affected)SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. 

Quando há uma grande quantidade de valores separados por vírgula como parâmetro de entrada, o código pode ser atrasado por alguns segundos enquanto se insere os dados no instável.

SQL Server SELECT INTO Example

Uma solução alternativa que podemos utilizar é o SELECT….INTO comando que geralmente tem melhor desempenho do que o INSERT…SELECT comando.

SET STATISTICS IO ONSET STATISTICS TIME ONDECLARE @ids NVARCHAR(MAX) = N'115676,115678,115679,115680,115681'DECLARE @input_xml XMLSELECT @input_xml = Cast('<root><x>'+ Replace(@ids, ',', '</x><x>')+ '</x></root>' AS XML)SELECT f.x.value('.', 'BIGINT') AS IdINTO #list_to_tableFROM @input_xml.nodes('/root/x') f(x)DROP TABLE #list_to_table

SQL Server parse e tempo de compilação:

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.(5 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server INSERT…SELECT vs. SELECT…INTO com um Conjunto de Dados Maior

Os exemplos acima são muito pequenos, por isso vamos usar o exemplo abaixo para ver como isto funciona com uma grande quantidade de dados. Este teste terá mais de um milhão de filas.

SQL Server INSERT… SELECT

CREATE TABLE #EMPLOYEEEEE (Emp_id BIGINT, f_name NVARCHAR(100), l_name NVARCHAR(100), Email NVARCHAR(100), is_active BIT) INSERT INTO #EMPLOYEESELECT Emp_id, f_name, l_name, Email, is_active FROM employee

SQL Server parse e tempo de compilação:

SQL Server Execution Times: CPU time = 1407 ms, elapsed time = 3241 ms.(1727264 row(s) affected)

SQL Server SELECT…INTO

SELECT Emp_id, f_name, l_name, Email, is_activeeINTO #EMPLOYEEFROM employee

SQL Server parse e tempo de compilação:

SQL Server Execution Times: CPU time = 1499 ms, elapsed time = 489 ms.(1727264 row(s) affected)

Resultados e Análise

Como podemos ver o SELECT…INTO foi consideravelmente mais rápido 489ms em comparação com3241ms.

O comando INSERT…INTO reutilizará páginas de dados que são criadas em cache para operações de inserção/actualização/apagamento. O comando SELECT…INTO irá criar novas páginas para criação de tabelas semelhantes a tabelas regulares e irá removê-las fisicamente quando a tabela temporária for abandonada.

Passos seguintes

• Calcula estas considerações de desempenho à medida que desenvolve o seu código ou tem de afinar o código que não está a funcionar da melhor forma.
• Teste cenários adicionais no seu ambiente para determinar qual a técnica de codificação que irá desempenhar melhor.
• Tenham sempre uma mente aberta a várias técnicas de codificação, testem cada técnica e deixem o desempenho ditar a resposta.

br>Última Actualização: 2017-01-04

>br>

botão da próxima dica

h5>Sobre o author

Jignesh Raiyani é um programador de SQL Server/DBA com experiência na concepção e desenvolvimento de T-Procedimentos SQL e afinação do desempenho da consulta.
Ver todas as minhas dicas

Recursos Relacionados
ul>>li>Mais Dicas de Desenvolvimento de Base de Dados…

br