October 6, 2008 at 10:33 pm
I have the following two tables.
employee_master(
emp_rid int primary key identity,
emp_no char(20) not null,
emp_name varchar(100)
);
salary_payment(
sp_rid int primary key,
sp_emp_rid int COMMENT 'Maping employee_master.emp_rid',
sp_pay_date date COMMENT 'Payment Date',
sp_amount varchar(100)
);
Assume in index is created on the fields salary_payment(sp_emp_rid).
Here I need to get all the salary payment details for particular employee
with emp_rid 10234.
For this I have two ways of writing queries.
Method 1:
select * from employee_master
join salary_payment on emp_rid = sp_emp_rid
where emp_rid = 10234
Method 2:
select * from employee_master
join salary_payment
on (emp_rid = 10234 and emp_rid = sp_emp_rid)
Question:
Will these methods make the difference in performance, if yes, how?
October 7, 2008 at 1:02 am
In this example, there may not be any performance differences. But if the query contains a number of other joins too, there will be differences.
JOIN clause is evaluated before WHERE is evaluated. If your query contains a number of joins, you might get better performance by moving the filter to the ON clause of the JOIN. By filtering rows in the join itself produces lesser number of rows to be processed at the next join.
Again, the query optimizer very smart and might decide to take a different path than what you would expect. You should really look at the execution plan to see if a given query is better than another.
.
October 7, 2008 at 2:34 am
jacob sebastian (10/7/2008)
JOIN clause is evaluated before WHERE is evaluated. If your query contains a number of joins, you might get better performance by moving the filter to the ON clause of the JOIN. By filtering rows in the join itself produces lesser number of rows to be processed at the next join.
Most of the time the optimiser will push the where clause predicates right down to the point where the seek/scan is done. The filters for the join may be done at the same time, depending on the type of join (specifically for nested loop) or the join may be done later
With an inner join, filter in the join or filter in the where produce the same plan and the same results. With an outer join, they may produce different results as the where is evaluated before the join and hence different number of rows may qualify for the join.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2008 at 2:42 am
As an example:
select c.AccountNumber, soh.OrderDate, soh.SalesOrderNumber from Sales.Customer c
inner join Sales.SalesOrderHeader soh on c.CustomerID = soh.CustomerID
where SalesPersonID = 282 and c.TerritoryID = 6
select c.AccountNumber, soh.OrderDate, soh.SalesOrderNumber from Sales.Customer c
inner join Sales.SalesOrderHeader soh on c.CustomerID = soh.CustomerID and SalesPersonID = 282 and c.TerritoryID = 6
The plans of the two are identical. The filter on the customer table is done in the index seek (it's the seek predicate) and the filter on SalesOrderHeader is done after the clustered index scan, but before the merge join.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2008 at 3:17 am
agreed.
.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply