Sample Chapter: Efficient PL/SQL, Page 16
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
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