Slow When Bulk Inserting Records to Database Using Lotus Notes Agent

I don't do Lotus Notes program. But our Notes developers told me that it was extremely slow to insert records into Oracle (SQL Server as well) database. One example is that 1 million rows took 8 hours.

After some investigation, I found that they were constructing full SQLs, instead of using parameters. Below is a piece of their code:

    Dim con As ODBCConnection
   Dim qry As ODBCQuery
   Dim result As ODBCResultSet

   Set qry = New ODBCQuery
   Set result = New ODBCResultSet
   con.ConnectTo("")
   Set qry.Connection = con

   for each document loop
       qry.SQL = "insert into person (fname,lname) values( '" & v_fname & 
                  "','" & v_lname & "' )"
       Set result.Query = qry
   end loop
   ...

The high-lite is the trouble. Whenever this was called, I noticed so many queries on Oracle data dictionary views. It seems to me that Notes was parsing the SQL, and the second statement sometimes took about 2 seconds.

To solve it, I did some research and found the parameters was useful.

    qry.SQL = "insert into person (fname,lname) values( ?fname?, ?lname? )"
    Set result.Query = qry
    for each document loop
        Call result.SetParameter( fname, "'" & v_fname & "'" )
        Call result.SetParameter( lname, "'" & v_lname & "'" )
    end loop

The performance improved greatly, one example is 30K records reduced time from 4 hours to 10 minutes. Still high, but Notes spends most of the time preparing data.

Accessing Non-exsits Item in Oracle Associative Array

Look at this piece of code:

 set serveroutput on
 declare
  type MONTH_TYPE is table of varchar(20) index by binary_integer;

  month_table   MONTH_TYPE;
begin
  month_table(1) := 'Jan';
  month_table(2) := 'Feb';

  if month_table(3) is null then
    dbms_output.put_line( 'March is not defined.' );
  end if;
end;
/

What you'll get? You may think the print line.

However, you'll get an error:

    ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 9

Well, associative array is working the same way as table (is that why it's defined as TABLE of ...), and month_table is similar to select value from month_table into v..., so need to have an exception handling.

    set serveroutput on
 declare
   type MONTH_TYPE is table of varchar(20) index by binary_integer;

   month_table   MONTH_TYPE;
 begin
   month_table(1) := 'Jan';
   month_table(2) := 'Feb';

   if month_table(3) is null then
     dbms_output.put_line( 'March is not defined.' );
   end if;
 exception
   when NO_DATA_FOUND then
     dbms_output.put_line( 'March is not found.' );
 end;
 /

Then, you'll get: March is not found.

Track Long Operations in Oracle

You may need to run a process on 1 million rows and the whole process takes a few hours to finish. In Oracle, there is a way that you can track the process.

Here's a piece of example code in PL/SQL, it takes advantage of package DBMS_APPLICATION_INFO.

 declare
   -- main variables
   ...

   -- long op info
   v_rindex     PLS_INTEGER;
   v_slno       PLS_INTEGER;
   v_totalwork  NUMBER;
   v_sofar      NUMBER;
   v_obj        PLS_INTEGER;

   v_op_name    varchar(100) := 'My work';
   v_units      varchar(100) := 'rows processed';
 begin
   -- Calculate total work (number of rows to be processed, etc.)
   select count(*) into v_totalwork from ...;

   v_sofar := 0;
   v_rindex     := DBMS_APPLICATION_INFO.set_session_longops_nohint;

   for ... -- A loop to process your work
   loop
     ...  -- do your work here.

     -- log longops view.
     v_sofar := v_sofar + 1;
     if mod( v_sofar, 500 ) = 0 then   -- log your operation every 500 rounds
        DBMS_APPLICATION_INFO.set_session_longops(rindex   => v_rindex,
                                               slno        => v_slno,
                                               op_name     => v_op_name,
                                               target      => v_obj,
                                               context     => 0,
                                               sofar       => v_sofar,
                                               totalwork   => v_totalwork,
                                               target_desc => 'Some description here',
                                               units       => v_units);
     end if;

   end loop;

   -- mark the end
   DBMS_APPLICATION_INFO.set_session_longops(rindex   => v_rindex,
                                          slno        => v_slno,
                                          op_name     => v_op_name,
                                          target      => v_obj,
                                          context     => 0,
                                          sofar       => v_sofar,
                                          totalwork   => v_totalwork,
                                          target_desc => 'Some description here',
                                          units       => v_units);

 end;
 /

To view the status of the process, run query:

 select sid, serial#, opname, sofar, totalwork, start_time, last_update_time
 from v$session_longops where opname = 'My Work';

Columns sofar and totalwork show you how much work has been done so far.