This discussion is archived
4 Replies Latest reply: Jun 19, 2013 2:18 AM by Etbin RSS

Optimize SQL  query

DheerajSingh Newbie
Currently Being Moderated

Hi All,

 

Please help me to optimize my below SQL  query :

 

delete from BATCH_REQUEST_RESPONSE where (SOState='ResponseReceived' or SOState='Header’ or SOState='InvalidSO’) and ACKREC='Y' and SourceAgentId=? and BATCH_REQUEST_RESPONSE.TransactionId NOT EXISTS (select transactionid from SORECORD)

 

Note: Result of (select transactionid from SORECORD ) could be till 100 K

 

Regards,

Dheeraj

  • 1. Re: Optimize SQL  query
    34MCA2K2 Journeyer
    Currently Being Moderated

    NOT EXISTS (select transactionid from SORECORD)

     

    One obvious problem here is you are misusing EXISTS, There is no Join between SORECORD & BATCH_REQUEST_RESPONSE tables. Please have a join between these two tables first below (.......)

     

     

    NOT EXISTS (select transactionid from SORECORD WHERE............)

     

    Still if the performance doesn't improve. You will need to provide a great deal of information on Tuning requests.


    See HOW TO: Post a SQL statement tuning request - template posting

     

    Regards,

  • 2. Re: Optimize SQL  query
    DheerajSingh Newbie
    Currently Being Moderated

    Hi,

    Thanks for your reply.

     

    Could you please give me a example for the same  that  might help me.

     

    Regards,

    Dheeraj

  • 3. Re: Optimize SQL  query
    34MCA2K2 Journeyer
    Currently Being Moderated

    I hope you are asking for example of proper use of NOT EXISTS

     

    delete from employee

    where not exists (select 1 from departments dept_id = employee.dept_id and status = 'ACTIVE')

     

    deletes all the rows from employees whose departments are INACTIVE. you are missing the bold part above in your query

  • 4. Re: Optimize SQL  query
    Etbin Guru
    Currently Being Moderated

    Maybe - I'm not sure what you're after

     

    delete from batch_request_response brr

    where sostate in ('ResponseReceived','Header','InvalidSO')

       and ackrec = 'Y'

       and sourceagentid = :agent_id

       and not exists(select null

                        from sorecord

                       where transactionid = brr.transactionid

                     )

     

    delete from batch_request_response brr

    where sostate in ('ResponseReceived','Header','InvalidSO')

       and ackrec = 'Y'

       and sourceagentid = :agent_id

       and 0 = (select count(*)

                  from sorecord

               ) /* if sorecord must be empty */

     

    Regards

     

    Etbin

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points