Merge Join

Hi,
I executed the following query in Northwind:
select c.* from customers c
join orders o on c.customerid=o.customerid
There are original indexes available on these two tables:
1) Clustered (Customers.CustomerID)
2) Clustered (Orders.OrderID)
3) nonClustered (Orders.CustomerID)
The execution plan shows that a nested loop is used to return the result.
But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
Now both tables are sorted based on the join predicate. But still nested
loop happens. I think Merge Join can be more efficient. Why nested loop
still is preferred by query optimizer?
Thanks in advance,
LeilaIt depends on how many rows there are. These are pretty small tables, so it
probably sees that it can return the data really fast and returns it. Just
a guess, but they don't keep looking for a plan when optimization would take
more time than running the query like the first plan they see.
--
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Leila" <leilas@.hotpop.com> wrote in message
news:%23CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I executed the following query in Northwind:
> select c.* from customers c
> join orders o on c.customerid=o.customerid
> There are original indexes available on these two tables:
> 1) Clustered (Customers.CustomerID)
> 2) Clustered (Orders.OrderID)
> 3) nonClustered (Orders.CustomerID)
> The execution plan shows that a nested loop is used to return the result.
> But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> Now both tables are sorted based on the join predicate. But still nested
> loop happens. I think Merge Join can be more efficient. Why nested loop
> still is preferred by query optimizer?
> Thanks in advance,
> Leila
>|||We have found in our software that nested loop joins have far better
performance. In fact, when we get parallelism queries back, we sometimes
will scale up the parallelism cost to 25 from 5 because the query optimizer
"goes stupid" at times.
=-Chris
"Leila" <leilas@.hotpop.com> wrote in message
news:#CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I executed the following query in Northwind:
> select c.* from customers c
> join orders o on c.customerid=o.customerid
> There are original indexes available on these two tables:
> 1) Clustered (Customers.CustomerID)
> 2) Clustered (Orders.OrderID)
> 3) nonClustered (Orders.CustomerID)
> The execution plan shows that a nested loop is used to return the result.
> But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> Now both tables are sorted based on the join predicate. But still nested
> loop happens. I think Merge Join can be more efficient. Why nested loop
> still is preferred by query optimizer?
> Thanks in advance,
> Leila
>|||If two tables are joined, and both tables have a clustered index on the
join key, the a MERGE JOIN is the most efficient join strategy (on
non-SMP systems).
I guess you were actually talking about LOOP JOINs versus HASH JOINs.
Gert-Jan
Christopher Conner wrote:
> We have found in our software that nested loop joins have far better
> performance. In fact, when we get parallelism queries back, we sometimes
> will scale up the parallelism cost to 25 from 5 because the query optimizer
> "goes stupid" at times.
> =-Chris
> "Leila" <leilas@.hotpop.com> wrote in message
> news:#CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> > I executed the following query in Northwind:
> >
> > select c.* from customers c
> > join orders o on c.customerid=o.customerid
> >
> > There are original indexes available on these two tables:
> >
> > 1) Clustered (Customers.CustomerID)
> > 2) Clustered (Orders.OrderID)
> > 3) nonClustered (Orders.CustomerID)
> >
> > The execution plan shows that a nested loop is used to return the result.
> > But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> > Now both tables are sorted based on the join predicate. But still nested
> > loop happens. I think Merge Join can be more efficient. Why nested loop
> > still is preferred by query optimizer?
> > Thanks in advance,
> > Leila
> >
> >sql