Results 1 to 7 of 7
  1. #1
    Join Date
    May 2013
    Posts
    3

    Unanswered: Qry takes 24 minutes

    I am trying to improve the performance of this qry which right now is taking far to long. On the only table used I already have a Primary Key and an Index. I am unsure what to do next. Here is the Qry.



    SELECT
    qry_tempCashValue.IssueYear,
    qry_tempCashValue.LM_PLAN_CODE,
    Count(distinct qry_tempCashValue.POLICY_NO) AS CountOfPOLICY_NO,
    qry_tempCashValue.[Interest Rate Code]
    FROM
    (
    SELECT
    tblInsurance.POLICY_NO,
    tblInsurance.IssueYear,
    tblInsurance.ISSUE_DATE,
    tblInsurance.LM_PLAN_CODE,
    tblInsurance.AMOUNT_INFORCE,
    tblInsurance.StatReserve,
    tblInsurance.StatReserveX,
    tblInsurance.DefPremReserve,
    tblInsurance.ExcessCashValue,
    tblInsurance.ExcessCashValueX,
    tblInsurance.STAT2_PUA_RES,
    tblInsurance.STAT2_OYT_RES,
    tblInsurance.StatOYTRes2X,
    qryPolyCalcParameters.[Calc Parameters Code],
    qryPolyCalcParameters.[Low Issue Date],
    qryPolyCalcParameters.[Interest Rate Code]
    FROM tblInsurance
    INNER JOIN qryPolyLifeMasterPlans
    ON tblInsurance.LM_PLAN_CODE =
    qryPolyLifeMasterPlans.[LifeMaster Plan Code]
    INNER JOIN qryPolyNonforfeitureValues
    ON qryPolyLifeMasterPlans.[Nonforfeiture Value Code] =
    qryPolyNonforfeitureValues.[Nonforfeiture Value Code]
    INNER JOIN qryPolyCalcParameters
    ON qryPolyNonforfeitureValues.[(Cash Value) Calc Parameters Code] =
    qryPolyCalcParameters.[Calc Parameters Code]
    WHERE
    qryPolyCalcParameters.[Low Issue Date]<[ISSUE_DATE]
    AND tblInsurance.COMPANY_CODE='NL'
    AND tblInsurance.LINE_OF_BUSINESS='IT'
    AND tblInsurance.SchedNP='PAR'
    AND tblInsurance.TERM_REASON='A'
    AND tblInsurance.ProdType='PERM'
    AND tblInsurance.PHASE_CODE=0
    AND tblInsurance.SUB_PHASE_CODE=1
    ) qry_tempCashValue


    GROUP BY
    qry_tempCashValue.IssueYear,
    qry_tempCashValue.LM_PLAN_CODE,
    qry_tempCashValue.[Interest Rate Code];

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by Cruse View Post
    INNER JOIN qryPolyLifeMasterPlans
    INNER JOIN qryPolyNonforfeitureValues
    Just a thought, but you're not selecting anything from these two tables which means they are part of the resultset...try selecting one column from each of these tables and see what happens. Also consider writing the query as a CTE. I'm sure other folks will see something I missed...

  3. #3
    Join Date
    May 2013
    Posts
    3
    That is because they are not tables they are views. There is an ON funciton following each INNER JOIN and this is where the information is being pulled.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Run this...

    SET SHOWPLAN_TEXT ON
    SET STATISTICS IO ON
    GO
    <insert your query here>

    and then post the results of these commands but not your query results in text format.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Slightly simplified (erroneous columns removed and CTE'd) and formatted for easier reading
    Code:
    ; WITH insurance AS (
      SELECT POLICY_NO
           , IssueYear
           , LM_PLAN_CODE
           , ISSUE_DATE -- <<< Assumption
      FROM   tblInsurance
      WHERE  COMPANY_CODE = 'NL'
      AND    LINE_OF_BUSINESS = 'IT'
      AND    SchedNP = 'PAR'
      AND    TERM_REASON = 'A'
      AND    ProdType = 'PERM'
      AND    PHASE_CODE = 0
      AND    SUB_PHASE_CODE = 1
    )
    SELECT IssueYear
         , LM_PLAN_CODE
         , Count(DISTINCT POLICY_NO) AS CountOfPOLICY_NO
         , "Interest Rate Code"
    FROM   (
            SELECT insurance.POLICY_NO
                 , insurance.IssueYear
                 , insurance.LM_PLAN_CODE
                 , qryPolyCalcParameters."Interest Rate Code"
            FROM   insurance
             INNER
              JOIN qryPolyLifeMasterPlans
                ON insurance.LM_PLAN_CODE = qryPolyLifeMasterPlans."LifeMaster Plan Code"
             INNER
              JOIN qryPolyNonforfeitureValues
                ON qryPolyLifeMasterPlans."Nonforfeiture Value Code" = qryPolyNonforfeitureValues."Nonforfeiture Value Code"
             INNER
              JOIN qryPolyCalcParameters
                ON qryPolyNonforfeitureValues."(Cash Value) Calc Parameters Code" = qryPolyCalcParameters."Calc Parameters Code"
            WHERE  qryPolyCalcParameters."Low Issue Date" < "ISSUE_DATE" -- Which object does ISSUE_DATE come from?
           ) As qry_tempCashValue
    GROUP
        BY IssueYear
         , LM_PLAN_CODE
         , "Interest Rate Code"
    ;
    Have made an assumption about the ISSUE_DATE field, is it correct?

    Would help if we knew the definitions of:
    qryPolyLifeMasterPlans
    qryPolyNonforfeitureValues
    qryPolyCalcParameters

    P.S. I loathe your naming conventions
    George
    Home | Blog

  6. #6
    Join Date
    May 2013
    Posts
    3
    I am running your version now

    My version used ot take 24 minutes but this morning for some reason it has just taken 31 minutes.

    Nothing has changed other then its the next day, why could this be?

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Do this for both versions.

    Quote Originally Posted by Thrasymachus View Post
    Run this...

    SET SHOWPLAN_TEXT ON
    SET STATISTICS IO ON
    GO
    <insert your query here>

    and then post the results of these commands but not your query results in text format.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •