CODEWITHSUNDEEP
sqloracle-databaseperformanceplsqlmultiset
Coat
Coat

Reputation: 717

Performance of Local PL/SQL Array Vs SQL Call

If I'm using oracle sql and plsql to do computations on an employee, and then selecting more data based on the result of those computations... will it be faster to select the all the data I may need all at once when selecting the employee (assume something like 20 rows with 5 columns each) and then use that data as a local array, or select just the one row I will need when finished?

-- Example with multiple selects
declare
    l_employeeID number;
    l_birthday date;
    l_horoscope varchar2;
begin
    select employeeID into l_employeeID from employeeTbl t where t.rownum = 1;
    l_birthday := get_birthdayFromID(l_employeeID);
    select horoscope into l_horoscope from horoscopeTable t where l_birthday between l_horoscope.fromDate and l_horoscope.toDate;
    return l_horoscope;
end;

-- Example with table selection, and loop iteration
declare
    l_empolyeeID number;
    l_birthday date;
    l_horoscope varchar2;
    l_horoscopeDates date_table;
begin
    select employeeID, cast(multiset(select horoscopeDate from horoscopeTable)) as date_table into l_employeeID, l_horoscopeDates from employeeTbl t where t.rownum = 1;
    l_birthday := get_birthdayFromID(l_employeeID);
    for i in 1 .. l_horoscopeDates.count - 1 loop
        if l_birthday between l_horoscopeDates(i) and l_horoscopeDates(i + 1) then
          return l_horoscopeDates(i);
        end if;
    end loop;
    return null;
end;

I understand that I'm paying more ram and IO to select additional data, but is it more efficient than incurring another context switch to call the sql when the extra data is not significantly larger than needed?

Upvotes: 0

Views: 496

Answers (1)

Yaron Idan
Yaron Idan

Reputation: 6765

Context switches are considered very expansive when using Oracle. If the table doesn't contain large amounts of data, you should defeinitely query more data in order to reduce the number of times PL/SQL makes an SQL query.

Having said that, I think your question should be the other way around, why are you using PL/SQL at all, when your entire logic can be summed into a single SQL statement? example - 

select horoscope 
from horoscopeTable 
where (select get_birthdayFromID(employeeID) 
       from employeeTbl 
       where t.rownum = 1) between l_horoscope.fromDate and l_horoscope.toDate;

Syntax might need a little touch ups but the main idea seems right to me.

You can also find more observations in this piece about tuning PL/SQL code - http://logicalread.solarwinds.com/sql-plsql-oracle-dev-best-practices-mc08/

Upvotes: 1

Related Questions