Driving a road

At my last post I talked about different ways to write SQL. Here is an example of such situation. There are three different queries written to get the similar results out of two tables. Here I am driving a road. So the information here is kind of spatial stored in relational way.

create table roadpoint(roadpoint_id number constraint roadpoint_pk primary key, roadnumber number not null, distance number not null);

create table region(region_id number constraint region_pk primary key, startpoint references roadpoint not null, endpoint references roadpoint not null);

insert into roadpoint select level,1,level*10 from dual connect by level<=20000;  


insert into region values (1,1,2);  
insert into region values (2,4,6);  
insert into region values (3,7,10);  
insert into region values (4,40,50);  
insert into region values (5,4006,4010);  

exec dbms_stats.gather_table_Stats(user,'REGION');  
exec dbms_stats.gather_table_Stats(user,'ROADPOINT');    

select * 
  from region reg, roadpoint st, roadpoint en 
 where reg.startpoint = st.roadpoint_id
   and reg.endpoint = en.roadpoint_id ;  

The problem here is that roadpoints inside region are not selected.

The needed rows may be impressed with a query like following.

select reg.region_id, rp.roadpoint_id,rp.roadnumber,rp.distance
from region reg, roadpoint rp
where exists (select null from roadpoint st, roadpoint en where reg.startpoint = st.roadpoint_id
 and reg.endpoint = en.roadpoint_id
 and rp.distance between st.distance and en.distance
);

SQL_ID  2q2uj40br0df2, child number 0
-------------------------------------
select reg.region_id, rp.roadpoint_id,rp.roadnumber,rp.distance from
region reg, roadpoint rp where exists (select null from roadpoint st,
roadpoint en where reg.startpoint = st.roadpoint_id   and reg.endpoint
= en.roadpoint_id   and rp.distance between st.distance and en.distance
)

Plan hash value: 2380388577

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |      1 |        |     25 |00:00:00.55 |     208K|       |       |          |
|*  1 |  FILTER                       |              |      1 |        |     25 |00:00:00.55 |     208K|       |       |          |
|   2 |   MERGE JOIN CARTESIAN        |              |      1 |    100K|    100K|00:00:00.01 |      60 |       |       |          |
|   3 |    TABLE ACCESS FULL          | REGION       |      1 |      5 |      5 |00:00:00.01 |       7 |       |       |          |
|   4 |    BUFFER SORT                |              |      5 |  20000 |    100K|00:00:00.01 |      53 |   690K|   486K|  613K (0)|
|   5 |     TABLE ACCESS FULL         | ROADPOINT    |      1 |  20000 |  20000 |00:00:00.01 |      53 |       |       |          |
|   6 |   NESTED LOOPS                |              |    100K|      1 |     25 |00:00:00.35 |     208K|       |       |          |
|*  7 |    TABLE ACCESS BY INDEX ROWID| ROADPOINT    |    100K|      1 |   4078 |00:00:00.31 |     200K|       |       |          |
|*  8 |     INDEX UNIQUE SCAN         | ROADPOINT_PK |    100K|      1 |    100K|00:00:00.10 |     100K|       |       |          |
|*  9 |    TABLE ACCESS BY INDEX ROWID| ROADPOINT    |   4078 |      1 |     25 |00:00:00.01 |    8167 |       |       |          |
|* 10 |     INDEX UNIQUE SCAN         | ROADPOINT_PK |   4078 |      1 |   4078 |00:00:00.01 |    4089 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter( IS NOT NULL)
  7 - filter("EN"."DISTANCE">=:B1)
  8 - access("EN"."ROADPOINT_ID"=:B1)
  9 - filter("ST"."DISTANCE"<=:B1)
  10 - access("ST"."ROADPOINT_ID"=:B1)    

Taking a look the generated plan one might consider something else. Here are two alternative cursors c_join and c_drivetheroad. Those are measured with Tom Kytes runstats

DECLARE
  CURSOR c_join
  IS
