Results 1 to 4 of 4

Thread: Slow query

  1. #1
    Join Date
    May 2014
    Posts
    1

    Unanswered: Slow query

    Hi,
    Iam looking is there any different way to execute below query as it is very slow.

    SELECT distinct(a.NAME) As C_NAME,b.NAME As s_NAME, c.SOURCEID
    FROM a,b,c ,d
    WHERE a.SID = b.SID
    AND b.SID = d.SID
    AND d.CVERSION > 0
    AND a.TYPE='C'
    AND a.ACTIVENODE=1
    AND c.NID = a.NID
    and a.SOURCEID not in (select distinct SOURCEID from REPORTING_DETAILS where trunc(COLLECTIONEND) = trunc(sysdate));

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, there are other ways that are probably a lot faster. You haven't given us enough information about your tables, indexes, etc. to help you find one of those faster ways.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    stop using distinct. It causes sorts. find a way to use sql to get just the row(s) you are after. instead of 'not in' try a 'not exists'
    Dave

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My some thoughts:

    (1) (Same as dav1mo wrote)
    Use NOT EXISTS insead of NOT IN, and avoid DISTINCT.

    (2) Doubt to join table d.
    Because, no column of the table d was not in the final select list.
    Using EXISTS might be sufficient.

    (3) If used EXISTS for table d,
    DISTINCT in the final SELECT clause might be unnecessary.

Posting Permissions

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