Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2008
    Posts
    16

    Unanswered: Performance Tuning - Assistance Reqd..

    Hi,
    Can some one help me to optimize this query. Its taking so much time

    select
    to_date(to_char(max(busi_date),'mm/dd/yyyy')||to_char(sysdate,'hh24:mi:ss'),'mm/dd/yyyy hh24:mi:ss') +
    (mod(:n_offset,24)/24) from
    (select busi_date from (select distinct to_date(busi_d
    ate,'mm/dd/yy') busi_date
    from date2busi_date
    where ((:n_offset < 24 and to_date(DUE_DATE,'mm/dd/rr') >= trunc(sysdate) )
    or (:n_offset >=24 and to_date(DUE_DATE,'mm/dd/rr') > trunc(sysdate) ))
    order by busi_date asc) where rownum <=:n_rows)

    Thanks a lot in advance ..
    Vijay..

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    We can start by formatting your SQL so that humans can read it:

    Code:
    select to_date(to_char(max(busi_date),'mm/dd/yyyy')
              || to_char(sysdate,'hh24:mi:ss'),'mm/dd/yyyy hh24:mi:ss')
              + (mod(:n_offset,24)/24)
    from ( select busi_date 
           from   ( select distinct to_date(busi_date,'mm/dd/yy') busi_date
                    from date2busi_date
                    where (  (   :n_offset < 24 
                             and to_date(DUE_DATE,'mm/dd/rr') >= trunc(sysdate) 
                             )
                          or (   :n_offset >=24 
                             and to_date(DUE_DATE,'mm/dd/rr') > trunc(sysdate) 
                             )
                          )
                    order by busi_date asc
                  )
            where rownum <=:n_rows
          )
    Now I can ask a couple of questions:

    1) What is the data type of DUE_DATE? I would hope it's a DATE, but then if it is a DATE why are you applying TO_DATE to it? Whether it is a DATE or not, applying TO_DATE will prevent use of any index on DUE_DATE.

    2) How many rows in table date2busi_date?

  3. #3
    Join Date
    Nov 2008
    Posts
    16
    Hi Andrew,
    Let me first thank you.. To answer your questions.

    1) Due_date is not a date data type. Its a char data type.

    2) The count in the date2busi_date table is : 19724


    SQL> select count(1) from date2busi_date;

    COUNT(1)
    ----------
    19724


    Thanks,
    Vijay.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    OK, well 19724 rows is a very small table really, so even a full table scan and a sort shouldn't take "so much time". How long is it actually taking?

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    What format is DUE_DATE in? In the case that it is indexed, going with some form of the following will benefit performance. It'd be perfect if due date was in YYYYMMDD format:

    Code:
    and DUE_DATE > to_char(sysdate,'YYYYMMDD')
    even if it's in YYYYMMDDHHMISS:

    Code:
    and DUE_DATE > (to_char(sysdate,'YYYYMMDD')||'000000')
    something to that effect.
    --=cf

Posting Permissions

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