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

    Unanswered: Query Optimization

    Hi,
    The sms_dispatcher table is having cmtime,sendafterdate,expirydate as numeric fields,none of them is PK. Anotherfield is PK. I tried taking index of cmtime but it didnt give me much efficient results. So how exactly can the queries be modified to get an optimization in performance ?

    update SMS_DISPATCHER set CMTIME = 1064929834 where (CMTIME is NULL or 1064929834 - CMTIME >= 10) and (1064929834 - SENDAFTERDATE >= 0) and 1064929834 not in (select CMTIME from SMS_DISPATCHER where CMTIME is not NULL)

    SELECT * from SMS_DISPATCHER where (CMTIME is not NULL and (CMTIME = 1064929834 OR 1064929834 - CMTIME >= 10)) AND (EXPIRYDATE - 1064929834 > 0 and (STATUS = 1 OR STATUS = 0) and 1064929834 - SENDAFTERDATE >= 0)

    Txs n Regds
    Sandy

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    I see some performance problems in your first update, but I don't know how to solve them for you. I'll just give you my thoughts, maybe you can figure out a better way for the UPDATE.

    **where ( CMTIME is NULL or 1064929834 - CMTIME >= 10)
    You're using the is null operator. Unfortunately, Oracle indexes do not store NULL values, so for this part of the where clause, Oracle has to search through the entire table (which might be expensive).

    **and 1064929834 not in (select CMTIME from SMS_DISPATCHER where CMTIME is not NULL)
    Here you use the not in operator. This one is also expensive, since the subquery may not return a single row so the complete query has to be executed and fetched. Also in the subquery you use again the NULL operator, which is not stored in an index, thus resulting the subquery to perform a full table scan. And the full table scan has to complete because of the not in operator.

    Another problem might be that you 'format' indexed columns.
    For example : EXPIRYDATE - 1064929834 > 0
    Now the value of the indexed column is changed and the this changed value will not be in the index so the index is not used.
    Change this to EXPIRYDATE > 1064929834 to have the same result and better performance.

    Maybe in your case this update can not be tuned any better, maybe it can. You know best. I can though give some hints.

    - NOT IN : sometimes can be changed to NOT EXISTS. This is often faster, since the not exists allready fails when one row is returned and the query stops executing.
    - try not to modify indexed columns.
    - Check to see if it is possible to reference indexed columns.
    - Try changing the way the columns are updated from SQl to PL/SQL. Try to program in a way that the PK of the record to be updated is retrieved very fast and then update that record identified by the primary key.


    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Sep 2003
    Posts
    20

    not exists

    Hi,
    as u suggested we tried not exists in place of not in.
    But it shows error when the query is changed like this.

    update SMS_DISPATCHER set CMTIME = 1064929834 where (CMTIME is NULL or 1064929834 - CMTIME >= 10) and (1064929834 - SENDAFTERDATE >= 0) and 1064929834 not exists (select CMTIME from SMS_DISPATCHER where CMTIME is not NULL)

    Hope you can suggest how to rewrite it using not exists.

    Txs n Regds.
    Sandy..

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    try

    and not exists (select CMTIME from SMS_DISPATCHER where CMTIME=1064929834 )


    Alan

  5. #5
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    alan is right.
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

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