Difference betwwen the execution od these Queries | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Difference betwwen the execution od these Queries

hi, I have "order" and "busunit" tables Order table has 417135 rows. Case 1: select * from ord
where idBusUnit in (select idBusUnit from busunit where idBusUnit < 500) It is taking 46 seconds to get the results
Case 2: select * from ord
inner join busunit
on
ord.idBusUnit = busunit.idBusUnit
and busunit.idBusUnit < 500 It is taking 1 min 23 seconds to get the results
Case 3: select * from ord
inner join busunit
on
ord.idBusUnit = busunit.idBusUnit
Where busunit.idBusUnit < 500 It is taking 1 min 46 seconds to get the results Case 4: declare @tbl table (idBusUnit int) insert into @tbl
select idBusUnit from busunit where idBusUnit < 500 select * from ord
inner join @tbl tbl
on
ord.idBusUnit = tbl.idBusUnit It is taking 1 min 46 seconds to get the results
Next time I run the same query it is taking 1.09 sec
Please tell me in each case steps how we are getting the results.
And if I am running it for the second time, then why the time taken is less than
the first one. Thanks & Regards,
butu
You can observe how you get the results by hitting Ctrl+K and examine the execution plan.
As for the second time: The magic words here are compilation and caching. You will almost always observe that subsequent calls to the same query are faster in returning the resultset. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
]]>