Início / dba / Perguntas / 118898
Accepted
Greg Bala
Asked: 2015-10-23 05:57:11 +0800 CST

Consulta de atualização simples, mas problemática

  • 772

Eu tenho uma atualização/consulta bastante simples, que tem me causado muita dor ao longo dos anos.

na forma mais simples é:

update VillageSemaphore
set TimeStamp = getdate() 
        where VillageID in (@X, @Y)

No entanto, em alguns processos armazenados, a consulta também inclui esta subconsulta "OR VillageID in (...)"

update VillageSemaphore
set TimeStamp = getdate() 
        where VillageID in (@X, @Y)

        OR VillageID in  ( -- this subquery can return many rows, many different VillageIDs
        select VSU.SupportingVillageID 
        from VillageSupportUnits VSU
        where SupportedVillageID = @Z       
            and VSU.UnitCount <> 0
            )

Observe que este OR pode retornar muitos IDs de vila, não apenas um, @Z. Esta versão da consulta, às vezes, é executada por muito tempo. Nenhuma reconstrução de índice, a reconstrução de estatísticas ajuda. Ele é executado lentamente quando o conteúdo da tabela Villages é excluído e repovoado. Nesse caso, a contagem de linhas seria de apenas algumas centenas de linhas. Eu nunca descobri por que isso acontece e sempre vivi com isso.

No entanto, recentemente eu estava olhando para o plano de consulta:

insira a descrição da imagem aqui

Parece que o número estimado de linhas (4000) é enorme em comparação com o número real de linhas (2).

Eu criei esta estatística, mas não ajuda

CREATE STATISTICS [stat_x] ON [VillageSU]([UnitCount], [VillageID])

ENTÃO MINHA PERGUNTA : alguma sugestão de por que isso poderia ser e o que eu poderia fazer para melhorar isso?

para referência, a tabela se parece com isso:

CREATE TABLE VillageSemaphore(
    VillageID    int         NOT NULL,
    TimeStamp    datetime    NOT NULL,
    CONSTRAINT PK97 PRIMARY KEY CLUSTERED (VillageID)
)

ATUALIZAÇÃO: Experimentando esta versão da consulta conforme sugerido por srutzky

CREATE TABLE #VillagesToLock (VillageID INT NOT NULL);
insert into #VillagesToLock values (@X)
insert into #VillagesToLock values (@Y)
insert into #VillagesToLock select VSU.SupportingVillageID 
        from VillageSupportUnits VSU
        where SupportedVillageID = @Z       
            and VSU.UnitCount <> 0