select reg.region_id, rp.roadpoint_id,rp.roadnumber,rp.distance
from region reg, roadpoint st, roadpoint en, roadpoint rp
where reg.startpoint = st.roadpoint_id
 and reg.endpoint = en.roadpoint_id
 and rp.distance between st.distance and en.distance
;
--
  CURSOR c_drivetheroad
  IS
select lv region_id,roadpoint_id,roadnumber,distance
from (
select region_id
,last_value(region_id ignore nulls)over(partition by roadnumber order by rp.distance) lv
,last_value(region_id ignore nulls)over(partition by roadnumber order by rp.distance desc) fv
,staend
,point
,rp.roadnumber
,roadpoint_id
,distance
from (
select *
from (
select reg.region_id, st.distance startdistance, en.distance enddistance
from region reg, roadpoint st, roadpoint en
where reg.startpoint = st.roadpoint_id
 and reg.endpoint = en.roadpoint_id
)
unpivot (point for staend in (startdistance as '1', enddistance as '-1'))
) re, roadpoint rp
where re.point (+)= rp.distance
) qw
where lv=fv
;
--
BEGIN
   runstats_pkg.rs_start;
  FOR i IN 1 .. 100 LOOP
     FOR rec IN c_join LOOP
        NULL;
     END LOOP;
  END LOOP;
   runstats_pkg.rs_middle;
  FOR i IN 1 .. 100 LOOP
     FOR rec IN c_drivetheroad LOOP
        NULL;
     END LOOP;
  END LOOP;
   runstats_pkg.rs_stop;
END;
/

Before looking at runstats results lets see the plans used. Does it seem like the first join method look like a bit easier than the second alternative. A-rows in the first one are 25 at most as those are 20000 in the second alternative.

SQL_ID  fu5n5fqtksa7b, child number 0
-------------------------------------
select reg.region_id, rp.roadpoint_id,rp.roadnumber,rp.distance from
region reg, roadpoint st, roadpoint en, roadpoint rp where
reg.startpoint = st.roadpoint_id   and reg.endpoint = en.roadpoint_id  
and rp.distance between st.distance and en.distance

Plan hash value: 2085902882

---------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |      1 |        |     25 |00:00:00.02 |     296 |
|   1 |  NESTED LOOPS                  |              |      1 |  25003 |     25 |00:00:00.02 |     296 |
|   2 |   NESTED LOOPS                 |              |      1 |      5 |      5 |00:00:00.01 |      31 |
|   3 |    NESTED LOOPS                |              |      1 |      5 |      5 |00:00:00.01 |      19 |
|   4 |     TABLE ACCESS FULL          | REGION       |      1 |      5 |      5 |00:00:00.01 |       7 |
|   5 |     TABLE ACCESS BY INDEX ROWID| ROADPOINT    |      5 |      1 |      5 |00:00:00.01 |      12 |
|*  6 |      INDEX UNIQUE SCAN         | ROADPOINT_PK |      5 |      1 |      5 |00:00:00.01 |       7 |
|   7 |    TABLE ACCESS BY INDEX ROWID | ROADPOINT    |      5 |      1 |      5 |00:00:00.01 |      12 |
|*  8 |     INDEX UNIQUE SCAN          | ROADPOINT_PK |      5 |      1 |      5 |00:00:00.01 |       7 |
|*  9 |   TABLE ACCESS FULL            | ROADPOINT    |      5 |   5001 |     25 |00:00:00.02 |     265 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  6 - access("REG"."STARTPOINT"="ST"."ROADPOINT_ID")
  8 - access("REG"."ENDPOINT"="EN"."ROADPOINT_ID")
  9 - filter(("RP"."DISTANCE">="ST"."DISTANCE" AND "RP"."DISTANCE"<="EN"."DISTANCE"))    



