Início / dba / Perguntas / 228041
Accepted
crokusek
Asked: 2019-01-25 17:15:08 +0800 CST

Como obter a aplicação cruzada para operar linha por linha em uma exibição?

  • 772

Temos uma visão otimizada para consultas de item único (200ms sem paralelismo):

select * 
    from OptimizedForSingleObjectIdView e2i
   where ObjectId = 3374700

Também funciona em pequenos conjuntos de ids estáticos (~5). 

select * 
    from OptimizedForSingleObjectIdView e2i
   where ObjectId in (3374700, 3374710, 3374720, 3374730, 3374740);

No entanto, se os objetos vierem de uma fonte externa, ele gerará um plano lento. O plano de execução mostra que a ramificação de execução para a parte de exibição está ignorando o predicado em ObjectId, enquanto no caso original ele os usa para realizar buscas de índice.

select v.*
  from 
     (
       select top 1 ObjectId from Objects
        where ObjectId % 10 = 0
        order by ObjectId
     ) o  
  join OptimizedForSingleObjectIdView v -- (also tried inner loop join)
    on v.ObjectId = o.ObjectId;

Não queremos investir em "dual" otimizando a visão para casos não singulares. Em vez disso, a solução que "procuramos" é chamar repetidamente a visão uma vez por objeto sem recorrer a um SP .

Na maioria das vezes, a solução a seguir chama a exibição linha por linha. Porém não desta vez e nem mesmo para apenas 1 objeto:

select v.*
  from
     (
       select top 1 ObjectId 
         from Objects 
        where ObjectId % 10 = 0 -- non-trivial predicate
        order by ObjectId
     ) o
   cross apply
    (
      select top 2000000000 *
        from OptimizedForSingleObjectIdView v_
       where ObjectId = o.ObjectId 
       order by v_.SomeField
    ) v;

Ao mesmo tempo, pensei que havia uma alegação de que a aplicação cruzada era garantida para execução de linha por linha quando chama uma UDF, mas isso também falhou:

create function FunctionCallingView(@pObjectId bigint)
returns table
as 
return select *
  from OptimizedForSingleObjectIdView 
 where ObjectId = @pObjectId;             

select v.*
  from
     (
       select top 1 ObjectId 
         from Objects 
        where ObjectId % 10 = 0
        order by ObjectId
     ) o
 cross apply FunctionCallingView(o.ObjectId) v

Adicionar opção (forçar ordem) não ajudou - no entanto, já existem duas dicas de hash na exibição. Removê-los temporariamente não ajudou e retarda o caso único.

Aqui está um trecho do plano estimado para o caso lento baseado em função. A estimativa de 1 linha está correta. Mais à direita (não mostrado) é onde há um predicado de busca que não inclui o resultado 1 superior. Isso parece semelhante a outros casos que temos em que valores de sondagem singulares de buscas de tabela não são usados ​​como predicados de busca em outros lugares.

insira a descrição da imagem aqui

sql-server execution-plan

1 respostas

  1. Best Answer

    Não é possível garantir totalmente a avaliação da exibição por linha da consulta externa, sem usar algo que introduza um novo escopo de execução T-SQL, por exemplo, uma BEGIN...ENDfunção com valor de tabela não-inline (multi-statement, ). Este é praticamente o conselho dado em resposta à sua pergunta anterior Como usar dicas de mesclagem para isolar consultas complexas no SQL Server .

    Ao mesmo tempo, pensei que havia uma alegação de que a aplicação cruzada era garantida para execução de linha por linha quando chama uma UDF

    Isso não se aplica a funções com valor de tabela inline , pois a definição é expandida na consulta de chamada antes do início da otimização.

    Dito isto, há algumas coisas que você pode fazer para encorajar fortemente o resultado desejado.

    O plano de execução mostra que a ramificação de execução para a parte da visualização está ignorando o predicado on ObjectIdenquanto no caso original ele os usa para realizar buscas de índice.

    Você espera que os ObjectIdvalores sejam avaliados "dentro da exibição" usando buscas de índice para cada linha de condução. Este é o estilo de execução de junção (aplicação) de loops aninhados correlacionados. Observe que o uso do APPLYelemento de linguagem T-SQL não garante que a execução física use o estilo apply.

    Parece que o SQL Server está optando por executar com os ObjectIdvalores testados no operador Nested Loops Join . Este é um padrão de execução de junção de loops aninhados não correlacionados ou ingênuos.

    Provavelmente, isso é causado pelas dicas de junção que você está usando dentro da exibição. As dicas de junção geralmente devem ser evitadas, porque restringem muito a liberdade do otimizador, e não apenas para o tipo físico de junção. Em particular, as dicas de junção também forçam a ordem das junções para toda a consulta (como se você tivesse usado uma FORCE ORDERdica) e impedem várias otimizações relacionadas ao posicionamento e estratégia de agregação e muito mais.

    Se você realmente precisa ter dicas de junção dentro de uma visão (algo que eu sugiro fortemente que você evite em geral), você pode achar que a maneira mais confiável de obter a forma do plano que você deseja é:

    1. Crie uma função em linha ( RETURNS TABLE) usando a definição de exibição (não referenciando a exibição).
    2. Forneça @ObjectIdcomo parâmetro para a função.
    3. Colocar um predicado usando o parâmetro dentro do corpo da função de uma maneira que torne uma busca de índice mais provável.
    4. Uma FORCESEEKdica de tabela dentro da função pode ser usada se realmente todo uso resultar em uma busca.
    5. Chame a nova função inline usando APPLY.

    Eu geralmente não gosto de usar sintaxes e dicas específicas na tentativa de forçar uma certa forma de plano físico. Você pode ter um sucesso mais geral parametrizando as consultas e garantindo a forma do plano usando um guia de plano.

    • 10

relate perguntas