By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,850 Members | 972 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,850 IT Pros & Developers. It's quick & easy.

Querry optimization to have better performance

P: 36
I have the folowing 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
having emp_rid 10234.
For this I have two ways of writing querries.

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?
Sep 25 '08 #1
Share this Question
Share on Google+
2 Replies


debasisdas
Expert 5K+
P: 8,127
you need to use method 1 .
Sep 25 '08 #2

P: 36
you need to use method 1 .

As I understand,
In first method all the rows in the first table is taken into joining, so all the records of employee_master table has to be joined with salary_payment. Then it does the filtering out of emp_rid to match 10234.

In the second method only one record from employee_master table will be considered and then the join operation is less expensive.

So the second method will be better. I have no solid idea of it.
Correct me if I am wrong.
Sep 25 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.