Início / dba / Perguntas / 75546
Accepted
Chris Jones - Belgium
Asked: 2014-09-03 23:24:58 +0800 CST

Banco de dados SQL Script muito, muito lento (manipulação de strings acima de 700k linhas)

  • 772

Eu tenho uma tabela contendo 700.000 linhas contendo um campo de id de string com valores como rec-232276-dup-0 e rec-354240-org. O rec- é constante, mas as outras partes do id podem mudar.

Quero dividir essa string para ter apenas a parte inteira e um valor booleano, dependendo se a próxima parte for igual a dup ou org (1 para dup, 0 para org) que inserirei de volta na tabela.

Eu escrevi o seguinte loop para fazer isso e funcionalmente funciona bem, mas quando eu o executo nas 700.000 linhas completas, leva um tempo excessivamente longo (+12 horas e contando).

O que fiz de errado para que demorasse tanto? É a manipulação de string que causa isso? O que posso fazer para melhorar isso?

Obrigado pela ajuda.

Meu roteiro é o seguinte:

select id
into #ControlTable 
from [dbo].[original_test_dataset]

declare @TableID varchar(20)

while exists (select * from #ControlTable)
begin

   select @TableID = (select top 1 id
                    from #ControlTable
                    order by id asc)

   declare @duplicate bit
   declare @id_only varchar(10)

   --1. Find id only
   -- Trim off rec-
   set @id_only = REPLACE(@TableID,'rec-','')
   -- Find position of first - and then take everything before it
   set @id_only = LEFT(@id_only,CHARINDEX('-',@id_only,0))
   set @id_only = REPLACE(@id_only,'-','')
   UPDATE original_test_dataset set id_only = @id_only WHERE id = @TableID;

   --2. Find if duplicate
   IF(PATINDEX('%dup%',@TableID) = 0)   
        BEGIN
           -- No duplicate so original file
           UPDATE original_test_dataset set duplicate = 0 WHERE id = @TableID;
        END
   ELSE
       BEGIN
           -- Duplicate
           UPDATE original_test_dataset set duplicate = 1 WHERE id = @TableID;
       END

   delete #ControlTable
   where id = @TableID
END

drop table #ControlTable

Definição de tabela adicionada conforme solicitado:

CREATE TABLE [dbo].[original_test_dataset](
    [id] [varchar](50) NULL,
    [ FirstName] [varchar](50) NULL,
    [ LastName] [varchar](50) NULL,
    [ Phone1] [varchar](50) NULL,
    [ Phone2] [varchar](50) NULL,
    [ Phone3] [varchar](50) NULL,
    [ No] [varchar](50) NULL,
    [ Road] [varchar](50) NULL,
    [ Village] [varchar](50) NULL,
    [ Town] [varchar](50) NULL,
    [ PC] [varchar](50) NULL,
    [ County] [varchar](50) NULL,
    [ DOB] [varchar](50) NULL,
    [id_only] [varchar](10) NULL,
    [duplicate] [bit] NULL
) ON [PRIMARY]

GO

/****** Object:  Index [PK_ORIGINAL_TEST_DATASET_ID]    Script Date: 03/09/2014  07:47:19 ******/
CREATE CLUSTERED INDEX [PK_ORIGINAL_TEST_DATASET_ID] ON [dbo].[original_test_dataset]
(
    [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
sql-server performance

2 respostas

  1. Best Answer

    Você tem uma pergunta sobre desempenho, então recomendo que você leia Como analisar o desempenho do SQL Server para começar.

    Quanto ao seu roteiro: você está fazendo isso o mais devagar possível. Reme por reme dolorosamente lento. Nem mesmo um cursor lento, é pior que um cursor lento. O que você tem aqui é uma única atualização:

    update original_test_dataset
set duplicate= case when PATINDEX('%dup%', id)=0 then 0 else 1 end,
 id_only = REPLACE((LEFT(REPLACE(id,'rec-',''), CHARINDEX('-',REPLACE(id,'rec-',''),0)),'-','');

    É isso, nada mais.

    O que você tem no script original é lento porque:

    • ele opera linha por linha, em vez de conjuntos. O SQL foi projetado para manipular conjuntos, não linhas.
    • ele realiza muitas gravações em confirmações independentes, resultando em enormes esperas de confirmação de liberação. A confirmação em lote deve ser usada.
    • ele executa várias varreduras não indexadas de ponta a ponta #ControlTable( cada operação #ControlTableé uma varredura não indexada de ponta a ponta, deveria ter um índice idativado, sim #tabelas temporárias precisam de índices)
    • ele faz duas atualizações por linha em vez de uma atualização de dois campos
    • Não sei se o acesso a original_test_datasetestá otimizado de alguma forma, na falta de um índice idprovavelmente não está.

    declare @TableID varchar(20)vs. [id] [varchar](50) NULL: você está truncando dados silenciosamente. Basicamente, seu script corrompe a tabela.

    • 5

  2. Pensei que esta poderia ser uma oportunidade para um uso atrevido de PARSENAME . Um pouco menos intuitivo, mas um pouco mais rápido (do cache frio):

    ;WITH cte AS
(
SELECT REPLACE( REVERSE( id ), '-', '.' ) id, duplicate, id_only
FROM dbo.original_test_dataset
)
UPDATE cte
SET duplicate = CASE PARSENAME( id, 3 ) WHEN 'pud' THEN 1 ELSE 0 END,
    id_only = REVERSE( PARSENAME( id, 2 ) )

    insira a descrição da imagem aqui

    • 1

relate perguntas