Melhoria de desempenho Aplicação externa

Você pode eliminar a classificação, se desejar, embora seja difícil dizer se isso melhorará o desempenho da consulta. A chave é como você construiu sua ORDER BYcláusula junto com as condições de pesquisa. Se houver alguma linha que corresponda x.sedolcode = b.breakdowncode, você deseja pegar essa linha, caso contrário, você vai para a próxima condição. Com os índices corretos, podemos evitar a classificação dividindo os arquivos APPLY. O otimizador de consulta do SQL Server realmente dá uma boa dica porque transforma suas ORcondições em UNIONs.

Vou criar um exemplo limitado para mostrar a forma geral do plano de consulta que procuro. Também assumirei que a PfPortfolioHoldingClassificationFtidtabela tem uma chave primária e uma chave de cluster em uma PKcoluna. Aqui estão meus dados de teste:

CREATE TABLE #BreakdownSet (
    BreakdownClassificationId BIGINT NOT NULL,
    breakdowncode VARCHAR(10) NULL,
    sedolcode VARCHAR(10) NULL,
    isincode VARCHAR(10) NULL
);

INSERT INTO #BreakdownSet
SELECT 
  t.RN
, CASE WHEN RN % 10 = 1 THEN t.RN ELSE NULL END
, CASE WHEN RN % 10 = 4 THEN t.RN ELSE NULL END
, CASE WHEN RN % 10 = 7 THEN t.RN ELSE NULL END
FROM
(
    SELECT TOP (1500000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t;

CREATE TABLE dbo.PfPortfolioHoldingClassificationFtid (
    PK BIGINT NOT NULL,
    isincode VARCHAR(10) NOT NULL,
    sedolcode VARCHAR(10) NOT NULL,
    ClassificationDate DATE NOT NULL,
    OTHER_COLUMN VARCHAR(200) NOT NULL,
    PRIMARY KEY (PK)
);

INSERT INTO dbo.PfPortfolioHoldingClassificationFtid WITH (TABLOCK)
SELECT 
  t.RN
, t.RN
, t.RN
, DATEADD(DAY, t.rn / 100, '20170101')
, REPLICATE('OTHER', 40)
FROM
(
    SELECT TOP (1500000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t;

CREATE INDEX isin_date ON dbo.PfPortfolioHoldingClassificationFtid (isincode, ClassificationDate);

CREATE INDEX sedol_date ON dbo.PfPortfolioHoldingClassificationFtid (sedolcode, ClassificationDate);

Aqui está sua consulta com minhas definições de tabela:

SELECT b.breakdownclassificationid, 
       k.isincode, 
       k.sedolcode, 
       classificationdate, 
       other_column 
FROM   #breakdownset b 
       OUTER apply (SELECT TOP 1 isincode, 
                                 sedolcode, 
                                 classificationdate, 
                                 other_column 
                    FROM   dbo.pfportfolioholdingclassificationftid x WITH ( 
                           nolock) 
                    WHERE  ( x.isincode > '' 
                             AND x.isincode = b.breakdowncode ) 
                            OR ( x.sedolcode > '' 
                                 AND x.sedolcode = b.breakdowncode ) 
                            OR ( x.sedolcode > '' 
                                 AND x.sedolcode = b.sedolcode ) 
                            OR ( x.isincode > '' 
                                 AND x.isincode = b.isincode ) 
                    ORDER  BY CASE 
                                WHEN x.sedolcode = b.breakdowncode THEN 1 
                                WHEN x.isincode = b.breakdowncode THEN 2 
                                WHEN x.sedolcode = b.sedolcode THEN 3 
                                WHEN x.isincode = b.isincode THEN 4 
                                ELSE 5 
                              END, 
                              classificationdate DESC) k; 

Não surpreendentemente eu recebo um plano diferente do seu, mas o tipo ainda tem um alto custo estimado. E se dividirmos APPLYem quatro partes e cada APPLYuma retornar apenas a chave primária da tabela? Se tivermos um índice de cobertura para cada um APPLY, podemos encontrar a chave primária da linha correspondente com no máximo quatro buscas de índice. Nenhuma classificação é necessária. Também podemos pular as buscas que não precisamos adicionando filtros nos APPLYs, mas isso não é um comportamento garantido. Aqui está uma maneira de escrevê-lo:

SELECT b.breakdownclassificationid, 
       k.isincode, 
       k.sedolcode, 
       classificationdate, 
       other_column 
FROM   #breakdownset b 
       OUTER apply (SELECT TOP 1 pk 
                    FROM   dbo.pfportfolioholdingclassificationftid x 
                    WHERE  x.sedolcode = b.breakdowncode 
                    ORDER  BY classificationdate DESC) a1 
       OUTER apply (SELECT TOP 1 pk 
                    FROM   dbo.pfportfolioholdingclassificationftid x 
                    WHERE  x.isincode = b.breakdowncode 
                           AND a1.pk IS NOT NULL 
                    ORDER  BY classificationdate DESC) a2 
       OUTER apply (SELECT TOP 1 pk 
                    FROM   dbo.pfportfolioholdingclassificationftid x 
                    WHERE  x.sedolcode = b.sedolcode 
                           AND a2.pk IS NOT NULL 
                    ORDER  BY classificationdate DESC) a3 
       OUTER apply (SELECT TOP 1 pk 
                    FROM   dbo.pfportfolioholdingclassificationftid x 
                    WHERE  x.isincode = b.isincode 
                           AND a3.pk IS NOT NULL 
                    ORDER  BY classificationdate DESC) a4 
       LEFT OUTER JOIN dbo.pfportfolioholdingclassificationftid k 
                    ON k.pk = COALESCE(a1.pk, a2.pk, a3.pk, a4.pk);

Com 1,5 milhão de linhas na tabela temporária, o pior caso possível deve ser 6 milhões de buscas de índice não clusterizado e 1,5 milhão de buscas de índice clusterizado.

A consulta é executada em dois segundos na minha máquina. O tempo de execução não importa porque tenho dados sem sentido. No entanto, não há um tipo no plano. Carreguei o plano de consulta para Paste The Plan . Você deve considerar fazer isso também para perguntas futuras. Aqui está também uma captura de tela do plano real:

A consulta que você tem faz uma junção de loop aninhada e não fica paralela. Com LOOP JOINe MAXDOP 1dicas a consulta termina em sete segundos na minha máquina. Aqui está o plano e aqui está uma captura de tela: