title: CAN anyone help me with the sp optimization?
body: <p>I wrote this sp. but it's taking almost 01:12 sec to execute. can anyone help me with the sp optimization? </p>
  
  <pre><code>declare @R71_TERM_PARA CHAR(3) = '14f'
  
  --AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
  -- REG-LOA
  SELECT TM.*
  FROM   (
          SELECT PN.UCLA_ID
                ,PN.FULL_NAME_PERSON
                ,SM.BAR_ASMT_CD
                ,SM.REG_PMT_STAT_FL
                ,SM.ENRL_WTHDRW_CD
                ,SM.JOINT_STU_FL
                ,TY.ADM_TERM_CD
                ,TD_ADM.TERM_SEQ_NUM AS ADM_TSEQ
                ,TT.TERM_CD          AS CURR_TERM
                ,TT.TERM_SEQ_NUM     AS CURR_TSEQ
                ,TT.CAREER_CD
                ,TT.CAREER_SUFX_CD
                ,TT.REG_TYP_CD
                ,PT.COLL_CD
                ,PT.MAJOR_CD
                ,PT.DEG_CD
                ,PT.PROG_PRIO_FL
                ,TT_PRV1.TERM_CD         AS PRV1_TERM
                ,TT_PRV1.TERM_SEQ_NUM    AS PRV1_TSEQ
                ,TT_PRV1.REG_TYP_CD      AS PRV1_REG_TYP 
                ,SM_PRV1.REG_PMT_STAT_FL AS PRV1_PMT_FL
                ,SM_PRV1.ENRL_WTHDRW_CD  AS PRV1_WTHDRW_CD
                ,TT_PRV1.CAREER_CD       AS PRV1_CAREER
                ,TT_PRV1.CAREER_SUFX_CD  AS PRV1_SUFX
                ,TT_PRV2.TERM_CD         AS PRV2_TERM
                ,TT_PRV2.TERM_SEQ_NUM    AS PRV2_TSEQ
                ,TT_PRV2.REG_TYP_CD      AS PRV2_REG_TYP
                ,SM_PRV2.REG_PMT_STAT_FL AS PRV2_PMT_FL
                ,SM_PRV2.ENRL_WTHDRW_CD  AS PRV2_WTHDRW_CD 
                ,TT_PRV2.CAREER_CD       AS PRV2_CAREER
                ,TT_PRV2.CAREER_SUFX_CD  AS PRV2_SUFX
                ,TL.LOA_STRT_TERM_CD
                ,TL.LOA_STRT_TSEQ
                ,TL.LOA_RET_TERM_CD
                ,TL.LOA_RET_TSEQ
                ,TL.LOA_APRV_DT
                ,TL.LOA_PETN_TYP_CD
          FROM   ID0APT AS AP
                 INNER JOIN
                 ID0PNT AS PN
                   ON AP.UCLA_ID               = PN.UCLA_ID
                  AND AP.NAME_SEQ_NUM          = PN.NAME_SEQ_NUM
                 INNER JOIN 
                 SR0SMT AS SM
                   ON AP.UCLA_ID               = SM.STU_ID
                 INNER JOIN 
                 SR0TTT AS TT 
                   ON TT.STU_ID                = SM.STU_ID
                  AND TT.TERM_CD               = SM.TERM_CD
                 INNER JOIN 
                 SR0TYT AS TY
                   ON TT.STU_ID                = TY.STU_ID
                  AND TT.CAREER_CD             = TY.CAREER_CD
                  AND TT.CAREER_SUFX_CD        = TY.CAREER_SUFX_CD
                 INNER JOIN 
                 SR0PTT AS PT
                   ON TT.STU_ID                = PT.STU_ID
                  AND TT.TERM_CD               = PT.TERM_CD
                  AND TT.CAREER_CD             = PT.CAREER_CD
                  AND TT.CAREER_SUFX_CD        = PT.CAREER_SUFX_CD
                 LEFT JOIN 
                 SR0TDT AS TD_ADM
                   ON TY.ADM_TERM_CD           = TD_ADM.TERM_CD
                 LEFT JOIN 
                 SR0TTT AS TT_PRV1 
                   ON TT.STU_ID                = TT_PRV1.STU_ID
                  AND TT.CAREER_CD             = TT_PRV1.CAREER_CD
                  AND TT.CAREER_SUFX_CD        = TT_PRV1.CAREER_SUFX_CD
                  AND CASE
                       --DENTAL, GRADUATE, UNDEGRADUATE PARAMETERS
                       WHEN TT.CAREER_CD = 'D'
                         OR TT.CAREER_CD = 'G'
                         OR TT.CAREER_CD = 'U' THEN
                          CASE RIGHT(SM.TERM_CD,1)
                           WHEN 'F' THEN SM.TERM_SEQ_NUM - 3
                           ELSE          SM.TERM_SEQ_NUM - 1
                          END
                       --LAW, MEDICAL PARAMETERS
                       ELSE
                          CASE RIGHT(SM.TERM_CD,1)
                           WHEN 'F' THEN SM.TERM_SEQ_NUM - 3
                           ELSE          SM.TERM_SEQ_NUM - 2
                          END
                      END                      = TT_PRV1.TERM_SEQ_NUM    
                 LEFT JOIN 
                 SR0SMT AS SM_PRV1 
                   ON TT_PRV1.STU_ID           = SM_PRV1.STU_ID
                  AND TT_PRV1.TERM_CD          = SM_PRV1.TERM_CD
                 LEFT JOIN 
                 SR0TTT AS TT_PRV2 
                   ON TT.STU_ID                = TT_PRV2.STU_ID
                  AND TT.CAREER_CD             = TT_PRV2.CAREER_CD
                  AND TT.CAREER_SUFX_CD        = TT_PRV2.CAREER_SUFX_CD
                  AND CASE
                       --DENTAL, GRADUATE, UNDERGRADUATE PARAMETERS
                       WHEN TT.CAREER_CD = 'D'
                         OR TT.CAREER_CD = 'G'
                         OR TT.CAREER_CD = 'U' THEN
                          CASE RIGHT(SM.TERM_CD,1)
                           WHEN 'S' THEN SM.TERM_SEQ_NUM - 2
                           ELSE          SM.TERM_SEQ_NUM - 4
                          END
                       --LAW, MEDICAL PARAMETERS
                       ELSE              SM.TERM_SEQ_NUM - 5
                      END                      = TT_PRV2.TERM_SEQ_NUM    
                 LEFT JOIN 
                 SR0SMT AS SM_PRV2 
                   ON TT_PRV2.STU_ID           = SM_PRV2.STU_ID
                  AND TT_PRV2.TERM_CD          = SM_PRV2.TERM_CD
                 INNER JOIN 
                 SR0TLT AS TL 
                   ON TT.STU_ID          = TL.STU_ID
                  AND TT.CAREER_CD       = TL.CAREER_CD
                  AND TT.CAREER_SUFX_CD  = TL.CAREER_SUFX_CD
                ,SR0TDT AS TD
          WHERE
                 SM.BAR_ASMT_CD         NOT IN ('N','X')
             AND AP.APP_ID                  = 'SR0'
             AND TD.TERM_CD                 = @R71_TERM_PARA
             AND SM.TERM_SEQ_NUM           &gt;= TD.TERM_SEQ_NUM
                 --LATEST LOA START TERM BASED ON TERM VALUE
             AND TL.LOA_STRT_TSEQ  IN
                  (
                   SELECT MAX(TL_MAX.LOA_STRT_TSEQ)
                   FROM   SR0TLT AS TL_MAX
                   WHERE  TL_MAX.STU_ID          = TT.STU_ID
                      AND TL_MAX.CAREER_CD       = TT.CAREER_CD
                      AND TL_MAX.CAREER_SUFX_CD  = TT.CAREER_SUFX_CD
                      AND TL_MAX.LOA_STRT_TSEQ  &lt;= TT.TERM_SEQ_NUM
                  )
                 --LATEST LOA START TERM BASED ON TERM VALUE
             AND TL.LOA_APRV_DT    IN
                  (
                   SELECT MAX(TL_DT.LOA_APRV_DT)
                   FROM   SR0TLT AS TL_DT
                   WHERE  TL_DT.STU_ID          = TT.STU_ID
                      AND TL_DT.CAREER_CD       = TT.CAREER_CD
                      AND TL_DT.CAREER_SUFX_CD  = TT.CAREER_SUFX_CD
                      AND TL_DT.LOA_STRT_TSEQ   =
                              (
                               SELECT MAX(TL_MAX.LOA_STRT_TSEQ)
                               FROM   SR0TLT AS TL_MAX
                               WHERE  TL_MAX.STU_ID          = TT.STU_ID
                                  AND TL_MAX.CAREER_CD       = TT.CAREER_CD
                                  AND TL_MAX.CAREER_SUFX_CD  = TT.CAREER_SUFX_CD
                                  AND TL_MAX.LOA_STRT_TSEQ  &lt;= TT.TERM_SEQ_NUM
                              )
                  )
         )      AS TM 
  WHERE   --VALIDATE ONLY VALID TERMS
          (  
                (
                     TM.CAREER_CD          IN ('D','M')
                 AND RIGHT(TM.CURR_TERM,1) IN ('F','W','S','2')
                )
             OR  
                (
                     TM.CAREER_CD          IN ('G','L','U')
                 AND RIGHT(TM.CURR_TERM,1) IN ('F','W','S')
                )
          )
      AND (
              (
                (
                    (
                     --LOA MUST BE WITHIN LOA PETITION RANGE
                         TM.REG_TYP_CD                    = 'LOA'
                     AND (
                             TM.CURR_TSEQ                 &gt; TM.LOA_RET_TSEQ
                          OR TM.BAR_ASMT_CD               = 'I'
                          OR (    TM.BAR_ASMT_CD         IN ('B','Y')
                              AND TM.ENRL_WTHDRW_CD  NOT IN ('C','X')
                             )
                          OR (
                                  TM.CURR_TERM            = TM.LOA_STRT_TERM_CD
                              AND TM.PROG_PRIO_FL         = 'Y'
                             )
                         )
                    )
                 OR 
                    (
                     --LOA MUST BE WITHIN LOA PETITION RANGE
                         TM.REG_TYP_CD                    &lt;&gt; 'LOA'
                     AND TM.CURR_TSEQ                      &lt; TM.LOA_RET_TSEQ
                     AND TM.CURR_TSEQ                      &gt; TM.LOA_STRT_TSEQ
                    )
                 OR
                    (
                     --FILED FOR LEAVE BUT INVALID BAR_ASMT_CD
                         TM.ENRL_WTHDRW_CD                 = 'L'
                     AND TM.REG_PMT_STAT_FL                = 'N'
                     AND TM.BAR_ASMT_CD               NOT IN ('D','Z')
                    ) 
                )
  
              )
          OR  (
                 --RLA MUST EQUAL PETITION RETURN TERM IN PREVIOUS LOA PETITION RANGE
                     TM.REG_TYP_CD                    = 'RLA'
                 AND TM.CURR_TERM                 NOT IN
                       (
                        SELECT TL.LOA_RET_TERM_CD
                        FROM   SR0TLT AS TL 
                        WHERE  TL.STU_ID          = TM.UCLA_ID
                           AND TL.CAREER_CD       = TM.CAREER_CD
                           AND TL.CAREER_SUFX_CD  = TM.CAREER_SUFX_CD
                           --LATEST LOA START TERM BASED ON TERM VALUE
                           AND TL.LOA_STRT_TSEQ  IN
                                  (
                                   SELECT MAX(TL_MAX.LOA_STRT_TSEQ)
                                   FROM   SR0TLT AS TL_MAX
                                   WHERE  TL_MAX.STU_ID          = TM.UCLA_ID
                                      AND TL_MAX.CAREER_CD       = TM.CAREER_CD
                                      AND TL_MAX.CAREER_SUFX_CD  = TM.CAREER_SUFX_CD
                                      AND TL_MAX.LOA_STRT_TSEQ   &lt; TM.CURR_TSEQ
                                  )
                            --VALIDATE ONLY VALID TERMS
                            AND (  
                                    (
                                         TM.CAREER_CD          IN ('D','M')
                                     AND RIGHT(TM.CURR_TERM,1) IN ('F','W','S','2')
                                    )
                                 OR  
                                    (
                                         TL.CAREER_CD          IN ('G','L','U')
                                     AND RIGHT(TM.CURR_TERM,1) IN ('F','W','S')
                                    )
                                )
                        )
              )
  
          OR  (
                (
                    (
                     --RLA MUST EQUAL PETITION RETURN TERM
                         TM.REG_TYP_CD                = 'RLA'
                     AND TM.CURR_TERM                &lt;&gt; TM.LOA_RET_TERM_CD
                     AND TM.CURR_TSEQ                 &gt; TM.LOA_STRT_TSEQ
                    )
                  OR
                    (
                     --RLA MUST EQUAL PETITION RETURN TERM
                         TM.REG_TYP_CD               &lt;&gt; 'RLA'
                     AND TM.CURR_TERM                 = TM.LOA_RET_TERM_CD
                    )
                )
              )
  
          )
  
  UNION
  
  SELECT TM.*
  FROM   (
          SELECT PN.UCLA_ID
                ,PN.FULL_NAME_PERSON
                ,SM.BAR_ASMT_CD
                ,SM.REG_PMT_STAT_FL
                ,SM.ENRL_WTHDRW_CD
                ,SM.JOINT_STU_FL
                ,TY.ADM_TERM_CD
                ,TD_ADM.TERM_SEQ_NUM AS ADM_TSEQ
                ,TT.TERM_CD          AS CURR_TERM
                ,TT.TERM_SEQ_NUM     AS CURR_TSEQ
                ,TT.CAREER_CD
                ,TT.CAREER_SUFX_CD
                ,TT.REG_TYP_CD
                ,PT.COLL_CD
                ,PT.MAJOR_CD
                ,PT.DEG_CD
                ,PT.PROG_PRIO_FL
                ,TT_PRV1.TERM_CD         AS PRV1_TERM
                ,TT_PRV1.TERM_SEQ_NUM    AS PRV1_TSEQ
                ,TT_PRV1.REG_TYP_CD      AS PRV1_REG_TYP 
                ,SM_PRV1.REG_PMT_STAT_FL AS PRV1_PMT_FL
                ,SM_PRV1.ENRL_WTHDRW_CD  AS PRV1_WTHDRW_CD
                ,TT_PRV1.CAREER_CD       AS PRV1_CAREER
                ,TT_PRV1.CAREER_SUFX_CD  AS PRV1_SUFX
                ,TT_PRV2.TERM_CD         AS PRV2_TERM
                ,TT_PRV2.TERM_SEQ_NUM    AS PRV2_TSEQ
                ,TT_PRV2.REG_TYP_CD      AS PRV2_REG_TYP
                ,SM_PRV2.REG_PMT_STAT_FL AS PRV2_PMT_FL
                ,SM_PRV2.ENRL_WTHDRW_CD  AS PRV2_WTHDRW_CD 
                ,TT_PRV2.CAREER_CD       AS PRV2_CAREER
                ,TT_PRV2.CAREER_SUFX_CD  AS PRV2_SUFX
                ,'' AS LOA_STRT_TERM_CD
                ,0  AS LOA_STRT_TSEQ
                ,'' AS LOA_RET_TERM_CD
                ,0  AS LOA_RET_TSEQ
                ,'' AS LOA_APRV_DT
                ,'' AS LOA_PETN_TYP_CD
          FROM   ID0APT AS AP
                 INNER JOIN
                 ID0PNT AS PN
                   ON AP.UCLA_ID               = PN.UCLA_ID
                  AND AP.NAME_SEQ_NUM          = PN.NAME_SEQ_NUM
                 INNER JOIN 
                 SR0SMT AS SM
                   ON AP.UCLA_ID               = SM.STU_ID
                 INNER JOIN 
                 SR0TTT AS TT 
                   ON TT.STU_ID                = SM.STU_ID
                  AND TT.TERM_CD               = SM.TERM_CD
                 INNER JOIN 
                 SR0TYT AS TY
                   ON TT.STU_ID                = TY.STU_ID
                  AND TT.CAREER_CD             = TY.CAREER_CD
                  AND TT.CAREER_SUFX_CD        = TY.CAREER_SUFX_CD
                 INNER JOIN 
                 SR0PTT AS PT
                   ON TT.STU_ID                = PT.STU_ID
                  AND TT.TERM_CD               = PT.TERM_CD
                  AND TT.CAREER_CD             = PT.CAREER_CD
                  AND TT.CAREER_SUFX_CD        = PT.CAREER_SUFX_CD
                 LEFT JOIN 
                 SR0TDT AS TD_ADM
                   ON TY.ADM_TERM_CD           = TD_ADM.TERM_CD
                 LEFT JOIN 
                 SR0TTT AS TT_PRV1 
                   ON TT.STU_ID                = TT_PRV1.STU_ID
                  AND TT.CAREER_CD             = TT_PRV1.CAREER_CD
                  AND TT.CAREER_SUFX_CD        = TT_PRV1.CAREER_SUFX_CD
                  AND CASE
                       --DENTAL, GRADUATE, UNDEGRADUATE PARAMETERS
                       WHEN TT.CAREER_CD = 'D'
                         OR TT.CAREER_CD = 'G'
                         OR TT.CAREER_CD = 'U' THEN
                          CASE RIGHT(SM.TERM_CD,1)
                           WHEN 'F' THEN SM.TERM_SEQ_NUM - 3
                           ELSE          SM.TERM_SEQ_NUM - 1
                          END
                       --LAW, MEDICAL PARAMETERS
                       ELSE
                          CASE RIGHT(SM.TERM_CD,1)
                           WHEN 'F' THEN SM.TERM_SEQ_NUM - 3
                           ELSE          SM.TERM_SEQ_NUM - 2
                          END
                      END                      = TT_PRV1.TERM_SEQ_NUM    
                 LEFT JOIN 
                 SR0SMT AS SM_PRV1 
                   ON TT_PRV1.STU_ID           = SM_PRV1.STU_ID
                  AND TT_PRV1.TERM_CD          = SM_PRV1.TERM_CD
                 LEFT JOIN 
                 SR0TTT AS TT_PRV2 
                   ON TT.STU_ID                = TT_PRV2.STU_ID
                  AND TT.CAREER_CD             = TT_PRV2.CAREER_CD
                  AND TT.CAREER_SUFX_CD        = TT_PRV2.CAREER_SUFX_CD
                  AND CASE
                       --DENTAL, GRADUATE, UNDERGRADUATE PARAMETERS
                       WHEN TT.CAREER_CD = 'D'
                         OR TT.CAREER_CD = 'G'
                         OR TT.CAREER_CD = 'U' THEN
                          CASE RIGHT(SM.TERM_CD,1)
                           WHEN 'S' THEN SM.TERM_SEQ_NUM - 2
                           ELSE          SM.TERM_SEQ_NUM - 4
                          END
                       --LAW, MEDICAL PARAMETERS
                       ELSE              SM.TERM_SEQ_NUM - 5
                      END                      = TT_PRV2.TERM_SEQ_NUM    
                 LEFT JOIN 
                 SR0SMT AS SM_PRV2 
                   ON TT_PRV2.STU_ID           = SM_PRV2.STU_ID
                  AND TT_PRV2.TERM_CD          = SM_PRV2.TERM_CD
                ,SR0TDT AS TD
          WHERE
                 SM.BAR_ASMT_CD         NOT IN ('N','X')
             AND AP.APP_ID                  = 'SR0'
             AND TD.TERM_CD                 = @R71_TERM_PARA
             AND SM.TERM_SEQ_NUM           &gt;= TD.TERM_SEQ_NUM
         )      AS TM 
  WHERE   --LOA AND RLA MUST HAVE A VALID PETIION RANGE
          TM.REG_TYP_CD         IN ('LOA','RLA')
      AND TM.UCLA_ID        NOT IN
           (
            SELECT TL.STU_ID
            FROM   SR0TLT AS TL 
            WHERE  TL.STU_ID          = TM.UCLA_ID
               AND TL.CAREER_CD       = TM.CAREER_CD
               AND TL.CAREER_SUFX_CD  = TM.CAREER_SUFX_CD
               --LATEST LOA START TERM BASED ON TERM VALUE
               AND TL.LOA_STRT_TSEQ  IN
                      (
                       SELECT MAX(TL_MAX.LOA_STRT_TSEQ)
                       FROM   SR0TLT AS TL_MAX
                       WHERE  TL_MAX.STU_ID          = TM.UCLA_ID
                          AND TL_MAX.CAREER_CD       = TM.CAREER_CD
                          AND TL_MAX.CAREER_SUFX_CD  = TM.CAREER_SUFX_CD
                          AND TL_MAX.LOA_STRT_TSEQ   &lt; TM.CURR_TSEQ
                      )
                --VALIDATE ONLY VALID TERMS
                AND (  
                        (
                             TM.CAREER_CD          IN ('D','M')
                         AND RIGHT(TM.CURR_TERM,1) IN ('F','W','S','2')
                        )
                     OR  
                        (
                             TL.CAREER_CD          IN ('G','L','U')
                         AND RIGHT(TM.CURR_TERM,1) IN ('F','W','S')
                        )
                    )
            )
  
  
  ORDER BY UCLA_ID
  
  IF @@ROWCOUNT = 0
  PRINT 'WARNING: NO ROWS WERE SELECTED.';
  
  END
  </code></pre>
  
  <p>I think there is similar code in the second union . Is there any possibility to add this code into CTE and then joining where ever needed ?</p>