5.5 Analyzing the Impact of Bulk Operations

Now that you know all about FORALL and BULK COLLECT, let's see if it's really worth the time to learn these new features. These statements are supposed to provide significant performance improvements over the context-switch-heavy, row-by-row processing of earlier versions of PL/SQL.

Let's run some tests to document the gains by running the bulktiming.sql script (using the PLVtmr package described in the Preface , in ).

Leaving out portions of the script not central to the timing, here is the code I used to compare the performance of a FOR loop and FORALL:

/* Filename on companion disk: bulktiming.sql */
BEGIN
   /* Load up the collection. */
   FOR indx IN 1..num LOOP
      pnums(indx) := indx;
      pnames(indx) := 'Part ' || TO_CHAR(indx);
   END LOOP;
   
   /* Do a bunch of individual inserts. */
   PLVtmr.capture;
   FOR indx IN 1..num LOOP
      INSERT INTO parts VALUES (pnums(indx), pnames(indx));
   END LOOP;
   PLVtmr.show_elapsed ('FOR loop');
   
   ROLLBACK;
   
   /* Perform the inserts via FORALL */
   PLVtmr.capture;
   FORALL indx IN 1..num
      INSERT INTO parts VALUES (pnums(indx), pnames(indx));
   PLVtmr.show_elapsed ('FORALL');
   
   ROLLBACK;
END;

And here are the results (drumroll, please!) for the specified number of rows (1000, 10,000, and 20,000):

FOR loop 1000 Elapsed: .39 seconds.
FORALL 1000 Elapsed: .05 seconds.

FOR loop 10000 Elapsed: 5.73 seconds.
FORALL 10000 Elapsed: .79 seconds.

FOR loop 20000 Elapsed: 10.34 seconds.
FORALL 20000 Elapsed: 1.49 seconds.

The results indicate that we can expect a single order of magnitude improvement in performance, always something to appreciate. And the timings increase in a linear fashion, giving us a comfortable feeling as to the scalability of this feature.

Finally, here is the script (minus various administrative tasks; see bulktiming.sql for all the details) I wrote to compare row-by-row fetching and BULK COLLECT:

/* Filename on companion disk: bulktiming.sql */
BEGIN
   /* Fetch the data row by row */
   PLVtmr.capture;
   FOR rec IN (SELECT * FROM parts)
   LOOP
      pnums(SQL%ROWCOUNT)  := rec.partnum;
      pnames(SQL%ROWCOUNT) := rec.partname;
   END LOOP;
   PLVtmr.show_elapsed ('Single row fetch '|| num);
 
   /* Fetch the data row by row */
   PLVtmr.capture;
   SELECT * BULK COLLECT INTO pnums, pnames FROM parts;
   PLVtmr.show_elapsed ('BULK COLLECT '|| num);   
END;

The results are as follows for the specified number of rows:

Single row fetch 1000 Elapsed: .14 seconds.
BULK COLLECT 1000 Elapsed: .02 seconds.

Single row fetch 10000 Elapsed: 1.56 seconds.
BULK COLLECT 10000 Elapsed: .4 seconds.

Single row fetch 20000 Elapsed: 2.75 seconds.
BULK COLLECT 20000 Elapsed: 1.48 seconds.

Single row fetch 100000 Elapsed: 18.91 seconds.
BULK COLLECT 100000 Elapsed: 85.18 seconds.

Again, we see improvements in performance, but notice that the gains through BULK COLLECT diminish with high numbers of rows. In fact, for 100,000 rows, BULK COLLECT was actually much slower than the single-row fetch. I am not sure what might be causing this slowdown.