faster select -Reply

[Richard Watts <rwatts@MDC.COM>]

Hi Angel,

I don't know what db your on, but if it is Oracle the answer is
"it depends".  Since the exists is a correlated sub-query it
will be performed for every row of data returned in the main
query, following a nested loop execution plan.  This is similar
to the solution offered earlier, using an SQR cursor based
approach and looping through the main query and executing
the secondary query, the only difference being where you
spend your CPU.  For large volume tables the looping
approach will almost always be slower.  If the list of master
shops is shorter than the list of shops then I would
implement the 'in' clause, which will cause a sort-join,
retrieving a distinct list of master shops first then returning all
shops that are master shops.  If the second database is
remote then there is only one call to it instead of many.

>>> Angel Guzman <Angel_Guzman@TRFB.NAVY.MIL>
01/29/97 01:02pm >>>
Which of these selects would be more efficient/faster in
comparing data from two different databases?

  from tt_shop
 where department = $dept
   and shop in (select master-shop from ldsloc.dbo.tt_shop)
!  and exists  (select master-shop from ldsloc.dbo.tt_shop
!                where master-shop = shop)
 order by department, division, branch, shop

Angel