Início / dba / Perguntas / 104192
Accepted
James Z
Asked: 2015-06-16 20:52:43 +0800 CST

Problema de otimização com função definida pelo usuário

  • 772

Tenho um problema para entender por que o servidor SQL decide chamar a função definida pelo usuário para cada valor na tabela, embora apenas uma linha deva ser buscada. O SQL real é muito mais complexo, mas consegui reduzir o problema a isso:

select  
    S.GROUPCODE,
    H.ORDERCATEGORY
from    
    ORDERLINE L
    join ORDERHDR H on H.ORDERID = L.ORDERID
    join PRODUCT P  on P.PRODUCT = L.PRODUCT    
    cross apply dbo.GetGroupCode (P.FACTORY) S
where   
    L.ORDERNUMBER = 'XXX/YYY-123456' and
    L.RMPHASE = '0' and
    L.ORDERLINE = '01'

Para esta consulta, o SQL Server decide chamar a função GetGroupCode para cada valor único que existe na tabela PRODUCT, mesmo que a estimativa e o número real de linhas retornadas de ORDERLINE sejam 1 (é a chave primária):

Plano de consulta

Mesmo plano no explorador de planos mostrando as contagens de linhas:

Explorador de planos Tabelas:

ORDERLINE: 1.5M rows, primary key: ORDERNUMBER + ORDERLINE + RMPHASE (clustered)
ORDERHDR:  900k rows, primary key: ORDERID (clustered)
PRODUCT:   6655 rows, primary key: PRODUCT (clustered)

O índice que está sendo usado para a varredura é:

create unique nonclustered index PRODUCT_FACTORY on PRODUCT (PRODUCT, FACTORY)

A função é, na verdade, um pouco mais complexa, mas a mesma coisa acontece com uma função fictícia de várias instruções como esta:

create function GetGroupCode (@FACTORY varchar(4))
returns @t table(
    TYPE        varchar(8),
    GROUPCODE   varchar(30)
)
as begin
    insert into @t (TYPE, GROUPCODE) values ('XX', 'YY')
    return
end

Consegui "consertar" o desempenho forçando o servidor SQL a buscar o 1 produto principal, embora 1 seja o máximo que pode ser encontrado:

select  
    S.GROUPCODE,
    H.ORDERCAT
from    
    ORDERLINE L
    join ORDERHDR H
        on H.ORDERID = M.ORDERID
    cross apply (select top 1 P.FACTORY from PRODUCT P where P.PRODUCT = L.PRODUCT) P
    cross apply dbo.GetGroupCode (P.FACTORY) S
where   
    L.ORDERNUMBER = 'XXX/YYY-123456' and
    L.RMPHASE = '0' and
    L.ORDERLINE = '01'

Em seguida, a forma do plano também muda para algo que eu esperava que fosse originalmente:

Plano de consulta com top

Eu também pensei que o índice PRODUCT_FACTORY sendo menor que o índice clusterizado PRODUCT_PK teria um efeito, mas mesmo forçando a consulta a usar PRODUCT_PK, o plano ainda é o mesmo do original, com 6655 chamadas para a função.

Se eu deixar ORDERHDR completamente de fora, o plano começa com um loop aninhado entre ORDERLINE e PRODUCT primeiro, e a função é chamada apenas uma vez.

Gostaria de entender qual pode ser o motivo disso já que todas as operações são feitas usando chaves primárias e como corrigir caso aconteça em uma consulta mais complexa que não pode ser resolvida com tanta facilidade.

Editar: criar instruções de tabela:

CREATE TABLE dbo.ORDERHDR(
    ORDERID varchar(8) NOT NULL,
    ORDERCATEGORY varchar(2) NULL,
    CONSTRAINT ORDERHDR_PK PRIMARY KEY CLUSTERED (ORDERID)
)

CREATE TABLE dbo.ORDERLINE(
    ORDERNUMBER varchar(16) NOT NULL,
    RMPHASE char(1) NOT NULL,
    ORDERLINE char(2) NOT NULL,
    ORDERID varchar(8) NOT NULL,
    PRODUCT varchar(8) NOT NULL,
    CONSTRAINT ORDERLINE_PK PRIMARY KEY CLUSTERED (ORDERNUMBER,ORDERLINE,RMPHASE)
)

CREATE TABLE dbo.PRODUCT(
    PRODUCT varchar(8) NOT NULL,
    FACTORY varchar(4) NULL,
    CONSTRAINT PRODUCT_PK PRIMARY KEY CLUSTERED (PRODUCT)
)
sql-server sql-server-2005

