No final, há um script de teste para comparar o desempenho entre uma variável @table e uma tabela #temp. Acho que configurei corretamente - os tempos de desempenho são retirados dos comandos DELETE/TRUNCATE . Os resultados que estou obtendo são os seguintes (tempos em milissegundos).
@Table Variable #Temp (delete) #Temp (truncate)
--------------- -------------- ----------------
5723 5180 5506
15636 14746 7800
14506 14300 5583
14030 15460 5386
16706 16186 5360
Só para ter certeza de que estou são, isso mostra que CURRENT_TIMESTAMP (também conhecido como GetDate()) é obtido no momento da instrução, não do lote, portanto, não deve haver interação entre TRUNCATE/DELETE com a SET @StartTime = CURRENT_TIMESTAMPinstrução.
select current_timestamp
waitfor delay '00:00:04'
select current_timestamp
-----------------------
2012-10-21 11:29:20.290
-----------------------
2012-10-21 11:29:24.290
É bastante consistente no salto entre a primeira execução e as subsequentes quando DELETE é usado para limpar a tabela. O que estou perdendo em minha compreensão de DELETE ? Eu repeti isso muitas vezes, troquei a ordem, dimensionei o tempdb para não exigir crescimento, etc.
CREATE TABLE #values (
id int identity primary key, -- will be clustered
name varchar(100) null,
number int null,
type char(3) not null,
low int null,
high int null,
status smallint not null
);
GO
SET NOCOUNT ON;
DECLARE @values TABLE (
id int identity primary key clustered,
name varchar(100) null,
number int null,
type char(3) not null,
low int null,
high int null,
status smallint not null
);
DECLARE @ExecutionTime TABLE( Duration bigINT )
DECLARE @StartTime DATETIME, @i INT = 1;
WHILE (@i <= 5)
BEGIN
DELETE @values;
DBCC freeproccache With NO_InfoMSGS;
DBCC DROPCLEANBUFFERS With NO_InfoMSGS;
SET @StartTime = CURRENT_TIMESTAMP -- alternate getdate()
/****************** measured process ***********************/
INSERT @values SELECT a.* FROM master..spt_values a join master..spt_values b on b.type='P' and b.number < 1000;
/**************** end measured process *********************/
INSERT @ExecutionTime
SELECT DurationInMilliseconds = datediff(ms,@StartTime,CURRENT_TIMESTAMP)
SET @i += 1
END -- WHILE
SELECT DurationInMilliseconds = Duration FROM @ExecutionTime
GO
-- Temporary table
DECLARE @ExecutionTime TABLE( Duration bigINT )
DECLARE @StartTime DATETIME, @i INT = 1;
WHILE (@i <= 5)
BEGIN
delete #values;
-- TRUNCATE TABLE #values;
DBCC freeproccache With NO_InfoMSGS;
DBCC DROPCLEANBUFFERS With NO_InfoMSGS;
SET @StartTime = CURRENT_TIMESTAMP -- alternate getdate()
/****************** measured process ***********************/
INSERT #values SELECT a.* FROM master..spt_values a join master..spt_values b on b.type='P' and b.number < 1000;
/**************** end measured process *********************/
INSERT @ExecutionTime
SELECT DurationInMilliseconds = datediff(ms,@StartTime,CURRENT_TIMESTAMP)
SET @i += 1
END -- WHILE
SELECT DurationInMilliseconds = Duration FROM @ExecutionTime
GO
DROP TABLE #values
SET NOCOUNT OFF;
Essa diferença só parece se aplicar quando o objeto é uma árvore B+. Ao remover a
primary keyvariável da tabela para que seja um heap, obtive os seguintes resultadosMas com o PK encontrei um padrão semelhante em meus testes também com resultados típicos abaixo.
Minha teoria é que há alguma otimização disponível ao fazer inserções em massa em árvores B+ temporárias locais que só se aplica quando ainda não possui nenhuma página alocada.
Baseio-me nas seguintes observações.
Ao executar várias versões do seu código de teste, só vi esse padrão com tabelas
@table_variablese .#tempNão são tabelas permanentestempdbnem##tabelas.Para obter o desempenho mais lento, não é necessário ter adicionado e removido anteriormente uma grande quantidade de linhas da tabela. Simplesmente adicionar uma única linha e deixá-la lá é suficiente.
TRUNCATEdesaloca todas as páginas da tabela.DELETEnão fará com que a última página da tabela seja desalocada.O uso do criador de perfil do VS 2012 mostra que, no caso mais rápido, o SQL Server usa um caminho de código diferente. 36% do tempo é gasto em
sqlmin.dll!RowsetBulk::InsertRowvs 61% do tempo gasto nosqlmin.dll!RowsetNewSS::InsertRowcaso mais lento.Corrida
depois que a exclusão retorna
Descobri que era possível reduzir um pouco a discrepância de tempo ativando o sinalizador de rastreamento 610 .
Isso teve o efeito de reduzir substancialmente a quantidade de registros para as inserções subsequentes (de 350 MB para 103 MB, já que não registra mais os valores individuais das linhas inseridas), mas teve apenas uma pequena melhoria nos tempos para o segundo e
@tablesubsequentes#tablecasos e a lacuna ainda permanece. O sinalizador de rastreamento melhorou significativamente o desempenho geral das inserções nos outros dois tipos de tabela.Ao examinar o log de transações, notei que as inserções iniciais em tabelas temporárias locais vazias parecem ainda mais minimamente registradas (em 96 MB).
Notavelmente, essas inserções mais rápidas tinham apenas
657transações (LOP_BEGIN_XACT/LOP_COMMIT_XACTpares) em comparação com10,000os casos mais lentos. Em particularLOP_FORMAT_PAGE, as operações parecem muito reduzidas. Os casos mais lentos têm uma entrada de log de transação para cada página na tabela (cerca de10,270) em comparação com apenas4essas entradas no caso rápido.O log usado em todos os três casos foi o seguinte (excluí os registros de log para atualizações nas tabelas base do sistema para reduzir a quantidade de texto, mas eles ainda estão incluídos nos totais)
Registrando a primeira inserção contra
@table_var(96,5 MB)Registrando inserções subseqüentes TF 610 off (350 MB)
Registrando inserções subsequentes TF 610 em (103 MB)
Observação e especulação. . .
Em alguns sistemas, CURRENT_TIMESTAMP é definido como a hora de início da transação atual. Uma pesquisa rápida não revelou nenhuma documentação definitiva de como CURRENT_TIMESTAMP se comporta no SQL Server. Mas o modo padrão do SQL Server é confirmar transações automaticamente, e não há BEGIN TRANSACTION aqui, então deve ser o tempo imediatamente antes da instrução INSERT. (A instrução DELETE deve ser confirmada automaticamente e, independentemente da maneira como CURRENT_TIMESTAMP funcione no SQL Server, não deve ter nada a ver com a instrução DELETE quando você estiver usando transações confirmadas automaticamente.)
Na primeira iteração, a instrução DELETE não tem nenhum trabalho real a fazer e não há nenhuma linha individual para registrar. Talvez o otimizador saiba disso e esteja reduzindo o tempo da primeira iteração. (A combinação de nenhuma linha para excluir e nenhuma linha individual para registrar.)
Você poderia testar isso (eu acho) inserindo antes de excluir.