SQL Server Performance Forum – Threads Archive
Inner Join Performance…
Hi,i have a question on performance regd Inner Joins. Tables #Rows
customer 91
orders 830
orderdetails 2155
Say, i use joins on the above tables in Northwind DB as select customers.city,orders.orderdate,[Order Details].productid
from [Order Details] inner join orders
inner join customers on
customers.customerid = orders.customerid on
orders.orderid = [Order Details].orderid
where customers.customerid = ‘CACTU’ Above query seems to be faster than the following query. select customers.city,orders.orderdate,[Order Details].productid
from customers inner join orders
inner join [Order Details] on
orders.orderid = [Order Details].orderid on
customers.customerid = orders.customerid
where customers.customerid = ‘CACTU’ 1. Does the number of rows returned from innermost JOIN have a bearing on the query performance ? 2.Also, can someone tell me the importance of
"Time Statistics
Cumulative client processing time
Cumulative wait time on server replies" in SHOW Query Statistics pane? CPU time for both queries is 0 ms while elapsed time alone differs. thanks
Anand
Hi Anand, I’m not sure about the second question, but in answer to your first. The two queries are subtly different. The problem is not so much whether the join returns a different number of rows, but whether the where clause can be used to reduce them. In your first case, SQL is likely to read the customers table for a customer with an id of CACTU, and then do the joining. In the second query the orders table can’t be restricted so it has to be joined in its entirety to the order details table. SQL doesn’t seem to convert this ANSI style join to the older style join and then optimise it, so whether you place a clause in the WHERE versus the ON area does have an impact. I’d suspect that if you change the second query to select customers.city,orders.orderdate,[Order Details].productid
from customers inner join orders
inner join [Order Details] on
orders.orderid = [Order Details].orderid on
customers.customerid = orders.customerid
and customers.customerid = ‘CACTU’ that this query would perform as fast as the first one. In general only put clauses which refer to the inner most table in the where clause, put all others in their respective ON clause Cheers
Twan
]]>