Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    20

    Unanswered: Suggetion abt a select query optimization

    Hi ,
    The following query is taking a lot of time.
    The table has 0.2 million records.Can any one suggest some methods to optoimize it ?

    SELECT * from TABLE_TEST where (TIME is not NULL and (TIME = 1064929834 OR 1064929834 - TIME >= 10)) AND (EXPDT - 1064929834 > 0 and (STATUS = 1 OR STATUS = 0) and 1064929834 - SADT >= 0)

    TIME is PK, SADT,EXPDT,TIME,STSTUS are all numbers.
    PLz suggest something.
    Waiting for your suggetion.
    Sandy

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    could try this

    SELECT *
    from TABLE_TEST
    where TIME is not NULL
    and TIME = 1064929834
    AND EXPDT - 1064929834 > 0
    and STATUS in (0, 1)
    and 1064929834 - SADT >= 0
    union
    SELECT *
    from TABLE_TEST
    where TIME is not NULL
    and 1064929834 - TIME >= 10
    AND EXPDT - 1064929834 > 0
    and STATUS in (0, 1)
    and 1064929834 - SADT >= 0

    BUT if time is PK then why the time is not null?

    Is 1064929834 always used or does this number change?

    Alan

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You can also rewrite

    1064929834 - TIME >= 10

    as

    TIME <= 1064929834-10

    I think if my algebra is right : )

    Then it can use an index range scan on the PK_TIME index which it might not use otherwise. Check your execution plan before and after this change to see if will make a difference or not.

    Alan

  4. #4
    Join Date
    Sep 2003
    Posts
    20

    Sorry

    Hi alan,
    Sorry, I wrongly wrote that TIME is the primary key.
    Its not the primary key and is a ordinary field only.
    There is another field as primary key. That doesnot come into the clauses.
    Yes 1064929834 always comes in that clause as a fixed number.
    Thanks a lot for your kind reply.
    I will try the select clause that you suggested for time its taking.
    Thanks again.
    Sandy.

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Create the index on time and then rewrite the clauses

    and 1064929834 - TIME >= 10
    AND EXPDT - 1064929834 > 0
    and 1064929834 - SADT >= 0

    so that the column is on its own on the LHS and move the constants to the RHS of the operator as suggested below.

    Alan

Posting Permissions

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