Rewriting using joins

[Former Member]

Hi,

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

[MarkCulp]

It would help if:

1) you explained in words what the query was trying to do

2) show us your schema

3) give us an idea of the sizes of each table

What have you tried? How long does it currently take? ... and lots of other questions (that I'm sure others will ask once you tell us the above).

[former_SQLA_member1694878]

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.

[VolkerBarth]

4) What version do you use?

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

SELECT id from x join y where x.col = <complex-condition1>
UNION
SELECT id from x join y where y.col1 = <complex-condition2>
UNION
SELECT id from x join y where y.col2 = <complex-condition3>

[Former Member]

Hi,

Thanks for your response.

1.This Query only gives a list of ids.

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.

[Breck_Carter]

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.