SQL_ID  8as0wh80pucty, child number 0
-------------------------------------
select lv region_id,roadpoint_id,roadnumber,distance  from (  select 
region_id  ,last_value(region_id ignore nulls)over(partition by 
roadnumber order by rp.distance) lv  ,last_value(region_id ignore 
nulls)over(partition by roadnumber order by rp.distance desc) fv  
,staend  ,point  ,rp.roadnumber  ,roadpoint_id  ,distance  from (  
select *  from (  select reg.region_id, st.distance startdistance, 
en.distance enddistance  from region reg, roadpoint st, roadpoint en  
where reg.startpoint = st.roadpoint_id    and reg.endpoint = 
en.roadpoint_id  )  unpivot (point for staend in (startdistance as '1', 
enddistance as '-1'))  ) re, roadpoint rp  where re.point (+)= 
rp.distance  ) qw  where lv=fv
 
Plan hash value: 1653289237
 
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |     25 |00:00:00.08 |      84 |       |       |          |
|*  1 |  VIEW                                |              |      1 |  20000 |     25 |00:00:00.08 |      84 |       |       |          |
|   2 |   WINDOW SORT                        |              |      1 |  20000 |  20000 |00:00:00.08 |      84 |   903K|   523K|  802K (0)|
|   3 |    WINDOW SORT                       |              |      1 |  20000 |  20000 |00:00:00.06 |      84 |   832K|   511K|  739K (0)|
|*  4 |     HASH JOIN RIGHT OUTER            |              |      1 |  20000 |  20000 |00:00:00.02 |      84 |   968K|   968K|  797K (0)|
|*  5 |      VIEW                            |              |      1 |     10 |     10 |00:00:00.01 |      31 |       |       |          |
|   6 |       UNPIVOT                        |              |      1 |        |     10 |00:00:00.01 |      31 |       |       |          |
|   7 |        NESTED LOOPS                  |              |      1 |        |      5 |00:00:00.01 |      31 |       |       |          |
|   8 |         NESTED LOOPS                 |              |      1 |      5 |      5 |00:00:00.01 |      26 |       |       |          |
|   9 |          NESTED LOOPS                |              |      1 |      5 |      5 |00:00:00.01 |      19 |       |       |          |
|  10 |           TABLE ACCESS FULL          | REGION       |      1 |      5 |      5 |00:00:00.01 |       7 |       |       |          |
|  11 |           TABLE ACCESS BY INDEX ROWID| ROADPOINT    |      5 |      1 |      5 |00:00:00.01 |      12 |       |       |          |
|* 12 |            INDEX UNIQUE SCAN         | ROADPOINT_PK |      5 |      1 |      5 |00:00:00.01 |       7 |       |       |          |
|* 13 |          INDEX UNIQUE SCAN           | ROADPOINT_PK |      5 |      1 |      5 |00:00:00.01 |       7 |       |       |          |
|  14 |         TABLE ACCESS BY INDEX ROWID  | ROADPOINT    |      5 |      1 |      5 |00:00:00.01 |       5 |       |       |          |
|  15 |      TABLE ACCESS FULL               | ROADPOINT    |      1 |  20000 |  20000 |00:00:00.02 |      53 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("LV"="FV")
   4 - access("unpivot_view_014"."POINT"="RP"."DISTANCE")
   5 - filter("unpivot_view_014"."POINT" IS NOT NULL)
  12 - access("REG"."STARTPOINT"="ST"."ROADPOINT_ID")
  13 - access("REG"."ENDPOINT"="EN"."ROADPOINT_ID")

And now some runstats numbers

Run1 ran in 126 hsecs
Run2 ran in 800 hsecs
run 1 ran in 15,75% of the time

Name                                  Run1        Run2        Diff
STAT...table scan blocks gotte      25,500       5,500     -20,000
STAT...no work - consistent re      25,955       5,714     -20,241
STAT...consistent gets from ca      27,661       6,221     -21,440
STAT...consistent gets              29,930       8,425     -21,505
STAT...consistent gets from ca      29,930       8,425     -21,505
STAT...session logical reads        29,990       8,459     -21,531
LATCH.cache buffers chains          57,767      15,172     -42,595
STAT...session uga memory max      123,452     410,072     286,620
STAT...session pga memory max      131,072     524,288     393,216
STAT...sorts (rows)                     33   4,000,000   3,999,967
STAT...table scan rows gotten   10,000,500   2,000,500  -8,000,000

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
60,427      20,974     -39,453    288.10%

