CODEWITHSUNDEEP
sqloracle-database
Joe Green
Joe Green

Reputation: 207

sql slow performance with function

I have following function:

Create function code_status
( p_code varchar, p_daytime date);

Select status into l_value from table1 where code=p_code and daytime=p_daytime;

Return l_value;

End;

And I use it in sql query below:

Select code, daytime, code_status(code, daytime) from table2
where daytime = '12 Jan 2017'

Query with this function runs very slow, is there any way to improve it, I dont think I need function here?

Thanks

Upvotes: 0

Views: 555

Answers (2)

tbone
tbone

Reputation: 15473

The function really isn't needed. It basically pulls from a lookup table (table1) to get a single value (status). So, join to it instead to get status, something like:

Select t2.code, t2.daytime, t1.status 
from table2 t2
left join table1 t1 on (t1.code=t2.code and t1.daytime=t2.daytime)
where t2.daytime = to_date('12 Jan 2017', 'DD Mon YYYY');

where table1 is your lookup table for status, and table2 is the driving table.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270021

For this query:

Select status into l_value
from table1
where code = p_code and daytime = p_daytime;

You want an index. The best index is table1(code, daytime, status).

Upvotes: 0

Related Questions