17 September 2010

Players offer another, better answer to the 16 September quiz (1384)

This quiz was focused on the use of the EXISTS method to avoid a NO_DATA_FOUND exception when trying to "read" an element at a given index value. But all the choices showed ways of looping through the contents of a collection. Several players wrote to remind me that if you want to loop through a collection that is sparse, the best way to do so is to use the FIRST, LAST, PRIOR, NEXT methods. In other words, instead of this:
DECLARE
  l_sons   ages_pkg.sons_t;
BEGIN
  l_sons (19860923).name_of_son := 'Eli';
  l_sons (19860923).age_of_son := 23;  
  l_sons (19720425).name_of_son := 'Chris';
  l_sons (19720425).age_of_son := 38;

  FOR indx IN l_sons.FIRST .. l_sons.LAST
  LOOP
     show_age (l_sons, indx);
  END LOOP;
END;
You would write:
DECLARE
  l_sons   ages_pkg.sons_t;
l_index  PLS_INTEGER;
BEGIN
  l_sons (19860923).name_of_son := 'Eli';
  l_sons (19860923).age_of_son := 23;  
  l_sons (19720425).name_of_son := 'Chris';
  l_sons (19720425).age_of_son := 38;

  l_index := l_sons.FIRST;
WHILE (l_index IS NOT NULL)
  LOOP
  LOOP
     show_age (l_sons, l_index);
l_index := l_sons.NEXT (l_index);
  END LOOP;
END;
Here are the remarks submitted by Jeff Kemp: Steven, you wrote "Generally, the best way to handle such situations is to call the EXISTS method to verify that am element exists at the specified index value, thereby avoiding the raising of a NO_DATA_FOUND exception" Certainly, this is one way to solve the problem, but when I am dealing with a sparsely-populated array I prefer to forego the FOR LOOP and use the .NEXT property of the array to jump from each index directly to its successor. (Alternatively, there is also the INDICES OF syntax for the FOR LOOP that also avoids all the extraneous EXISTS checks) If there are hundreds of millions indexes between two entries in the array, your loop will test the EXISTS method for each and every one of them; sure, on modern processors this will not take much time, but I'm willing to bet (and I think I will test this when I get a chance) that a measurable performance improvement could be had by avoiding all the unnecessary EXISTS checks. Just my $0.02 :) I agree completely. I should have pointed that out, and will do so in the answer text. Anyone else care to comment? SF

5 comments:

  1. I should correct myself, the INDICES OF syntax, of course, only applies when using a FORALL :)

    ReplyDelete
  2. Quite a big difference in performance (tested on 11gR1 running on my laptop):

    set timing on
    prompt FOR LOOP with no_data_found handler
    declare
    type t is table of number index by binary_integer;
    r t;
    begin
    r(1000000) := 1;
    r(2000000) := 2;
    for i in r.first..r.last loop
    begin
    r(i) := r(i) + 1;
    exception
    when no_data_found then
    null;
    end;
    end loop;
    end;
    /
    prompt LOOP with FIRST/NEXT
    declare
    type t is table of number index by binary_integer;
    r t;
    i binary_integer;
    begin
    r(1000000) := 1;
    r(2000000) := 2;
    i := r.first;
    loop
    exit when i is null;
    r(i) := r(i) + 1;
    i := r.next(i);
    end loop;
    end;
    /

    FOR LOOP with no_data_found handler
    anonymous block completed
    3,625ms elapsed
    LOOP with FIRST/NEXT
    anonymous block completed
    0ms elapsed

    The difference might be less if this code were to be natively compiled.

    ReplyDelete
  3. In my opinion, we cannot generalize. It would depend upon how dense the array is.

    I find using "exception when_no_data_found" to be advantageous in most of my situations.
    My customer table is sparsely-populated, but more or less dense. (The table gets inserted based on a sequence, but if customer closes the record the row is deleted. So for E.g. if my table contains customer_no from 0 to 10000, normally there would be 9000+ records.) In this scenario, I prefer to handle no_data_found, than adding a bit more load on the processor for array lookup using .NEXT or .EXISTS methods.


    prompt sparse first/next
    declare
    t dbms_sql.varchar2_table;
    n number;
    m number :=0;
    begin
    for i in 1..1000000
    loop
    if mod(i,100) != 0
    then
    t(i) := null;
    end if;
    end loop;
    dbms_output.put_line('count = '||t.count);

    n := t.first;
    loop
    m := m+1;
    if n = t.last
    then
    exit;
    end if;
    n := t.next(n);
    end loop;
    dbms_output.put_line('m = '||m);
    end;
    /



    prompt sparse handling no_data_found
    declare
    t dbms_sql.varchar2_table;
    n number;
    m number := 0;
    begin
    for i in 1..1000000
    loop
    if mod(i,100) != 0
    then
    t(i) := null;
    end if;
    end loop;
    dbms_output.put_line('count = '||t.count);

    for i in t.first .. t.last
    loop
    begin
    n := t(i);
    m := m + 1;
    exception when no_data_found
    then
    null;
    end;
    end loop;
    dbms_output.put_line('m = '||m);
    end;
    /

    prompt truly dense first/next

    declare
    t dbms_sql.varchar2_table;
    n number;
    m number :=0;
    begin
    for i in 1..1000000
    loop
    --if mod(i,100) != 0
    --then
    t(i) := null;
    --end if;
    end loop;
    dbms_output.put_line('count = '||t.count);

    n := t.first;
    loop
    m := m+1;
    if n = t.last
    then
    exit;
    end if;
    n := t.next(n);
    end loop;
    dbms_output.put_line('m = '||m);
    end;
    /



    prompt truly dense handling no_data_found
    declare
    t dbms_sql.varchar2_table;
    n number;
    m number := 0;
    begin
    for i in 1..1000000
    loop
    --if mod(i,100) != 0
    --then
    t(i) := null;
    --end if;
    end loop;
    dbms_output.put_line('count = '||t.count);

    for i in t.first .. t.last
    loop
    begin
    n := t(i);
    m := m + 1;
    exception when no_data_found
    then
    null;
    end;
    end loop;
    dbms_output.put_line('m = '||m);
    end;
    /

    sparse first/next
    count = 990000
    m = 990000

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:04.42
    sparse handling no_data_found
    count = 990000
    m = 990000

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:03.11
    truly dense first/next
    count = 1000000
    m = 1000000

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:03.01
    truly dense handling no_data_found
    count = 1000000
    m = 1000000

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:01.62



    To me "exception no_data_found" does not mean "I dont care!", but it says "Its OK!"

    ReplyDelete
  4. My above comment was for comparing ".next" approach to "exception when no_data_found"

    But when I compare ".exists" to "exception when no_data_found", I would always prefer to use the latter.

    For the .exists approach, we are looking up the array 2 times.

    if tb.exists(i) --first
    then
    x := tb(i); --second


    as against once in the when_no_data_found approach:

    begin
    x := tb(i); --once
    exception when no_data_found
    then
    null;
    end;

    Of these two, won't the EWNDF one be always better, from a performance stand point?

    ReplyDelete
  5. Excellent points, Spoon. You definitely need to look at the data that you are manipulating. Different patterns can easily lead to different implementations.

    I would only add that when you do this, be sure to encapsulate the implementation so that you can change it as easily as possible as the underlying data changes.

    And, yes, I would expect that an occasional NDF trapping would be more efficient than calling exists for every element.

    SF

    ReplyDelete