Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    36

    Unanswered: help optimizing query

    I have a query that is taking about 12 minutes to run. I'm sure there has to be a way to speed it up...i'm just not sure how. any suggestions on how to rewrite this would be much appreciated. i think it has something to do with the "not in" join.

    Select
    Distinct T.TSNUMB
    from
    TSR T
    where
    T.TSSTAT='C'
    and T.TSCDAT >= '01-Oct-03'
    and T.TSCDAT <= sysdate
    and T.TSORGA in (Select distinct O.ORORGA
    from OrgCode O
    where
    O.ORTCON like '123456%'
    and T.TSNUMB not in (select distinct w.wcnumb
    from
    wobaskdtl w
    where w.wcbask like '%-V'))
    Last edited by aliaga61; 02-02-04 at 10:15.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: help optimizing query

    You could try using NOT EXISTS instead of NOT IN:

    Select
    Distinct T.TSNUMB
    from
    TSR T
    where
    T.TSSTAT='C'
    and T.TSCDAT >= '01-Oct-03'
    and T.TSCDAT <= sysdate
    and T.TSORGA in (Select distinct O.ORORGA
    from OrgCode O
    where
    O.ORTCON like '123456%'
    and not exists (select null
    from
    wobaskdtl w
    where w.wcbask like '%-V' and w.wcnumb = t.tsnumb))

    Also make sure your tables have been analyzed and the statistics are not stale (I'm inferring this is Oracle?)

  3. #3
    Join Date
    Jan 2004
    Posts
    36
    thank you...not sure i completely understand what is going on there (have to sit down and really look at it) but it works.

Posting Permissions

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