How to optimize this SQL Server query - Multiple subqueries

Question

I have this query, with 4 subqueries, in SQL Server and I'm looking a way to optimize it:

 DECLARE @INICIO DATE
 DECLARE @FIN DATE
 SET @INICIO='2020-06-17'
 SET @FIN='2020-07-27'

 SELECT VALO.FECHA_VALORACION,
        VALO.CODIGO_PORTAFOLIO,
        VALO.CUSIP,
        VALO.NUMERO_INVERSION,
        VALO.SM_GRUPO 
        SM_TIPO,
        VALO.TIPO_INSTRUMENTO DESCRIPCION_INSTMT,
        VALO.CODIGO_MONEDA CODIGO_MONEDA_PAGAR, 
        VALO.CODIGO_MONEDA CODIGO_MONEDA_RECIBIR,
        VALO.ID_CONTRAPARTE,
        OP.PRECIO 
        PRECIO_FX,
        VALO.VALOR_NOMINAL,
        VALO.VALOR_DERECHO,
        VALO.VALOR_OBLIGACION,
        VALO.VALOR_UTILIDAD_PERDIDA, 
        OP.FECHA FECHA_OPERACION,
        VALO.TIPO_OPERACION,
        OP.VALOR_OPERACION  VALOR_OPERACION_COP,
        OP.FECHA_VENCIMIENTO,
        VALO.SM_GRUPO,
        VALO.VALOR_UTILIDAD_PERDIDA_DER,
        VALO.VALOR_UTILIDAD_PERDIDA_OBL,
        (SELECT SUM(NESTED.VALOR_UTILIDAD_PERDIDA)
         FROM Spirit.TSPT_VALORACION_FUT NESTED 
         WHERE NESTED.CUSIP=VALO.CUSIP
           AND NESTED.CODIGO_PORTAFOLIO=VALO.CODIGO_PORTAFOLIO 
           AND FECHA_VALORACION>=dateadd(month, datediff(month, 0, @INICIO), 0)
           AND FECHA_VALORACION<=VALO.FECHA_VALORACION 
           AND VALOR_UTILIDAD_PERDIDA>0
         GROUP BY CUSIP,CODIGO_PORTAFOLIO) AS UTILIDAD_MENSUAL,
        (SELECT SUM(NESTED.VALOR_UTILIDAD_PERDIDA)
         FROM Spirit.TSPT_VALORACION_FUT NESTED 
         WHERE NESTED.CUSIP=VALO.CUSIP
           AND NESTED.CODIGO_PORTAFOLIO=VALO.CODIGO_PORTAFOLIO 
           AND FECHA_VALORACION>=dateadd(month, datediff(month, 0, @INICIO), 0)
           AND FECHA_VALORACION<=VALO.FECHA_VALORACION 
           AND VALOR_UTILIDAD_PERDIDA<0
         GROUP BY CUSIP,CODIGO_PORTAFOLIO) AS PERDIDA_MENSUAL,
        (SELECT SUM(NESTED.VALOR_UTILIDAD_PERDIDA)
         FROM Spirit.TSPT_VALORACION_FUT NESTED 
         WHERE NESTED.CUSIP=VALO.CUSIP
         AND NESTED.CODIGO_PORTAFOLIO=VALO.CODIGO_PORTAFOLIO 
           AND NESTED.FECHA_VALORACION<=VALO.FECHA_VALORACION 
           AND NESTED.VALOR_UTILIDAD_PERDIDA>0
         GROUP BY CUSIP,CODIGO_PORTAFOLIO) AS UTILIDAD_ACUMULADA,
        (SELECT SUM(NESTED.VALOR_UTILIDAD_PERDIDA)
         FROM Spirit.TSPT_VALORACION_FUT NESTED 
         WHERE NESTED.CUSIP=VALO.CUSIP
           AND NESTED.CODIGO_PORTAFOLIO=VALO.CODIGO_PORTAFOLIO 
           AND FECHA_VALORACION<=VALO.FECHA_VALORACION
           AND VALOR_UTILIDAD_PERDIDA<0
         GROUP BY CUSIP,CODIGO_PORTAFOLIO) AS PERDIDA_ACUMULADA
FROM Spirit.TSPT_VALORACION_FUT VALO 
     INNER JOIN Spirit.TSPT_OPERACIONES OP ON OP.NUMERO_INVERSION = VALO.NUMERO_INVERSION
            AND OP.TIPO_OPERACION = VALO.TIPO_OPERACION
            AND OP.SM_GRUPO IN ('OPTION') 
WHERE VALO.SM_GRUPO IN ('OPTION')
  AND VALO.CODIGO_PORTAFOLIO IN ('AFP-P','AFP-F')
  AND VALO.FECHA_VALORACION >= @INICIO
  AND VALO.FECHA_VALORACION <= @FIN
ORDER BY VALO.CUSIP,VALO.FECHA_VALORACION

But I don't know how to integrate it and get the best. Thank you so much.

This is the result of a previous optimization process.

I've been trying some like this:

(SELECT sum (case when NESTED.VALOR_UTILIDAD_PERDIDA > 0 then NESTED.VALOR_UTILIDAD_PERDIDA else 0 end) as UTILIDAD_MENSUAL,
        sum (case when NESTED.VALOR_UTILIDAD_PERDIDA < 0 then  NESTED.VALOR_UTILIDAD_PERDIDA else 0 end) as PERDIDA_MENSUAL
    FROM Spirit.TSPT_VALORACION_FUT NESTED 
WHERE NESTED.CUSIP=VALO.CUSIP
  AND NESTED.CODIGO_PORTAFOLIO=VALO.CODIGO_PORTAFOLIO 
  AND FECHA_VALORACION>=dateadd(month, datediff(month, 0, @INICIO), 0) 
  AND FECHA_VALORACION<=VALO.FECHA_VALORACION 
  AND VALOR_UTILIDAD_PERDIDA>0
GROUP BY CUSIP,CODIGO_PORTAFOLIO) AS ACUMULADOS

