Querry optimization to have better performance

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • getmeidea
    New Member
    • Feb 2007
    • 36

    Querry optimization to have better performance

    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?
  • debasisdas
    Recognized Expert Expert
    • Dec 2006
    • 8119

    #2
    you need to use method 1 .

    Comment

    • getmeidea
      New Member
      • Feb 2007
      • 36

      #3
      Originally posted by debasisdas
      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.

      Comment

      Working...