The below query retrieves 5000 ids.Need to fine tune in such a way that it takes less time to execute.

selecttop5000idsfromtable1t1jointable2t2ont1.class=t2.classandt1.pid=t2.pidandisnull(t.bkngpur,'')!='ABC'and(t.rspid=p.spidor(isnull(t.rspid,'')=isnull(p.spid,'')))leftjointable3t3ont.cpid1=c.cpidleftjointable4t4ont.cpid1=cp.cpidwherestatusin('A','B')andTid!='PR%'andexDate>(selectcurdatefromtable5)andp.rep=1and(rrtorin('ABC','DEF')or((Tidin(selecttsidfromtable5wherebidin(selectlbidfromtable6wherepbidin('abc','def','ghi'))ort4.indc=1))or(idsin(selecttsidfromtable7wherebidin(selectlbidfromtable6wherepbidwherepbidin('abc','def','ghi')ort4.indc=1))))andt3.col1in(NULL,'XYZ) and t1.tpid in ('P','Q','R','S')orderby1

Could you run the query from Interactive SQL and gather the execution plan? The plan viewer can be accessed using Tools >> Plan Viewer. From this window you should change the statistics level to 'Detailed and node statistics' and get the plan. The result can be saved and uploaded.

Historically, we've sometimes used UNION to speed up queries with different disjuntive conditions that perform rather well on their own (say, by using index scans) and don't perform well when ORed, something like

ids

Please answer the questions that Mark and Mikel asked. If you don't answer those questions, there isn't much chance you will get an answer.

"Rewriting the query using joins instead of subqueries" may or MAY NOT help, since the query optimizer often does that automatically... that is like asking for painkillers without answering any medical questions.