FYI, white space and line breaks make for readable SQL.

Thom A
– Thom A ♦

2020-07-24 22:51:09 +00:00
Commented Jul 24, 2020 at 22:51 — Thom A
– Thom A ♦, Commented Jul 24, 2020 at 22:51

Jason · Accepted Answer · 2020-07-24 23:46:17Z

2

Since all your sub-queries seem to be coming from the same table, you can consolidate them into a single query in the FROM clause ... Try this ... I really can't debug the syntax without a table schema to reference ...

DECLARE @INICIO DATE
DECLARE @FIN DATE

SET @INICIO='2020-06-17'
SET @FIN='2020-07-27'

SELECT
    VALO.FECHA_VALORACION,
    VALO.CODIGO_PORTAFOLIO,
    VALO.CUSIP,
    VALO.NUMERO_INVERSION,
    VALO.SM_GRUPO
    SM_TIPO,
    VALO.TIPO_INSTRUMENTO DESCRIPCION_INSTMT,
    VALO.CODIGO_MONEDA CODIGO_MONEDA_PAGAR,
    VALO.CODIGO_MONEDA CODIGO_MONEDA_RECIBIR,
    VALO.ID_CONTRAPARTE,
    OP.PRECIO
    PRECIO_FX,
    VALO.VALOR_NOMINAL,
    VALO.VALOR_DERECHO,
    VALO.VALOR_OBLIGACION,
    VALO.VALOR_UTILIDAD_PERDIDA,
    OP.FECHA FECHA_OPERACION,
    VALO.TIPO_OPERACION,
    OP.VALOR_OPERACION VALOR_OPERACION_COP,
    OP.FECHA_VENCIMIENTO,
    VALO.SM_GRUPO,
    VALO.VALOR_UTILIDAD_PERDIDA_DER,
    VALO.VALOR_UTILIDAD_PERDIDA_OBL,
    S.UTILIDAD_MENSUAL,
    S.PERDIDA_MENSUAL,
    S.UTILIDAD_ACUMULADA,
    S.PERDIDA_ACUMULADA
FROM
    Spirit.TSPT_VALORACION_FUT VALO
    INNER JOIN Spirit.TSPT_OPERACIONES OP ON
        OP.NUMERO_INVERSION = VALO.NUMERO_INVERSION
        AND OP.TIPO_OPERACION = VALO.TIPO_OPERACION
        AND OP.SM_GRUPO IN ('OPTION')
    OUTER APPLY (
        SELECT
            SUM(CASE WHEN FECHA_VALORACION>=dateadd(month, datediff(month, 0, @INICIO), 0) AND VALOR_UTILIDAD_PERDIDA>0
                THEN NESTED.VALOR_UTILIDAD_PERDIDA
                ELSE 0
            END) UTILIDAD_MENSUAL,

            SUM(CASE WHEN  FECHA_VALORACION>=dateadd(month, datediff(month, 0, @INICIO), 0) AND VALOR_UTILIDAD_PERDIDA<0 
                THEN NESTED.VALOR_UTILIDAD_PERDIDA
                ELSE 0
            END) PERDIDA_MENSUAL,

            SUM(CASE WHEN NESTED.VALOR_UTILIDAD_PERDIDA>0 
                THEN NESTED.VALOR_UTILIDAD_PERDIDA
                ELSE 0
            END) UTILIDAD_ACUMULADA,

            SUM(CASE WHEN VALOR_UTILIDAD_PERDIDA<0
                THEN NESTED.VALOR_UTILIDAD_PERDIDA
                ELSE 0 END
            ) PERDIDA_ACUMULADA
        FROM
            Spirit.TSPT_VALORACION_FUT NESTED
        WHERE
            FECHA_VALORACION<=VALO.FECHA_VALORACION AND 
            NESTED.CUSIP = VALO.CUSIP AND
            NESTED.CODIGO_PORTAFOLIO=VALO.CODIGO_PORTAFOLIO
        GROUP BY
            CUSIP,
            CODIGO_PORTAFOLIO
    ) S
WHERE
    VALO.SM_GRUPO IN ('OPTION')
    AND VALO.CODIGO_PORTAFOLIO IN ('AFP-P','AFP-F')
    AND VALO.FECHA_VALORACION >= @INICIO
    AND VALO.FECHA_VALORACION <= @FIN
ORDER BY
    VALO.CUSIP,
    VALO.FECHA_VALORACION

edited Jul 24, 2020 at 23:46

answered Jul 24, 2020 at 23:24

Jason

7316 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jaxonjma Over a year ago

It give me: "Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression."

jaxonjma Over a year ago

Line " SUM(CASE WHEN FECHA_VALORACION>=dateadd(month, datediff(month, 0, @INICIO), 0) AND FECHA_VALORACION<=VALO.FECHA_VALORACION AND VALOR_UTILIDAD_PERDIDA>0 THEN NESTED.VALOR_UTILIDAD_PERDIDA ELSE 0 END) UTILIDAD_MENSUAL"

Jason Over a year ago

No problem ... can you give me a CREATE TABLE definition for the two tables in the query?

Jason Over a year ago

Try this one ... I was able to move more common conditions into the WHERE CLAUSE out of the CASE statements.

Collectives™ on Stack Overflow

How to optimize this SQL Server query - Multiple subqueries

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related