James Z's questions

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):

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

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:

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)
)