Início / dba / Perguntas / 54327
Accepted
Tim Schmelter
Asked: 2013-12-04 02:47:37 +0800 CST

Problema de desempenho com TOP (1) em uma coluna anulável

  • 772

Preciso de ajuda para identificar o motivo de um problema de desempenho.

Na verdade, a consulta a seguir fica em uma função de valor escalar, mas esse não é o motivo do problema, pois precisa do mesmo tempo que a consulta:

SELECT TOP (1) dd.gsp_part_price 
FROM  tabdata 
       INNER JOIN locsl 
               ON tabdata.fisl = locsl.idsl 
       INNER JOIN locgsp 
               ON locsl.figsp = locgsp.idgsp 
       INNER JOIN tabdatadetail dd
               ON dd.fidata = tabdata.iddata 
       INNER JOIN tabsparepart sp 
               ON dd.fisparepart = sp.idsparepart 
WHERE  sp.sparepartname = '1270-3132' 
       AND dd.gsp_part_price IS NOT NULL 
ORDER BY Claim_Submitted_Date DESC

Esta consulta retorna o preço imediatamente para a maioria das peças sobressalentes, mas algumas peças precisam de vários minutos para serem devolvidas NULL. Portanto, esta consulta retorna 940 linhas imediatamente para a mesma parte acima, onde cada gsp_part_priceé NULLo motivo do NULLacima:

SELECT dd.*, sp.* 
FROM tabdatadetail dd 
INNER JOIN tabsparepart sp 
    on dd.fisparepart=sp.idsparepart
WHERE sp.sparepartname='1270-3132'

Por que a primeira consulta precisa de 3 minutos para retornar NULLenquanto a consulta abaixo retorna imediatamente as 940 linhas? Devo admitir que ainda tenho problemas para interpretar os planos de execução.

Observe que a primeira consulta também retorna imediatamente se eu remover o IS NOT NULL CHECK:

dd.gsp_part_price IS NOT NULL

Todas as tabelas envolvidas estão vinculadas por meio de chaves estrangeiras, tentei adicionar um índice no gsp_part_pricequal não teve efeito.

Aqui está o plano de execução da consulta:

plano de execução da consulta

EdittabData : Aqui estão as estatísticas nos índices PK e FK (para a tabela pai ):

DBCC SHOW_STATISTICS('dbo.tabDataDetail','PK_tabDataDetail') WITH STAT_HEADER;
PK_tabDataDetail    Nov  6 2013 10:04AM 64327191    387089  183 1   8   NO 

DBCC SHOW_STATISTICS('dbo.tabDataDetail','IX_fiData') WITH STAT_HEADER;
IX_fiData   Nov  6 2013 10:04AM 64327191    378150  198 0,2441889   4   NO 

DBCC SHOW_STATISTICS('dbo.tabDataDetail','IX_GspPartPrice') WITH STAT_HEADER;
IX_GspPartPrice Dez  3 2013 12:17PM 64788799    64788799    200 0,0002433512    7,065503    NO

O último índice foi adicionado agora, conforme mencionado acima. Portanto, as linhas reais são 64788799, por que a grande diferença entre as linhas estimadas e reais, também, por que há uma diferença entre as linhas reais do plano de consulta e as linhas reais "reais"?

Aqui está o plano completo: http://s000.tinyupload.com/index.php?file_id=86912774678831839436

Editar : do comentário de Mikael Eriksson:

Eu acho que o seu top(1)e order byé o culpado aqui. A varredura de índice está usando o índice Claim_Submitted_Datepara localizar a primeira ocorrência de um valor o mais rápido possível. O SQL Server acredita que você encontrará esse valor após verificar 3.000 linhas. Talvez essa estimativa faça sentido se você tiver 5.000 linhas tabSpareParte quiser apenas uma. O problema é que, se não houver linhas encontradas ou se você tiver uma peça sobressalente que foi enviada recentemente, o SQL Server terá que verificar todo o índice antes de descobrir que não há linhas a serem encontradas.

Embora eu ainda não entenda o porquê, parece que isso é verdade. Se eu remover TOP 1ou alterar para dizer que TOP 1000o resultado vem imediatamente. Alguém tem alguma ideia do que posso fazer? Porque, se várias linhas forem retornadas, só preciso do último preço enviado.

sql-server sql-server-2005

1 respostas

  1. Best Answer

    A consulta leva muito tempo porque não encontra linhas.

    O primeiro plano é configurar, escolha uma linha de tabSparePartonde sparepartname = '1270-3132'. Em seguida, ele segue para a varredura de índice de tabData. Para cada linha buscada, tabDatahá uma busca de índice clusterizado locSLseguida por uma busca de índice clusterizado na tabDataDetailqual também verifica se não é nulo gsp_part_pricee, finalmente, retorna os valores de fiSpareParte GSP_Part_Price. fiSpareParté então comparado com o idSparePartretornado do Index Seek em tabSparePart. Se forem iguais, a consulta termina imediatamente e retorna o arquivo gsp_part_price. Se não forem iguais, a varredura de índice tabDatamove-se para o próximo registro e todas as verificações são executadas novamente. Isso continua acontecendo até que uma linha seja retornada ou todas as linhas tabDatatenham sido processadas.

    Acontece que você obtém um plano de consulta totalmente diferente quando faz top(1000).

    Você pode usar a optimize fordica para fazer o SQL Server pensar que você realmente deseja 1.000 linhas quando precisa apenas de uma.

    Sua função de valor escalar ficaria assim:

    CREATE FUNCTION [dbo].[FunctionName]()
RETURNS INT
AS
BEGIN

  DECLARE @TOP INT
  DECLARE @RET INT

  SET @TOP = 1

  SELECT TOP (@TOP) @RET = dd.gsp_part_price 
  FROM  tabdata 
          INNER JOIN locsl 
                  ON tabdata.fisl = locsl.idsl 
          INNER JOIN locgsp 
                  ON locsl.figsp = locgsp.idgsp 
          INNER JOIN tabdatadetail dd
                  ON dd.fidata = tabdata.iddata 
          INNER JOIN tabsparepart sp 
                  ON dd.fisparepart = sp.idsparepart 
  WHERE  sp.sparepartname = '1270-3132' 
          AND dd.gsp_part_price IS NOT NULL 
  ORDER BY Claim_Submitted_Date DESC 
  OPTION (OPTIMIZE FOR(@TOP = 1000))

  RETURN @RET

END
    • 4

relate perguntas