Crie um site como este com o WordPress.com
Comece agora

Qual é mais rápido: “SELECT … into” ou “INSERT … SELECT”?

Qual das duas construções abaixo você acha que é de execução mais rápida?

— construção #1
INSERT into ​tabela_destino
SELECT *
from ​origem;

ou

— construção #2
SELECT *
into ​tabela_destino
from ​origem;

Essa questão foi postada há poucos dias em um artigo de Pinal Dave e ele afirmou que a construção #2 é mais rápida.

Será mesmo? 🤔

1. Introdução

Há poucos dias, no Linked in, li referência a um artigo de blog em que o autor do artigo comparou a performance de construções T-SQL para carga inicial de uma tabela. As construções comparadas foram

— construção #1
INSERT into tabela_destino
SELECT *
from
origem;

e

— construção #2
SELECT *
into
tabela_destino
from
origem;

Nos testes o autor daquele artigo, Pinal Dave (doravante chamado de PD), comentou que, ao analisar a demora em carga de uma tabela, deduziu que a causa era o uso da construção #1 e sugeriu o uso da construção #2, que se mostrou mais eficiente nos testes que ele efetuou. E, a partir desses testes PD então escreve na conclusão do artigo que

If you compare the time of this query with the previous query which we ran in Test 1, you can clearly see that absolutely hands down the winner is Test 2: SELECT INTO

e ainda comenta que

I have run this test multiple times with different datasets and scenarios and I noticed that every time SELECT INTO is faster than INSERT INTO SELECT”.

Ou seja, PD afirma e reafirma que “SELECT … INTO” é mais rápido do que “INSERT into … SELECT”.

Só que não!


2. Testes

2.1. TABLOCK

