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

    Unanswered: How can it be optimized ??

    update TABLE_TEST set TIME = 1064929834 where (TIME is NULL or 1064929834 - TIME >= 10) and (1064929834 - SADT >= 0) and 1064929834 not in (select TIME from TABLE_TEST where TIME is not NULL)

    SADT->NUMBER(38);
    TIME->NUMBER(38);
    Last edited by skdas; 09-26-03 at 11:04.

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

    update TABLE_TEST set TIME = 1064929834
    where (TIME is NULL or 1064929834 - TIME >= 10)
    and (1064929834 - SADT >= 0)
    and 1064929834 not in (select TIME from TABLE_TEST where TIME=1064929834)

    and if you havent already putting an index on TIME might help (depending on your data volume/distribution). And dont forget to analyze if you add a new index.

    If the OR clause is particularly slow try splitting it into two update statements, on for time is null and another for the other criteria.

    Other thing to do would be to look into the indexing a bit more, looking at the table storage settings etc.

    Alan

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Could do a not exists on the subquery instead.

    Alan

  4. #4
    Join Date
    Sep 2003
    Posts
    20

    already indexed

    Thanks a lot for the reply.
    The TABLE_TEST has the TIME field as the primary key. So its automatically indexed. The table has some 200000 records.
    I will try splitting the or clause.
    I will try the modified query you have given again a lot of thanks.
    Sandy
    Last edited by skdas; 09-26-03 at 11:43.

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If time is the primary key why the test for time is null?

    Alan

  6. #6
    Join Date
    Sep 2003
    Posts
    20

    sorry TIME not Primary key

    Hi Alan,
    I had the original query

    update TABLE_TEST set TIME = 1064929834 where (TIME is NULL or 1064929834 - TIME >= 10) and (1064929834 - SADT >= 0) and 1064929834 not in (select TIME from TABLE_TEST where TIME is not NULL)

    You suggested

    update TABLE_TEST set TIME = 1064929834
    where (TIME is NULL or 1064929834 - TIME >= 10)
    and (1064929834 - SADT >= 0)
    and 1064929834 not in (select TIME from TABLE_TEST where TIME=1064929834)

    I tried it and found that it doesnot give a quicker time.
    So what other methods can be applied to optimize it ?
    How exectly the exists clause can be applied ? would it be faster ?

    The table has another field as primary key and it doesnot appear in the clause.The time field is a general field and not indexed.Since i have a primary key(though other than the fielsd involved in the query) i think it
    is automatically indexed according to its PK.
    So what should i do to run it faster ?
    Waiting for your reply and thanking for your help,
    Sandy..

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Then create an index on time (see create index in your sql manual) and analyze it afterwards then the query I suggested should be much quicker as it doesnt have to do a full table scan. Also try not exists for the subquery as that might help aswell.

    Alan

  8. #8
    Join Date
    Sep 2003
    Posts
    20

    THANKS ALAN

    Thanks Alan,
    I would do the indexing as suggested by you.
    and try your suggested query for the select clause.
    Thanks again for your time and help.
    Regards,
    Sandy..

Posting Permissions

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