Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2013
    Posts
    2

    Question Unanswered: Query Tuning/Optimization

    Hi all,

    The following query takes about an hour for execution:

    SELECT A.ATTR1, A.ATTR2, A.ATTR3, A.ATTR4, A.ATTR5, A.ATTR6, B.ATTR7
    FROM
    (SELECT ATTR1, ATTR2, ATTR3, ATTR4, ATTR5, ATTR6
    FROM TABLE1
    WHERE ATTR4 IN ('AB','CD')
    AND CTSEQ = '1'
    AND ATTR3 BETWEEN '20130212' AND '20130213'
    AND ATTR1 IN ('023','021','009','041','043')) A
    LEFT JOIN
    (SELECT ATTR2, ATTR7, ATTR1 , count(*) AS COUNT_ROWS
    FROM TABLE2
    GROUP BY ATTR2, ATTR7, ATTR1
    WHERE ATTR1 IN ('023','021','009','041','043')) B
    ON A.ATTR2 = B.ATTR2
    AND A.ATTR1 = B.ATTR1;

    Additional info:

    Number of records in Table1: ~ten million ;
    Number of records in Table2: ~hundred million

    Can this be optimized any further on the query (SQL) side? Can this be optimized further on the DB side? Its an Oracle Database in this case.

    Regards,
    DeepG

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Are there indexes on the two tables?

    1)On table1 an index on attr1 may help, it might also help to include the columns attr3,attr4,ctseq if they are selective (i.e. filter the resultset down a lot).

    2)On table2 an index on attr1 might help aswell assuming this is fairly selective.

    3) Do you need the count_rows column as you dont seem to use it

    Of course adding indexes to table will slow down inserts, updates, deletes so you need to look at your whole application not just your query.

    Alan

  3. #3
    Join Date
    Feb 2013
    Posts
    2

    Thanks

    Thanks Alan for the quick response. Have been away for a while, so regret the delay in replying. I will investigate the current status on indexes. However, any other form of optimization possible? Specifically, structuring the query in a different manner.

    I have already arrived at the present version of the query by replacing ">" and "<" by BETWEEN and DISTINCT by GROUP BY. It helped, but not enough.
    Also, listed down specific attributes in the select clause rather than just writing TABLE1.*.

    Is there DB level tuning apart from indexing the DB Tables possible?

    Regards,
    Deepak

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by DeepG View Post
    Hi all,

    The following query takes about an hour for execution:
    . . . E t c . . .
    Can this be optimized any further on the query (SQL) side? Can this be optimized further on the DB side? Its an Oracle Database in this case.
    No, I'm sure it cannot be optimized until you fix the syntax errors.

    PS: You need to provide the create table / insert statements with test data and the result expected.
    Last edited by LKBrwn_DBA; 02-25-13 at 15:29.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about a simple query with some(appropiate) indexes, like...
    Code:
    SELECT DISTINCT
           a.attr1
         , a.attr2
         , a.attr3
         , a.attr4
         , a.attr5
         , a.attr6
         , b.attr7
     FROM  TABLE1 AS a
     LEFT  JOIN
           TABLE2 AS b
      ON   b.attr1 = a.attr1
       AND b.attr2 = a.attr2
     WHERE a.attr1 IN ( '023' , '021' , '009' , '041' , '043' )
       AND a.attr3 BETWEEN '20130212' AND '20130213'
       AND a.attr4 IN ( 'AB' , 'CD' )
       AND a.ctseq = '1'
    Index on TABLE1:
    ( attr1 , attr3 , attr4 , ctseq )
    or
    ( attr1 , attr3 , attr4 , ctseq , attr2 , attr5 , attr6 )

    Index on TABLE2:
    ( attr1 , attr2 , attr7 )

Tags for this Thread

Posting Permissions

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