Se analisarmos somente os códigos T-SQL que PD utilizou nos testes, a afirmação dele estaria correta. Entretanto, é preciso ter em mente que o objetivo da consultoria que PD realizou no cliente era para otimização de uma movimentação volumosa de dados de uma tabela para outra. Ou seja, bulk load. E, pelo que se deduz da solução sugerida por PD (construção #1), a tabela de destino estava vazia.

Recentemente publiquei artigo sobre bulk load no SQL Server e quando vi a construção #1 me perguntei: mas cadê o TABLOCK? Resolvi então testar a construção #1 mas com a dica de tabela TABLOCK definida para a tabela de destino, criando uma terceira solução:

— construção #3
INSERT into tabela_destino with (TABLOCK)
SELECT *
from
origem;

De acordo com a documentação de TABLOCK (vide item Referências, ao final), “When importing data into a heap by using the INSERT INTO SELECT FROM statement, you can enable optimized logging and locking for the statement by specifying the TABLOCK hint for the target table. In addition, the recovery model of the database must be set to simple or bulk-logged”.

Peguei os scripts que PD publicou em seu artigo e os executei em instância com o SQL Server 2016, utilizando o mesmo banco de dados. Somente acrescentei o terceiro teste, utilizando a construção #3.

Adivinha qual foi resultado?

2.2. Obtenção do banco de dados

O primeiro passo foi obter o mesmo banco de dados que PD utilizou em seus testes, que é o WideWorldImporters. Este é um banco de dados de amostra da Microsoft, disponibilizado a partir da versão 2016 do SQL Server. Para quem não conhece o que é esse banco de dados, basta acessar o documento “Wide World Importers sample databases for Microsoft SQL” (vide item Referências, ao final).

O primeiro código do artigo de PD gera uma tabela com 10 milhões de linhas para simular a origem. Nos testes que realizei, reduzi a amostra para 1 milhão de linhas.

— código #1
USE [WideWorldImporters]
GO
— SampleTable Build
CREATE TABLE [10MillionRows](
     [StockItemTransactionID] [int] NOT NULL,
     [StockItemID] [int] NOT NULL,
     [TransactionTypeID] [int] NOT NULL,
     [CustomerID] [int] NULL,
     [InvoiceID] [int] NULL,
     [SupplierID] [int] NULL,
     [PurchaseOrderID] [int] NULL,
     [TransactionOccurredWhen] [datetime2](7) NOT NULL,
     [Quantity] [decimal](18, 3) NOT NULL,
     [LastEditedBy] [int] NOT NULL,
     [LastEditedWhen] [datetime2](7) NOT NULL);
GO
INSERT INTO [10MillionRows]
  SELECT TOP 10000000 SI.*
    FROM [Warehouse].[StockItemTransactions] SI
         CROSS JOIN [Warehouse].[StockItemTransactions] SI1;
GO

2.3. Teste 1: INSERT … SELECT

O primeiro teste, assim como no artigo de PD, foi com a construção #1 (INSERT … SELECT), utilizando o código que consta naquele artigo, acrescido da opção para exibir as estatísticas de IO.

— código #2
CREATE TABLE [InsertIntoTest1](
     [StockItemTransactionID] [int] NOT NULL,
     [StockItemID] [int] NOT NULL,
     [TransactionTypeID] [int] NOT NULL,
     [CustomerID] [int] NULL,
     [InvoiceID] [int] NULL,
     [SupplierID] [int] NULL,
     [PurchaseOrderID] [int] NULL,
     [TransactionOccurredWhen] [datetime2](7) NOT NULL,
     [Quantity] [decimal](18, 3) NOT NULL,
     [LastEditedBy] [int] NOT NULL,
     [LastEditedWhen] [datetime2](7) NOT NULL
);
GO
— Actual Test
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
INSERT INTO [InsertIntoTest1]
SELECT *
  FROM [10MillionRows];
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
— Clean up
DROP TABLE [InsertIntoTest1];
GO

A figura abaixo contém imagem do plano de execução real:

código#2

As estatísticas na execução do código #2 (INSERT … SELECT) emitiram o seguinte resultado:

— resultado teste 1
Tabela ‘InsertIntoTest1’. Número de verificações 0, leituras lógicas 1008333, leituras físicas 0, leituras read-ahead 0, leituras lógicas lob 0, leituras físicas lob 0, leituras read-ahead lob 0.
Tabela ’10MillionRows’. Número de verificações 1, leituras lógicas 8334, leituras físicas 0, leituras read-ahead 0, leituras lógicas lob 0, leituras físicas lob 0, leituras read-ahead lob 0.
Tempos de Execução do SQL Server:
Tempo de CPU = 24016 ms, tempo decorrido = 25327 ms.

2.4. Teste 2: SELECT … into

O segundo teste, assim como no artigo de PD, foi com a construção #2 (SELECT … into), utilizando o código que consta naquele artigo, acrescido também da opção para exibir as estatísticas de IO.

— código #3
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
SELECT *
  INTO [dbo].[SelectIntoTest]
  FROM [10MillionRows];
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
— Clean up
DROP TABLE [SelectIntoTest];
GO

A figura abaixo contém imagem do plano de execução real:

código#3

As estatísticas na execução do código #3 (SELECT … into) emitiram o seguinte resultado:

— resultado teste 2
Tabela ’10MillionRows’. Número de verificações 1, leituras lógicas 8334, leituras físicas 0, leituras read-ahead 0, leituras lógicas lob 0, leituras físicas lob 0, leituras read-ahead lob 0.
Tempos de Execução do SQL Server:
Tempo de CPU = 14890 ms, tempo decorrido = 15707 ms.

Comparando visualmente as estatísticas, percebe-se que a construção #2 resultou em melhor performance. Mas antes de analisar detalhadamente os resultados, vamos ao último teste.

2.5. Teste 3: INSERT … with (TABLOCK) SELECT

O terceiro e último teste é derivado do código #1, no qual é acrescentada a dica de tabela TABLOCK.

— código #4
CREATE TABLE [InsertIntoTest3](
     [StockItemTransactionID] [int] NOT NULL,
     [StockItemID] [int] NOT NULL,
     [TransactionTypeID] [int] NOT NULL,
     [CustomerID] [int] NULL,
     [InvoiceID] [int] NULL,
     [SupplierID] [int] NULL,
     [PurchaseOrderID] [int] NULL,
     [TransactionOccurredWhen] [datetime2](7) NOT NULL,
     [Quantity] [decimal](18, 3) NOT NULL,
     [LastEditedBy] [int] NOT NULL,
     [LastEditedWhen] [datetime2](7) NOT NULL);
GO
— Actual Test
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
INSERT INTO [InsertIntoTest3] with (TABLOCK)
  SELECT *
    FROM [10MillionRows];
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
— Clean up
DROP TABLE [InsertIntoTest3];
GO

A figura abaixo contém imagem do plano de execução real do código #4:

código#4

As estatísticas na execução do código #3. INSERT … with (TABLOCK) SELECT, emitiram o seguinte resultado:

— resultado teste 3
Tabela ’10MillionRows’. Número de verificações 1, leituras lógicas 8334, leituras físicas 0, leituras read-ahead 0, leituras lógicas lob 0, leituras físicas lob 0, leituras read-ahead lob 0.
Tempos de Execução do SQL Server:
Tempo de CPU = 14141 ms, tempo decorrido = 14948 ms.

3. Análise dos resultados

3.1. Quadro comparativo

Para facilitar a análise dos testes foi compilado o seguinte quadro com os resultados:

Teste Tabela de destino Tabela de origem Tempo CPU Tempo decorrido
1 1.008.333 8.334 24.016 ms 25.327 ms
2 8.334 14.890 ms 15.707 ms
3 8.334 14.141 ms 14.948 ms

Comparando-se os resultados dos testes 2 e 3 percebe-se que na prática não há diferença entre eles. Ou seja, a afirmação genérica de Pinal Dave de que “SELECT … into” é mais rápido do que “INSERT .. SELECT” não procede.

Os testes que efetuei inicialmente foram com a edição Express do SQL Server 2016 SP1, que não possibilita que os planos de execução gerados utilizem paralelismo. Posteriormente repeti os testes, mas na edição Developer do SQL Server 2016 SP2 e com o paralelismo ativo. O equilíbrio entre os resultados dos testes 2 e 3 ocorreu nas duas condições (sem e com paralelismo).

Para compreender as opções de paralelismo na construção “INSERT … SELECT” a partir do SQL Server 2016 recomendo a leitura de 3 artigos, na sequência:

3.2. Prós e contras

Ao consultar os artigos anteriormente mencionados foi possível conhecer que os resultados podem ser diferentes na versão 2014 do SQL Server, caso o paralelismo esteja ativo. É que para a construção “INSERT … SELECT” o paralelismo somente está disponível a partir da versão 2016 mas para a construção “SELECT … into” é possível a partir da versão 2014.

As construções do tipo “SELECT … into” não copiam várias informações da tabela de origem (restrições, índices, procedimentos de gatilho, permissões) e também não permitem a especificação de FILEGROUP. Além disso, pelo fato de combinar ações DDL e DML, os metadados podem ficar bloqueados enquanto a transação estiver ativa (caso a consulta seja com nível de isolamento read committed). Por outro lado, são úteis para a criação de tabelas temporárias.

3.3. Conclusão

De forma geral, as duas construções se equivalem (desde que se defina a dica de tabela TABLOCK na tabela de destino para a construção #1), embora no SQL Server 2014 possa existir diferença de performance entre as duas construções.

Considerando-se as informações disponibilizadas neste artigo, sugiro que faça testes em seu ambiente de trabalho e tire suas conclusões.

4. Referências


PDF
Este artigo também está disponível em arquivo no formato PDF, o que possibilita tanto visualizar online quanto obter cópia (download) para leitura offline e até mesmo impressão. Clique na figura abaixo para acessar.

código#4_paralelo

Publicidade

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

Este site utiliza o Akismet para reduzir spam. Saiba como seus dados em comentários são processados.

%d blogueiros gostam disto: