Como posso melhorar o desempenho do procedimento armazenado?

Aqui está uma tentativa de reescrever a função no procedimento:

CREATE PROCEDURE [dbo].[ImproveProcedure]
(
  @port varchar(50)
 ,@portdate datetime
)
AS   
BEGIN
    DECLARE @intdate datetime;
    SELECT @intdate = MAX(rate_date)
      FROM Interestrate
     WHERE rate_type = 'Zero'
       AND rate_date <= @portdate
    ;

    IF (OBJECT_ID('tempdb..#tmp_fx') IS NOT NULL) DROP TABLE #tmp_fx;
    CREATE TABLE #tmp_fx
         ( currency1 varchar(10)
          ,currency2 varchar(10)
          ,month int
          ,rate_date datetime
          ,rate float
          ,CONSTRAINT PK_tmp_fx PRIMARY KEY (currency2, month)
         );

    -- NOTE: currency1 not needed in PK, as it's always 'USD' - may want to change if add'l values are added.

    -- Get base currency pairs, months, and max dates
    INSERT INTO #tmp_fx
    SELECT currency1, currency2, forward_month, MAX(rate_date)
      FROM (SELECT currency1, currency2, forward_month, rate_date
              FROM fx
             WHERE currency1 = 'USD'
               AND rate_date <= @portdate
            UNION ALL
            SELECT currency2, currency1, forward_month, rate_date
              FROM fx
             WHERE currency2 = 'USD'
               AND rate_date <= @portdate
           ) sq
     GROUP BY currency1, currency2, forward_month
    ;

    -- Set rates based on previous query:
    --   Step 1: rates where currency1 = currency1 -- multiply
    UPDATE t
       SET rate = fx.Rate
      FROM #tmp_fx t
             INNER JOIN fx ON (    t.currency1 = fx.currency1
                               AND t.currency2 = fx.currency2
                               AND t.month = fx.forward_month
                               AND t.rate_date = fx.rate_date
                              )
    ;

    --   Step 2: rates where currency2 = currency1 -- divide
    UPDATE t
       SET rate = 1 / fx.Rate
      FROM #tmp_fx t
             INNER JOIN fx ON (    t.currency2 = fx.currency1
                               AND t.currency1 = fx.currency2
                               AND t.month = fx.forward_month
                               AND t.rate_date = fx.rate_date
                              )
     WHERE fx.rate IS NULL
    ;

    -- NOTE - unset rates left NULL - will force to 1 in final query


    UPDATE tr
       SET NonDiscount = null
          ,Discount = null
          ,NonDiscountTcurr = null
          ,DiscountTcurr = null
          ,NonDiscountNew = null
          ,DiscountNew = null    
     WHERE port = @port
       AND portdate = @portdate
    ;

    UPDATE tr
       SET NonDiscount =  CASE WHEN p1.Sub <> 'Option'
                            THEN CASE sss_ind
                                   WHEN 'P' then -exposure
                                   ELSE          exposure
                                 END
                            ELSE CASE WHEN buysell IN ('A', 'S')
                                   THEN -vol * markprice
                                   ELSE vol * markprice
                                 END
                          END
                        * ISNULL(fx.Rate, CAST(1 as float))
          ,NonDiscountTcurr = CASE WHEN p1.Sub <> 'Option'
                                THEN CASE sss_ind
                                       WHEN 'P' then -exposure
                                       ELSE          exposure
                                     END
                                ELSE CASE WHEN buysell IN ('A', 'S')
                                       THEN -vol * markprice
                                       ELSE vol * markprice
                                     END
                              END
      FROM tr
             INNER JOIN Phy p1 ON (tr.trans = p1.trans)
             LEFT  JOIN #tmp_fx fx ON (    'USD' = fx.currency1
                                       AND ccd = fx.currency2
                                       AND portdate = fx.rate_date
                                       AND  CASE 
                                              WHEN DATEDIFF(month, portdate, COALESCE(priceend1, priceend2, mature)) < 1
                                                THEN 1
                                              WHEN DATEDIFF(month, portdate, COALESCE(priceend1, priceend2, mature)) > 48
                                                THEN 48
                                              ELSE   DATEDIFF(month, portdate, COALESCE(priceend1, priceend2, mature))
                                            END
                                          = fx.month
                                      )
     WHERE port = @port
       AND portdate = @portdate
       AND p1.Sub IS NOT NULL
    ;
END;

Sua função usa ccd, portdatee o número de meses entre portdatee três datas possíveis (usando a primeira que não é NULL) para calcular a taxa pela qual exposuredeve ser multiplicado.

• Se você encontrar uma correspondência onde currency1é 'USD', você multiplica por rate;
• Se a única correspondência for onde currency2é 'USD', você divide por rate;
• se nenhuma correspondência for encontrada, o ratepadrão será 1

Então, vamos tentar construir uma tabela com suas taxas disponíveis.

Temos basicamente dois fatores que identificam os rate_datevalores que usaremos:

• o mês, um número inteiro entre 1 e 48;
• as duas moedas, uma das quais será 'USD', a outra irá variar.

A data máxima para cada um deles determinará qual taxa é necessária.

Isso nos dá no máximo 48 * (número de moedas) linhas para se preocupar, em termos práticos. Acho que são 10 a 15 mil linhas, não uma tabela excessivamente grande.

Para fins de junção, ajudará a padronizar a moeda, então a primeira é sempre USD- assim não precisamos nos preocupar se estamos multiplicando ou dividindo pela taxa.

Portanto, construímos uma tabela temporária com os valores exclusivos de currency1, currency2e forward_month(sempre colocando 'USD' na coluna da tabela temporária currency1) e o máximo rate_datepara cada conjunto exclusivo desses três valores.

Em seguida, adicionamos as taxas. Caso seja relevante, faremos como você fez; Se tivermos um caso em que 'USD' esteja em fxas currency1, usaremos isso de preferência em qualquer linha em que 'USD' esteja em currency2. Onde 'USD' está em currrency1, armazenamos ratediretamente; onde está currency2, precisamos dividir pela taxa, então armazenamos 1 / rate.

Então, em vez de usar a função para encontrar a taxa, juntamos à tabela temporária, com base em:

• a tabela temporária está currency1sendo 'USD'
• a tabela temporária está currency2sendo a ccddas outras tabelas na consulta
• a rate_datecorrespondência da portdatecoluna (que corresponde à passada @portdate)
• o mês corresponde ao número de meses entre portdate, e o primeiro de priceend1, priceend2e matureisso não é NULL. (Nota: COALESCEpega uma lista de dois ou mais valores e retorna o primeiro valor não NULL da lista; é um pouco mais fácil de ler do que ISNULL(x,ISNULL(y,z)), mas funciona da mesma forma)

Tornamos isso um LEFT JOIN, então incluímos linhas onde não podemos encontrar uma linha correspondente na tabela temporária.

Em seguida, simplesmente multiplicamos o valor apropriado (dependendo do valor de Sub) pela taxa retornada (ou por 1, se retornarmos um NULL, seja porque a tabela temporária não correspondia ou porque a tabela temporária tinha uma linha correspondente com um NULO rate).

NOTA: Combinei suas duas instruções UPDATE originais em uma, adicionando outra CASEno valor de p1.Sub. Para garantir que isso esteja funcionando como sua versão original de duas instruções, também coloquei uma verificação para ver se p1.Subé NULL; se isso não puder ser NULL, você poderá remover essa verificação.

Experimente isso e veja se funciona para você. Não é totalmente otimizado (acho que poderíamos reduzir as linhas na tabela temporária verificando as moedas que estão realmente em nossas linhas de transação de destino, por exemplo), mas remove a natureza linha por linha da função que você estava usando.

NOTA: o código não foi testado.