Sample Chapter: Efficient PL/SQL

Now we can simply generate any size employee/department base for our test and get the average run time for the procedure based on three executions. Let's look at the output:

SQL> @c:\reptest 500 50
Average run time:.3

PL/SQL procedure successfully completed.

SQL> @c:\reptest 1000 100
Average run time:.93

PL/SQL procedure successfully completed.

SQL> @c:\reptest 1500 150
Average run time:1.87

PL/SQL procedure successfully completed.

SQL> @c:\reptest 2000 200
Average run time:3.3

PL/SQL procedure successfully completed.

SQL> @c:\reptest 2500 250
Average run time:4.96

PL/SQL procedure successfully completed.

The developer's apparently fast piece of code scales very poorly. In fact, running a large number of tests shows that the elapsed time increases exponentially with a linear increase in the employee numbers. Given that the program was supposed to run within three seconds, we can see that (on the hardware used for testing) we have a 1900 employee limit on the solution. After this, our solution no longer works because it takes longer than three seconds.

Luckily, we caught this flaw in our testing, so the poor developer is marched into the office and told to improve the performance of his code. Here is where the second fundamental problem occurs.

Improving a Procedural Solution Yields another Procedural Solution

If you want to make things run faster, you need to do less work. Our developer observes that most of the work appears to be queries to the emp table, so if he can avoid that, he can improve performance. As a first cut, he can merge the two queries that look up average and minimum salaries:

SQL> create or replace
  2  procedure report_sal_adjustment2 is
  3  v_avg_dept_sal emp.sal%type;
  4  v_min_dept_sal emp.sal%type;
  5  v_dname        dept.dname%type;
  6  cursor c_emp_list is
  7    select empno,ename,deptno,sal,hiredate
  8    from emp;
  9 begin
 10   for each_emp in c_emp_list loop

Here we can pick up the department name and minimum salary at the same time we get the average salary.

 11   select avg(emp.sal),min(emp.sal),dept.dname
 12   into v_avg_dept_sal,v_min_dept_sal,v_dname
 13   from  dept,emp
 14   where dept.deptno =each_emp.deptno
 15   and   emp.deptno =dept.deptno
 16   group by dname;

The rest of the code is unchanged.

 17     if abs(each_emp.sal -v_avg_dept_sal )/v_avg_dept_sal >0.20
           then
 18       if v_min_dept_sal =each_emp.sal then
 19         insert into emp_sal_log
 20         values (each_emp.ename,each_emp.hiredate,
 21                 each_emp.sal,v_dname,'Y');
 22       else
 23         insert into emp_sal_log
 24         values (each_emp.ename,each_emp.hiredate,
 25                 each_emp.sal,v_dname,'Y');
 26       end if;
 27     end if;
 28   end loop;
 29 end;
 30 /

Procedure created.

We adjust the REPTEST.SQL script to now call the new report_sal_adjustment2 procedure. Running this through for similar employee and department numbers yields the results shown in Table 1-3.

Table 1-3. Results of Changes to REPORT_SAL_ADJUSTMENT

---

EMPLOYEES	DEPARTMENTS	ELAPSED TIME
500	50	0.27
1000	100	0.86
1500	150	1.68
2000	200	3.03
2500	250	4.64

We can see that performance has improved by approximately 10 percent, which raises the employee ceiling to around 2000, but that's hardly a massive gain. What if there are 50,000 employees in the organization?

Encouraged by the gains on the first bout of tuning, our developer can explore further alternatives. We have seen that running less SQL against the emp table seems to make things run faster. Perhaps an even more efficient solution will be to pre-fetch all the average department salaries into a memory-based lookup table so no additional SQL will be required. Being a savvy developer who always keeps up with the latest and greatest PL/SQL functionality, he knows that this can be achieved using a collection. With some additional code, we arrive at a new solution.

SQL> create or replace
  2  procedure report_sal_adjustment3 is

Now we need some type definitions to hold an array (or list) of departmental details, namely the average salary, the minimum salary, and the department name. This is done in two sections—a record to hold each row of details, and an array of those records.

  3    type dept_sal_details is record (
  4      avg_dept_sal emp.sal%type,
  5      min_dept_sal emp.sal%type,
  6      dname        dept.dname%type );
  7    type dept_sals is table of dept_sal_details
  8        index by binary_integer;
  9    v_dept_sal dept_sals;
 10    cursor c_emp_list is
 11      select empno,ename,deptno,sal,hiredate
 12      from emp;

We have a new cursor to retrieve the department summary details—this will be the source of the entries we will add to our PL/SQL table.

 13    cursor c_dept_salaries is
 14      select avg(sal)asal,min(sal)msal,dname,dept.deptno
 15      from dept,emp
 16      where emp.deptno =dept.deptno
 17      group by dname,dept.deptno;
 18 begin

Page 16 of 19