December 2, 2010 at 9:33 am
hi
Have a look at this sample which one is better
whether we can give @i1 in join is it is good
Declare @i1 int
Declare @i2 int
set @i1=2010
set @i2=123
Select name,salary,month from employee_tbl as e inner join salary_tbl as s
on e.empid=s.empid
and e.cmpid=s.cmpid
where e.cmpid=@i1 and s.salyear=@i2
-- OR
Select name,salary,month from employee_tbl as e inner join salary_tbl as s
on e.empid=s.empid
and e.cmpid=@i1
where e.cmpid=@i1 and s.salyear=@i2
I see in the execution plan but there is no difference since it is less data we cant able to say which is better.If huge data's are there then what we can do
Thanks
Parthi
Thanks
Parthi
December 2, 2010 at 1:02 pm
having a choice I would tend to favor the second version. It has the potential (depending upon table structure) to be faster.
The probability of survival is inversely proportional to the angle of arrival.
December 2, 2010 at 4:54 pm
I'd actually use the join if the keys are unique together (concatinated key).
The optimizer is already going to optimize for the literal on e.cmpid and s.salyear, and will choose a join operator based on the index and stats of both keys. This could actually cause a bad plan if cmpid is not evenly distributed in the set (parameter sniffing).
I'm guessing that empid and cmpid are a concatinated key, you may actually get different results with the second one if there are more rows where s.empid has multiple s.cmpid(s) since your filter only applies to the employee table.
If empid is unique in both tables, then you don't need the e.cmpid criteria in both the join and the where - either would do the same thing.
If you wanted another way, you could do:
Select name,salary,month from employee_tbl as e inner join salary_tbl as s
on e.empid=s.empid
and e.cmpid=s.cmpid /* take this line out if empid is unique */
and e.cmpid=@i1
and s.salyear=@i2
Join and where clauses cause the optimizer to evaluate keys and statistics in pretty much the same way ...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply