Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    18

    Unanswered: calculation predicate consumes too much cpu

    Hi experts,

    The below sql consumes too much cpu, do you have advice on how to reduce the cpu time?
    do the way that put the statement:
    AND ( CAV_PRIC_AMT - TOT_RCVD_PRIC_AMT ) >= : H
    AND ( CAV_PRIC_AMT - TOT_RCVD_PRIC_AMT ) <= : H
    into cobol can help to reduce the cpu time?

    SELECT ...
    FROM T
    WHERE ORG = : H
    AND VALID_FLAG = : H
    AND BACC >= : H
    AND BACC <= : H
    AND CSTM_NO >= : H
    AND CSTM_NO <= : H
    AND ( CAV_PRIC_AMT - TOT_RCVD_PRIC_AMT ) >= : H
    AND ( CAV_PRIC_AMT - TOT_RCVD_PRIC_AMT ) <= : H
    AND OPEN_DT >= : H
    AND OPEN_DT <= : H
    AND DUE_DT >= : H
    AND DUE_DT <= : H

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by welyngj View Post
    Hi experts,

    The below sql consumes too much cpu, do you have advice on how to reduce the cpu time?
    do the way that put the statement:
    AND ( CAV_PRIC_AMT - TOT_RCVD_PRIC_AMT ) >= : H
    AND ( CAV_PRIC_AMT - TOT_RCVD_PRIC_AMT ) <= : H
    into cobol can help to reduce the cpu time?

    SELECT ...
    FROM T
    WHERE ORG = : H
    AND VALID_FLAG = : H
    AND BACC >= : H
    AND BACC <= : H
    AND CSTM_NO >= : H
    AND CSTM_NO <= : H
    AND ( CAV_PRIC_AMT - TOT_RCVD_PRIC_AMT ) >= : H
    AND ( CAV_PRIC_AMT - TOT_RCVD_PRIC_AMT ) <= : H
    AND OPEN_DT >= : H
    AND OPEN_DT <= : H
    AND DUE_DT >= : H
    AND DUE_DT <= : H
    What platform and version? Have you checked the plan? What indexes exist on the table? Is a generated column an option?
    --
    Lennart

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    AND ( CAV_PRIC_AMT - TOT_RCVD_PRIC_AMT ) >= : H
    AND ( CAV_PRIC_AMT - TOT_RCVD_PRIC_AMT ) <= : H
    are stage 2 predicates, which can slow you down. Have you considered changing them into stage 1?
    Such as:
    AND CAV_PRIC_AMT >= TOT_RCVD_PRIC_AMT + : H
    AND ( CAV_PRIC_AMT <= TOT_RCVD_PRIC_AMT + : H
    This is only stage 1 depending on which table is accessed first, though.
    Dave

Posting Permissions

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