We actually read less rows and sort them and use memory a bit more in the c_drivetheroad version. The cursor c_join seems to be faster in this case. But the note that the number of a-rows vs rows gotten are not in sync. Also logical reads are one magnitude more in c_join run.

Visual tuning

It was a nice opportunity to attend beta testing a tool that does something that I do with pen and paper quite often when trying to figure out query relations.

Could not attend a webinar last Thursday. Just waiting for the opportunity to see the replay.

Pipelining

In reducing number of function calls I wrote about how to rewrite a query that is calling a function. Another approach to the issue is to alter the function. The function includes only a SQL clause. The whole result of the clause is bulk collected first and then returned. The usage of the function is in IN clause. IN predicate is satisfied if there is one equality coming out of the select. So in the best cases it is not needed to populate the whole bulk collect inside the function. Using pipelining as an alternative here.

Original bulk collect version.

SQL> create or replace type ns_typ is table of number;
  2  /

Type created.

SQL> create or replace function rn(n number) return ns_typ is
  2    ret ns_typ;
  3    begin
  4     dbms_lock.sleep(1);
  5     select level bulk collect into ret from dual connect by level <= n;
  6    return ret;
  7  end;
  8  /

Function created.

And the pipelined version of the function.

SQL> create or replace function rnpiped(n number) return ns_typ pipelined is
  2  begin
  3    for ret in
  4     (select level l from dual connect by level <= n)
  5    loop
  6     dbms_lock.sleep(1/n);
  7     pipe row (ret.l);
  8    end loop;
  9  end;
 10  /

If the whole result returned from the function is needed there is no great difference in the execution times. Actually it seems to be increasing a bit.

SQL> select * from table(rn(10));

COLUMN_VALUE
------------
           1
           2
           3
           4
           5
           6
           7
           8
           9
          10

10 rows selected.

Elapsed: 00:00:01.00
SQL>
SQL> select * from table(rnpiped(10));

COLUMN_VALUE
------------
           1
           2
           3
           4
           5
           6
           7
           8
           9
          10

10 rows selected.

Elapsed: 00:00:01.07

But when used in IN predicate the results with this data are even better than in the previous post.

SQL> select * from ta a where a.n in (select * from table(rn(a.m)));

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:10.00
SQL>
SQL> select * from ta a where a.n in (select * from table(rnpiped(a.m)));

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:01.95

And putting both together.

SQL> with aa as (
  2    select *
  3      from ta a
  4    ), bb as (
  5    select distinct m
  6      from aa
  7    ), cc as (
  8    select /*+materialize*/ b.m, dd.column_value n
  9      from bb b, table(rn(b.m)) dd)
 10    select *
 11      from aa
 12     where (n,m) in (select n,m from cc)
 13  ;

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:03.09
SQL>
SQL> with aa as (
  2    select *
  3      from ta a
  4    ), bb as (
  5    select distinct m
  6      from aa
  7    ), cc as (
  8    select /*+materialize*/ b.m, dd.column_value n
  9      from bb b, table(rnpiped(b.m)) dd)
 10    select *
 11      from aa
 12     where (n,m) in (select n,m from cc)
 13  ;

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:00.68

Reducing the number of function calls

Using a slow function call in your query? Maybe you are calling it unnecessarily.

SQL> create or replace type ns_typ is table of number;
  2  /

Type created.

SQL> create or replace function rn(n number) return ns_typ is
  2  ret ns_typ;
  3  begin
  4   dbms_lock.sleep(1);
  5   select level bulk collect into ret from dual connect by level <= n;
  6  return ret;
  7  end;
  8  /

Function created.

SQL>
SQL> create table ta as select level n, mod(level,3)+1 m from dual connect by level <= 10;

Table created.

SQL> select * from ta;

         N          M
---------- ----------
         1          2
         2          3
         3          1
         4          2
         5          3
         6          1
         7          2
         8          3
         9          1
        10          2

10 rows selected.

SQL> set timi on

SQL> select * from ta a where a.n in (select * from table(rn(a.m)));

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:10.01

The query is calling rn function for each ten rows of ta table. Each call takes one second as the function is using dbms_lock. There are only three distinct values that the function is needed to be called.

SQL> with aa as (
  2  select *
  3    from ta a
  4  ), bb as (
  5  select distinct m
  6    from aa
  7  ), cc as (
  8  select /*+materialize*/ b.m, dd.column_value n
  9    from bb b, table(rn(b.m)) dd)
 10  select *
 11    from aa
 12   where (n,m) in (select n,m from cc)
 13  ;

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:03.03

Alternatively you might consider using result cache for the function.

SQL> create or replace function rn(n number) return ns_typ result_cache is
  2  ret ns_typ;
  3  begin
  4   dbms_lock.sleep(1);
  5   select level bulk collect into ret from dual connect by level <= n;
  6  return ret;
  7  end;
  8  /

Function created.

Elapsed: 00:00:00.04
SQL>
SQL> select * from ta a where a.n in (select * from table(rn(a.m)));

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:03.01
SQL>
SQL> select * from ta a where a.n in (select * from table(rn(a.m)));

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:00.00

Cleanup

SQL> drop table ta purge;
SQL> drop function rn;
SQL> drop type ns_typ;

A dark side of Easter

It was an Easter a while ago. I had a bottle of Finnish porter beer. It was made by Laitilan breweries. My mother in law got some of it and made it clear that it tasted like mämmi. She put some vanilla sauce in the class and the taste was, well similar like mämmi with vanilla sauce. This year Laitilan breweries has a bottle that has a yellow lable saying Mämmi on it. They have actually made beer out of mämmi. Well if you do not know what mämmi is take alook of something else from Finnish kitchen called Kalakukko. They make that mainly in Kuopio. I had an exiting evening in Hakametsä. Ice hockey arena in Tampere. My favourite ice hockey team Tappara just won Kalpa - a team from Kuopio.

Putting things together that do not actually have nothing to do with each other make sometimes wheels running. Ice hockey and mämmi have nothing in common like vanilla sauce and Kuopio. During last year I have had several occasions when the ideas from spatial advisor have came to the rescue. There have been several cases when I have been dealing with query performance problems with temporal data. Well spatial information is more close to temporal than Kalpa is to Laitila. These ideas have come to the rescue several times when the number count in tables have increased from a million to ten or hundred million. Changing a traditional b-tree index to a function based locator index and a query where predicate to use that has helped the response time to be usable. Joining similar table with greater than or between comparison has also been a troublesome to deal with. Using those function based locator indexes and sdo_join have helped a lot.

Lets have a simple example not from those real life experiences like I did not reveal earlier. Lets copy the base situation from sum over time writing. Notable here is that the locator indexing is available also in free XE version and also SE and EE. The basic idea for function based locator index may be expressed like

DROP TABLE T CASCADE CONSTRAINTS PURGE;

DROP FUNCTION TF;


CREATE TABLE T (FRO DATE, TIL DATE, N NUMBER);

INSERT INTO T VALUES (TO_DATE('09.04.2009','dd.mm.yyyy'), TO_DATE('10.04.2009','dd.mm.yyyy'), 1);

INSERT INTO T VALUES (TO_DATE('10.04.2009','dd.mm.yyyy'), TO_DATE('12.04.2009','dd.mm.yyyy'), 2);

INSERT INTO T VALUES (TO_DATE('11.04.2009','dd.mm.yyyy'), TO_DATE('14.04.2009','dd.mm.yyyy'), 3);

COMMIT;

Query performing not with satisfactory response time.

SELECT * 
  FROM T 
 WHERE TIL >= TO_DATE('11.04.2009','dd.mm.yyyy') 
   AND FRO <= TO_DATE('14.04.2009','dd.mm.yyyy')
;

10.04.2009 12.04.2009 2
11.04.2009 14.04.2009 3

CREATE OR REPLACE FUNCTION TF(FRO DATE, TIL DATE) RETURN SDO_GEOMETRY deterministic as 
BEGIN
RETURN MDSYS.SDO_GEOMETRY(2002,NULL, NULL, SDO_ELEM_INFO_ARRAY (1,2,1),
    SDO_ORDINATE_ARRAY(to_number(to_char(FRO,'j')),0,to_number(to_char(TIL,'j')),0));
