Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2010
    Posts
    13

    Unanswered: DB2 SQL perforamance

    I have a DB2 table with over 22 million records. The table is BY FISCAL DATE
    from 1999 to now. There's about 1 million rows per YEAR(FISCAL DATE).

    The current selection is:

    if YEAR(FISC_DATE) = 2010
    AND ADATE <= '2010-07-07'


    the proposed coding is plus ADATE is made index:
    write new sql to retrieve the earliest ADATE for YEAR(FISC_DATE) = 2010
    and pass it in @ED to next sql

    if YEAR(FISC_DATE) = 2010
    AND ADATE between '@ED and 2010-07-07'

    WILL THIS PROPOSED CHANGE HELP PERFORMANCE?

    Thanks, Don






    where AC is acceptance_date

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What columns do you have indexes on?

    Andy

  3. #3
    Join Date
    Jul 2010
    Posts
    13

    DB@# sql

    fisc_date and acceptance_date

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation Using indexes or functions

    Instead of
    Code:
    IF YEAR(FISC_DATE) = 2010
    use
    Code:
    IF FISC_DATE between '2010-01-01' and '2010-12-31'
    Lenny

  5. #5
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    I think when you use a function index is not used. So Lenny is right.
    But fiscal year is not necessaraly from Jan 1 to Dec 31 so the statement
    IF FISC_DATE between '2010-01-01' and '2010-12-31' might not work.

    Or if fiscal year end Jan 1 - Dec 31 you do not need to use FISC_DATE in your query so can do it this way:

    Code:
    if ADATE between '2010-01-01' and '2010-07-07'
    Last edited by MarkhamDBA; 07-07-10 at 13:43.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by MarkhamDBA View Post
    did you mean instead of:
    Code:
    if YEAR(FISC_DATE) = 2010
    AND ADATE <= '2010-07-07'
    use:
    Code:
    if FISC_DATE between '2010-01-01' and '2010-07-07'
    ?
    No he meant that you replace it with this:
    Code:
    if FISC_DATE between '2010-01-01' and '2010-07-07
    AND ADATE <= '2010-07-07'
    Andy

  7. #7
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by ARWinner View Post
    No he meant that you replace it with this:
    Code:
    if FISC_DATE between '2010-01-01' and '2010-07-07
    AND ADATE <= '2010-07-07'
    Andy
    you mean:
    Code:
    if FISC_DATE between '2010-01-01' and '2010-12-31'
    AND ADATE <= '2010-07-07'
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes, that is what I meant.

    Andy

Posting Permissions

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