Use function or direct column name in sql

Question

I have a question regarding usage of functions in a sql query.

I have the following function for getting username from a table

CREATE OR REPLACE
  FUNCTION get_info(
      p_emp_no employees.emp_id%TYPE)
    RETURN VARCHAR2
  AS
    o_userid employees.emp_name%TYPE;
  BEGIN
    SELECT emp_name INTO o_userid FROM employees WHERE emp_id = p_emp_no;
    RETURN o_userid;
  END;
  /

when I use the above function in sql code as

select product_no, get_info(p.raised_by)
from products p, employees e
where p.raised_by  = e.emp_no

execution takes more time than the below sql

select product_no, e.emp_name
from products p, employees e
where p.raised_by  = e.emp_no

The second query is faster than the first query.

So is it advisable to use functions to get username from a table or use direct column name and use join to link with employees table?

Reason why I asked is there could be multiple references to employees table to get username for raised_by, created_by, modified_by etc. So I thought it would be better to use a function to get emp_name instead of multiple joins with employees table.

Any insight into this is highly appreciable.

Thanks

Garfield · Accepted Answer · 2012-12-18 05:47:58Z

4

Using a join is the way to go. Using the function will trigger a separate query with a separate index lookup for each row result, greatly slowing down the results.

I understand your desire to avoid multiple joins, but in your example, three joins would be better than three function calls.

Think of it this way. The function is doing it's own join on

WHERE emp_id = p_emp_no

And it is not only doing it once, but a separate join for EVERY SINGLE ROW!

answered Dec 18, 2012 at 5:47

Garfield

1,2323 gold badges13 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use function or direct column name in sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related