The below query retrieves 5000 ids.Need to fine tune in such a way that it takes less time to execute.
select top 5000 ids from table1 t1 join table2 t2 on t1.class=t2.class and t1.pid = t2.pid and isnull(t.bkngpur, '') !='ABC' and (t.rspid = p.spid or (isnull(t.rspid,'') = isnull(p.spid,''))) left join table3 t3 on t.cpid1 = c.cpid left join table4 t4 on t.cpid1 = cp.cpid where status in ('A','B') and Tid != 'PR%' and exDate > (select curdate from table5) and p.rep = 1 and (rrtor in( 'ABC','DEF') or (( Tid in (select tsid from table5 where bid in (select lbid from table6 where pbid in ('abc','def', 'ghi')) or t4.indc = 1)) or (ids in (select tsid from table7 where bid in ( select lbid from table6 where pbid where pbid in ('abc','def', 'ghi') or t4.indc =1) ) )) and t3.col1 in (NULL,'XYZ) and t1.tpid in ('P','Q','R','S') order by 1
Thanks in advance
Thanks for your response.
1.This Query only gives a list of ids.
1 2 3 . . . . 5000
Well this query is taking ~7 seconds to complete.Need to rewrite the query using joins instead of sub queries.
answered 12 Mar '14, 11:21