update VillageSemaphore set TimeStamp = getdate() 
    where VillageID in (select VillageID from #VillagesToLock)

este é o resultado até agora: http://screencast.com/t/96KafTPoNGM - o plano de consulta parece melhor.

O custo da consulta também caiu de 3% para 1%, o que parece bom. 3% pode não parecer muito, mas este é um procedimento armazenado de 2500 linhas!

PERGUNTA : Não posso tornar #VillagesToLock.VillageID um PK, pois não é exclusivo. E espero que o #VillagesToLock normalmente não tenha mais do que 2 a 10 linhas. VillageSemaphore pode ter muitos milhares de linhas. Vale a pena colocar um índice em #VillagesToLock neste caso?

ATUALIZAÇÃO 24 DE NOVEMBRO Eu implementei esta alternativa insira a descrição da imagem aqui

O plano de consulta parece muito melhor insira a descrição da imagem aqui

Obrigado a todos que também o tempo para me ajudar!

sql-server sql-server-2012

3 respostas

  1. Embora eu não esteja convencido de que isso é um problema com a própria consulta (você verificou o bloqueio quando ela está lenta? você verificou o(s) tipo(s) de espera ocorrendo durante a execução) INe ORpode ser um padrão problemático para otimizar para . Você já pensou em dividir isso em várias declarações?

    UPDATE dbo.VillageSemaphoreset 
  SET [TimeStamp] = GETDATE() -- TimeStamp is a terrible column name btw 
  WHERE VillageID = @X;

UPDATE dbo.VillageSemaphoreset 
  SET [TimeStamp] = GETDATE()
  WHERE VillageID = @Y;

IF (whatever condition leads you to "sometimes add this OR")
BEGIN
  UPDATE v 
    SET [TimeStamp] = GETDATE()
    FROM dbo.VillageSemaphoreset AS v
    WHERE VillageID = @Z
    AND EXISTS 
    (
      SELECT 1 FROM dbo.VillageSU AS vs
      WHERE vs.VillageID = v.VillageID
    );
END

    Isso pode resolver o problema de estimativa, mas concordo com Max, uma estatística com uma coluna inicial de UnitCountnão ajudará nas estimativas para essas consultas de qualquer maneira.

    • 6
  2. Best Answer

    Como alternativa, você também pode criar uma tabela temporária local de forma que o UPDATE use um INNER JOIN:

    CREATE TABLE #VillageIDsToUpdate (VillageID INT NOT NULL PRIMARY KEY);

INSERT INTO #VillageIDsToUpdate (VillageID) VALUES (@X);
INSERT INTO #VillageIDsToUpdate (VillageID) VALUES (@Y);
IF (@Z IS NOT NULL)
BEGIN
  INSERT INTO #VillageIDsToUpdate (VillageID)
    SELECT SUVillageID
    FROM   VillageSU
    WHERE  VillageID = @Z;
END;

UPDATE vs
SET    vs.TimeStamp = GETDATE()
FROM   VillageSemaphore vs
INNER JOIN #VillageIDsToUpdate ids
        ON ids.VillageID = vs.VillageID;

    ATUALIZAR:

    Acabei de pensar em algo que pode ajudar a tornar a filtragem de duplicatas mais eficiente: que tal usar a IGNORE_DUP_KEYconfiguração no PK? Por exemplo:

    CREATE TABLE #VillageIDsToUpdate (VillageID INT NOT NULL PRIMARY KEY
                                            WITH (IGNORE_DUP_KEY = ON));

    Se você fizer isso, o seguinte funcionará conforme desejado:

    INSERT INTO #VillageIDsToUpdate (VillageID) VALUES (1);
INSERT INTO #VillageIDsToUpdate (VillageID)
  SELECT tmp.val
  FROM   (VALUES (1), (2), (3), (3)) tmp(val);

SELECT * FROM #VillageIDsToUpdate;

    Retorna:

    VillageID
---------
1
2
3

    E isso significa que você pode fazer as INSERTinstruções sugeridas acima sem precisar adicionar DISTINCTou fazer qualquer consulta secundária para remover duplicatas :-).

    • 3

  3. Você tem uma chave/índice no SUVillageID no VillageSU? Se não, você vai querer adicionar isso. Além disso, você já tentou isso:

    with ctesuv as
    (select SUVillageID as VillageID
        from VillageSU 
        where VillageID = @Z -- if @Z itself is a list of values, you want an `IN` here
    ),
update VillageSemiphore
    set [TimeStamp] = getdate() 
        where VillageID in (@X, @Y, (select * from ctesuv))

    nota: TimeStamp é uma palavra-chave reservada no Access e também pode estar no servidor SQL.

    ou

    update VillageSemiphore
SET [TimeStamp] = getdate()
   WHERE VillageID in (@X,@Y) OR 
        EXISTS (SELECT 1 FROM (
            select SUVillageID from VillageSU 
            where VillageID = @Z)
        )

    e quanto a

    update VillageSemiphore as VS
SET VS.[TimeStamp] = getdate()
    WHERE EXISTS (SELECT 1 FROM (
        SELECT TOP 1
            VS.VillageID, SU.SUVillageID
        FROM VillageSU as SU
        WHERE
            (SU.VillageID = @Z AND SU.SUVillageID = VS.VillageID)
            OR (VS.VillageID IN (@X,@Y)) -- this would cross-join to all rows in SU...the top1 may limit that, but you may need some alternate logic here. I'll have to think a bit more about this
    )
    • 1

relate perguntas