Início / dba / Perguntas / 152530
Accepted
BanksySan
Asked: 2016-10-18 12:50:38 +0800 CST

Maneira rápida de carregar uma grande quantidade de dados de teste

  • 772

Estou tentando preencher uma tabela com uma carga de dados fictícios para que eu possa otimizar, etc.

Eu tenho o seguinte:

WHILE @RowCount < 3000000
BEGIN

    SELECT @Random = ROUND(@Upper * RAND(), 0)

    INSERT INTO [dbo].[Test]
               ([Id]
               ,[OtherKey]
               ,[Description])
         VALUES
               (@RowCount
               ,@Random
               ,CAST(@Random AS VARCHAR(max)))

    SET @RowCount = @RowCount + 1
END

No entanto, isso parece muito lento.

Existe uma maneira melhor de automatizar o carregamento de linhas semi-aleatórias em uma tabela de banco de dados?

Novo script

Este parece ser bem rápido:

USE [Test]
GO

/****** Object:  Table [dbo].[Test]    Script Date: 17/10/2016 21:22:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

USE [Test]
GO

CREATE TABLE [dbo].[Test](
    [Id] [int] NOT NULL,
    [OtherKey] [int] NOT NULL,
    [Description] [varchar](max) NOT NULL,
    [Time] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

-- Add sample data

DECLARE @RowCount INT
DECLARE @Random INT
DECLARE @Upper INT

SET @Upper = 1000
SET @RowCount = 0

WHILE @RowCount < 1000000
BEGIN

    SELECT @Random = ROUND(@Upper * RAND(), 0)

    INSERT INTO [dbo].[Test]
               ([Id]
               ,[OtherKey]
               ,[Description]
               ,[Time])
         VALUES
               (@RowCount
               ,@Random
               ,CAST(@Random AS VARCHAR(max))
               ,GETDATE())

    SET @RowCount = @RowCount + 1
END

GO

/****** Object:  Index [IX_ID]    Script Date: 17/10/2016 22:18:48 ******/
CREATE CLUSTERED INDEX [IX_ID] ON [dbo].[Test]
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO



/****** Object:  Index [IX_OtherKey]    Script Date: 17/10/2016 21:22:46 ******/
CREATE NONCLUSTERED INDEX [IX_OtherKey] ON [dbo].[Test]
(
    [OtherKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Se alguém tiver alguma maneira mais rápida, ficaria muito feliz em ouvi-la.

sql-server performance

1 respostas

  1. Best Answer

    Aqui estão alguns princípios orientadores para carregar não pequenas quantidades de dados de teste:

    1) Sempre que possível, use o registro mínimo .

    Se você pode evitar gravar dados desnecessários no log de transações, ótimo, por que não fazer isso? Isso exigirá que seu banco de dados de teste use um modelo de recuperação simples. Você também precisa ter cuidado para seguir as regras que permitem obter um registro mínimo. Se você precisar inserir em uma tabela com um índice clusterizado que já contém o sinalizador de rastreamento de dados 610 pode ajudar.

    2) Evite opções DDL de tabela e coluna que contribuem para sobrecarga desnecessária.

    Como você descobriu, às vezes é mais eficiente criar um índice clusterizado depois que os dados são carregados, em vez de antes, embora possa ocorrer de qualquer maneira, dependendo dos dados. Às vezes, criar o índice clusterizado depois pode ser mais rápido simplesmente porque o SQL Server pode inserir dados em paralelo em um heap existente (a partir do SQL Server 2016) e pode criar o índice clusterizado em paralelo. Esta não será uma operação minimamente registrada e exigirá que todos os dados da tabela sejam classificados, portanto, isso pode não funcionar se o seu sistema de desenvolvimento não for grande o suficiente.

    Como você também descobriu, definitivamente crie índices não clusterizados depois de carregar os dados em vez de antes.

    Eu acredito que há alguma sobrecarga para usar o tipo de dados VARCHAR(MAX), então eu evitaria isso se possível.

    3) Evite operações linha por linha sempre que possível. O SQL Server geralmente é mais eficiente com soluções baseadas em conjunto.

    Existem algumas operações linha por linha em seu código. Você está fazendo um loop WHILE e apenas processando uma linha por vez. Você também está criando uma transação para cada linha que está inserindo. Deve haver alguma sobrecarga para criar e confirmar uma transação, certo? Por que pagar isso por cada linha? Se o seu sistema for grande o suficiente, muitas vezes você pode apenas inserir seus dados de teste com uma única consulta, especialmente se você puder usar um registro mínimo.

    4) Use paralelismo quando possível.

    O código que você executa uma linha por vez e não pode aproveitar vários núcleos no servidor.

    Darei a você uma maneira de abordar problemas como esse, mas essa consulta pode não atender exatamente às suas necessidades.

    WITH
L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3),
L5 AS (SELECT 1 AS c FROM L4 A CROSS JOIN L4),
NUMS AS (SELECT 1 AS NUM FROM L5)   
SELECT TOP 1000000
  CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS INT) [ID]
, CAST(t.RAND_VALUE AS INT) [OTHERKEY]
, CAST(t.RAND_VALUE AS VARCHAR(100)) [DESCRIPTION]
, CAST(GETDATE() AS DATETIME) [TIME]
INTO [X_JRO_TEST_RAND_2]
FROM NUMS CROSS JOIN (SELECT ROUND(1000 * RAND(CHECKSUM(NEWID())), 0) RAND_VALUE) t;

    Vou detalhar a consulta para você usando os princípios orientadores listados no início do post.

    1) SELECT INTO cria uma tabela HEAP como parte da inserção. Se o banco de dados tiver um modelo de recuperação simples, essa operação será minimamente registrada. Posso economizar trabalho não gravando dados desnecessários no log de transações.

    2) A tabela não possui nenhum índice antes de carregar os dados. Também estou usando VARCHAR(100) em vez de VARCHAR(MAX).

    3) Para gerar números de forma eficiente, estou usando uma técnica popularizada (provavelmente criada) por Itzik Ben-Gan . Todos os CTEs estão lá para gerar 1.000.000 linhas. Gostaria de verificar o artigo para obter mais detalhes sobre como a abordagem funciona. Para fazer RAND() retornar um valor diferente para cada linha, usei uma das técnicas documentadas neste post de estouro de pilha .

    4) A partir do SQL Server 2014, o otimizador de consulta pode inserir dados em um heap em paralelo ao usar a sintaxe SELECT INTO. A partir do SQL Server 2016, o otimizador de consulta pode inserir dados em um heap em paralelo, mesmo sem a sintaxe SELECT INTO. No meu sistema de teste, a consulta é executada em paralelo.

    Em um sistema de teste aqui, seu código levou 10:30 para um milhão de linhas, mas a consulta acima levou apenas 23 segundos. Medi apenas a etapa de carregamento de dados e não me preocupei com os índices. Vale ressaltar que provavelmente existem maneiras ainda mais eficientes de gerar dados no formato que você deseja e que meu código não possui valores diferentes para a coluna TIME.

    Também vale ressaltar que, se sua solução for executada com rapidez suficiente para seus propósitos, ótimo, vá em frente e mantenha-a. Às vezes, não é necessário obter todo o desempenho do código, especialmente o código executado nos bastidores para fins de desenvolvimento.

    • 6

relate perguntas