2 respostas

  1. Best Answer

    Existem três razões técnicas principais para você obter o plano que você faz:

    1. A estrutura de custo do otimizador não tem suporte real para funções não sequenciais. Ele não faz nenhuma tentativa de olhar dentro da definição da função para ver o quão caro pode ser, apenas atribui um custo fixo muito pequeno e estima que a função produzirá 1 linha de saída cada vez que for chamada. Ambas as suposições de modelagem são muitas vezes completamente inseguras. A situação melhorou ligeiramente em 2014 com o novo estimador de cardinalidade habilitado, pois a estimativa fixa de 1 linha foi substituída por uma estimativa fixa de 100 linhas. No entanto, ainda não há suporte para custear o conteúdo de funções não inline.
    2. O SQL Server inicialmente recolhe as junções e as aplica em uma única junção lógica n-ária interna. Isso ajuda o otimizador a raciocinar sobre juntar pedidos mais tarde. A expansão da junção n-ária única em ordens de junção candidatas vem depois e é amplamente baseada em heurísticas. Por exemplo, junções internas vêm antes de junções externas, tabelas pequenas e junções seletivas antes de tabelas grandes e junções menos seletivas e assim por diante.
    3. Quando o SQL Server executa otimização baseada em custo, ele divide o esforço em fases opcionais para minimizar as chances de gastar muito tempo otimizando consultas de baixo custo. Existem três fases principais, pesquisa 0, pesquisa 1 e pesquisa 2. Cada fase tem condições de entrada e as fases posteriores permitem mais explorações do otimizador do que as anteriores. Sua consulta se qualifica para a fase de pesquisa menos capaz, fase 0. Um plano de custo baixo o suficiente é encontrado lá para que os estágios posteriores não sejam inseridos.

    Dada a pequena estimativa de cardinalidade atribuída à UDF aplicável, a heurística de expansão de junção n-ária infelizmente a reposiciona mais cedo na árvore do que você desejaria.

    A consulta também se qualifica para otimização de pesquisa 0 em virtude de ter pelo menos três junções (incluindo as aplicáveis). O plano físico final que você obtém, com a varredura de aparência estranha, é baseado na ordem de junção deduzida heuristicamente. Seu custo é baixo o suficiente para que o otimizador considere o plano "bom o suficiente". A estimativa de baixo custo e a cardinalidade da UDF contribuem para esse acabamento precoce.

    A pesquisa 0 (também conhecida como a fase de processamento da transação) visa consultas do tipo OLTP de baixa cardinalidade, com planos finais que geralmente apresentam junções de loops aninhados. Mais importante, a pesquisa 0 executa apenas um subconjunto relativamente pequeno das habilidades de exploração do otimizador. Este subconjunto não inclui puxar e aplicar a árvore de consulta em uma junção (regra PullApplyOverJoin). Isso é exatamente o que é necessário no caso de teste para reposicionar o UDF apply acima das junções, para aparecer por último na sequência de operações (por assim dizer).

    Há também um problema em que o otimizador pode decidir entre a junção de loops aninhados ingênuos (predicado de junção na própria junção) e uma junção indexada correlacionada (aplicar) em que o predicado correlacionado é aplicado no lado interno da junção usando uma busca de índice. O último é geralmente a forma de plano desejada, mas o otimizador é capaz de explorar ambos. Com estimativas de custeio e cardinalidade incorretas, pode-se optar pela junção NL não aplicável, como nos planos enviados (explicando a varredura).

    Portanto, existem vários motivos de interação envolvendo vários recursos gerais do otimizador que normalmente funcionam bem para encontrar bons planos em um curto período de tempo sem usar recursos excessivos. Evitar qualquer um dos motivos é suficiente para produzir a forma de plano 'esperada' para a consulta de amostra, mesmo com tabelas vazias:

    Planeje tabelas vazias com pesquisa 0 desativada

    There is no supported way to avoid search 0 plan selection, early optimizer termination, or to improve the costing of UDFs (aside from the limited enhancements in the SQL Server 2014 CE model for this). This leaves things like plan guides, manual query rewrites (including the TOP (1) idea or using intermediate temporary tables) and avoiding poorly-costed 'black boxes' (from a QO point of view) like non-inline functions.

    Reescrever CROSS APPLYcomo OUTER APPLYtambém pode funcionar, pois atualmente evita alguns dos primeiros trabalhos de colapso de junção, mas você deve ter cuidado para preservar a semântica da consulta original (por exemplo, rejeitar quaisquer NULLlinhas estendidas que possam ser introduzidas, sem que o otimizador seja reduzido de volta para um aplicação cruzada). Você precisa estar ciente, porém, de que não há garantia de que esse comportamento permaneça estável; portanto, lembre-se de testar novamente esses comportamentos observados sempre que aplicar um patch ou atualizar o SQL Server.

    No geral, a solução certa para você depende de uma variedade de fatores que não podemos julgar por você. No entanto, gostaria de encorajá-lo a considerar soluções com garantia de sempre funcionar no futuro e que funcionem com (em vez de contra) o otimizador sempre que possível.

    • 30

  2. Parece que esta é uma decisão baseada em custo do otimizador, mas bastante ruim.

    Se você adicionar 50.000 linhas ao PRODUCT, o otimizador acha que a varredura é muito trabalhosa e fornece um plano com três buscas e uma chamada para o UDF.

    O plano que recebo para 6655 linhas no PRODUCT

    insira a descrição da imagem aqui

    Com 50.000 linhas no PRODUCT, obtenho esse plano.

    insira a descrição da imagem aqui

    Acho que o custo de chamar o UDF é totalmente subestimado.

    Uma solução alternativa que funciona bem nesse caso é alterar a consulta para usar a aplicação externa no UDF. Eu obtenho o bom plano, não importa quantas linhas existam na tabela PRODUCT.

    select  
    S.GROUPCODE,
    H.ORDERCATEGORY