END;
/

SELECT TO_CHAR(to_date('19000101','yyyymmdd'),'J'),TO_CHAR(to_date('22000101','yyyymmdd'),'J') FROM dual;

--2415021 2524594

DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'T';

INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME,COLUMN_NAME,DIMINFO)
  VALUES (
  'T',
  'RAFU.TF(FRO,TIL)',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('X', 2415021, 2524594, 0.5),
    SDO_DIM_ELEMENT('Y', 0, 0, 0.5)
     )
  )
;

COMMIT;

CREATE INDEX T_LOCATOR_IDX ON T(TF(FRO,TIL))
  INDEXTYPE IS mdsys.spatial_index;

SELECT * 
  FROM T 
 WHERE SDO_FILTER(TF(FRO,TIL),
                  TF(TO_DATE('11.04.2009','dd.mm.yyyy')
                    ,TO_DATE('14.04.2009','dd.mm.yyyy'))
                 ) = 'TRUE'
;

10.04.2009 12.04.2009 2
11.04.2009 14.04.2009 3

Just try with your own temporal data and PlanViz and compare the a-rows inside your query.

An another thing that I sayed in sum over time writing was "I like to see continous pair stored as open ended". It is just one thing to deal with this approach. SQL standard by the way says that the end in time interval should not be included in the interval. But if the end is the same than the start moment then the interval is presenting the one spot in the timeline. So in that case the end is not excluding.

Yet another thing about timelines and my earlier postings. Last year I wrote about some ideas about not overlapping daily. The idea presented in this post might near to the answer missing in that post. But it is not possible to create a domain index as a unique.

By the way Mämmi beer with vanilla sauce got called today white russian by my mother in law. Wonder what might Dude in Big Lebowski say about that.

Load using Java

Cary Millsap is writing good things about measuring when tuning performance. His paper Making friends "Optimizing the insert program" is talking how to insert 10000 rows using Java. There are presented only possibilities to insert using Statement in a loop or using PreparedStatement in a loop. Should there be a alternative way also presented? Avoid looping statements in Java and populate all rows just in single call to the database.

Timing results inserting 10000 rows:

Array
Executed in 0 min 0 s 313 ms.
Prepared
Executed in 0 min 2 s 985 ms.

ARRAY:

create table si (s number(19),s2 number(19));
create or replace type nums is object ( n1 number(19), n2 number(19));
create type sit is table of nums;

    private static void insertArray(Connection c, List elems) 
            throws SQLException {
        ArrayDescriptor ad = ArrayDescriptor.createDescriptor("SIT", c);
        ARRAY a = new ARRAY(ad, c, elems.toArray());
        OraclePreparedStatement ops 
           = (OraclePreparedStatement)c.prepareStatement(
                        "insert into si " +
                        " select *" +
                        "    from table(?)");
        ops.setARRAY(1, a);

        System.out.println(ops.executeUpdate());
        ops.close();
        c.commit();
    }

Prepared:

    private static void insertPrepared(Connection c, List elems) 
            throws SQLException {
        PreparedStatement ps = c.prepareStatement("insert into si values(?,?)");
        Iterator i = elems.iterator();
        while ( i.hasNext()) 
        {
            Object[] o = i.next();
            ps.setInt(1, (Integer)o[0]);
            ps.setInt(2, (Integer)o[1]);
            ps.executeUpdate();
        }
        ps.close();
        
        c.commit();
    }

Here are some timings for other number of rows. It shows that if you are inserting 100-1000 rows this approach might be worth considering. Measure yourself. Be sure to have enough memory available for your Java.

ms      localhost      remote db 
rows    prepared array prepared array
10      94       140   93       141
100     125      141   125      156
1000    313      203   844      265
10000   1500     344   16297    453
100000  12063    1422  206210   2078
1000000 182063   java.lang.OutOfMemoryError: Java heap space  

insertintoselect.java