from    
    ORDERLINE L
    join ORDERHDR H on H.ORDERID = L.ORDERID
    join PRODUCT P  on P.PRODUCT = L.PRODUCT    
    outer apply dbo.GetGroupCode (P.FACTORY) S
where   
    L.ORDERNUMBER = 'XXX/YYY-123456' and
    L.RMPHASE = '0' and
    L.ORDERLINE = '01' and
    S.GROUPCODE is not null

    insira a descrição da imagem aqui

    A melhor solução no seu caso é provavelmente obter os valores necessários em uma tabela temporária e, em seguida, consultar a tabela temporária com uma aplicação cruzada à UDF. Dessa forma, você tem certeza de que a UDF não será executada mais do que o necessário.

    select  
    P.FACTORY,
    H.ORDERCATEGORY
into #T
from    
    ORDERLINE L
    join ORDERHDR H on H.ORDERID = L.ORDERID
    join PRODUCT P  on P.PRODUCT = L.PRODUCT
where   
    L.ORDERNUMBER = 'XXX/YYY-123456' and
    L.RMPHASE = '0' and
    L.ORDERLINE = '01'

select  
    S.GROUPCODE,
    T.ORDERCATEGORY
from #T as T
  cross apply dbo.GetGroupCode (T.FACTORY) S

drop table #T

    Em vez de persistir na tabela temporária, você pode usar top()uma tabela derivada para forçar o SQL Server a avaliar o resultado das junções antes que a UDF seja chamada. Basta usar um número muito alto no topo, fazendo com que o SQL Server tenha que contar suas linhas para essa parte da consulta antes de continuar e usar o UDF.

    select S.GROUPCODE,
       T.ORDERCATEGORY
from (
     select top(2147483647)
         P.FACTORY,
         H.ORDERCATEGORY
     from    
         ORDERLINE L
         join ORDERHDR H on H.ORDERID = L.ORDERID
         join PRODUCT P  on P.PRODUCT = L.PRODUCT    
     where   
         L.ORDERNUMBER = 'XXX/YYY-123456' and
         L.RMPHASE = '0' and
         L.ORDERLINE = '01'
     ) as T
  cross apply dbo.GetGroupCode (T.FACTORY) S

    insira a descrição da imagem aqui

    Gostaria de entender qual pode ser o motivo disso já que todas as operações são feitas usando chaves primárias e como corrigir caso aconteça em uma consulta mais complexa que não pode ser resolvida com tanta facilidade.

    Eu realmente não posso responder a isso, mas pensei que deveria compartilhar o que sei de qualquer maneira. Não sei por que uma varredura da tabela PRODUCT é considerada. Pode haver casos em que essa é a melhor coisa a fazer e há coisas sobre como os otimizadores tratam as UDFs que eu não conheço.

    Uma observação extra foi que sua consulta obtém um bom plano no SQL Server 2014 com o novo estimador de cardinalidade. Isso ocorre porque o número estimado de linhas para cada chamada para a UDF é 100 em vez de 1, como no SQL Server 2012 e anteriores. Mas ainda tomará a mesma decisão baseada em custo entre a versão de digitalização e a versão de busca do plano. Com menos de 500 (497 no meu caso) linhas no PRODUCT você consegue a versão scan do plano mesmo no SQL Server 2014.

    • 24

relate perguntas