you are MISSING THE ENTIRE POINT. You you missing the point -- I just totally give up. (it is ALL about what happens with more then one user, what happens with one user here - as i keep trying to say - it abosolutely meaningless.)
Do what you want, go ahead, -- but you know what -- stay away -- far far far away from my dataases PLEASE. Your databases are the ones that have latch free waits like crazy, ora-4031's, people sitting there waiting to flush the shared pool, enormous performance spikes. Go for it -- if that is what you want. Just go for it. Remember, its all about that 0.01 second you are trying to save in the java client, seems worth it doesn't it?
(don't you see something wrong with your testing -- look at that last one -- perhaps you need to look inside your database --
GNORE THESE TIMES
STMT (172) times=60
PSTMT (188) times=60 <<<<==========
IGNORE THESE TIMES
PAY ATTENTION TO THESE TIMES
STMT (140) times=60 ---------note this
PSTMT (641) times=60 <<<<=========
PAY ATTENTION TO THESE TIMES
or your JVM. turn off garbage collection for example -- did you stop to ask "hmm, why 188 and then 641??? wonder, just wonder what is going on there? )
And you are so wrong, so so wrong with your statement that a single user test is even valid. You are so missing the point -- so missing the point.
funny thing is -- how do you explain these:
PAY ATTENTION TO THESE TIMES
STMT (15) times=2
PSTMT (16) times=2
PAY ATTENTION TO THESE TIMES
PAY ATTENTION TO THESE TIMES
STMT (0) times=3
PSTMT (0) times=3
PAY ATTENTION TO THESE TIMES
Hmm, apparently -- just apparently -- on your machine, you should run 3 statements in all programs cause that is faster then 2???
Do you think there might -- just might -- be some bursty performance issues here on your machine? That you might -- just might -- have to run this test like 50 times and average the numbers out?
Ok, let me do that for you. here is a modified yet again perftest.java:
import java.sql.*;
import oracle.jdbc.OracleDriver;
import java.util.Date;
public class perftest
{
static PreparedStatement saveTimesPs;
static void saveTimes( Connection con,
String which,
long elap ) throws Exception
{
System.out.println( which + " (" + elap + ")" );
if ( saveTimesPs == null )
saveTimesPs = con.prepareStatement
("insert into timings " +
"( which, elap ) values "+
"( ?, ? )" );
saveTimesPs.setString(1,which);
saveTimesPs.setLong(2,elap);
saveTimesPs.executeUpdate();
}
static void doStatement (Connection con,
int count,
int offset,
String which ) throws Exception
{
long start = new Date().getTime();
Statement st = con.createStatement();
for (int i = offset; i < count+offset; i++)
{
st.executeUpdate
("insert into testxxxperf " +
"(id, code, descr, insert_user, insert_date)" +
" values (" + i +
", 'ST - code" + i + "'" +
", 'St - descr" + i + "'" +
", user, sysdate ) ");
}
st.close();
con.commit();
long end = new Date().getTime();
System.out.println( "STMT" + " (" + (end-start) + ") times="+count );
saveTimes( con, which, (end-start) );
}
static void doPreparedStatement (Connection con,
int count,
int offset,
String which ) throws Exception
{
long start = new Date().getTime();
PreparedStatement ps = con.prepareStatement("insert into testxxxperf " +
"(id, code, descr, insert_user, insert_date)"
+ " values (?,?,?, user, sysdate)");
for (int i = offset; i < count+offset; i++)
{
ps.setInt(1,i);
ps.setString(2,"PS - code" + i);
ps.setString(3,"PS - desc" + i);
ps.executeUpdate();
}
con.commit();
long end = new Date().getTime();
System.out.println( "PSTMT" + " (" + (end-start) + ") times="+count );
saveTimes( con, which, (end-start) );
}
public static void main (String arr[]) throws Exception
{
Connection con = null;
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
con = DriverManager.getConnection
//("jdbc:oracle:oci8:@ora920.us.oracle.com",
("jdbc:oracle:thin:@aria-dev:1521:ora920",
"scott", "tiger");
con.setAutoCommit(false);
Integer iters = new Integer(arr[0]);
System.out.println( "IGNORE THESE TIMES" );
doStatement (con, 1, 0, "I-STMT" );
doPreparedStatement(con, 1, 0, "I-PSTMT" );
System.out.println( "IGNORE THESE TIMES" );
System.out.println( "PAY ATTENTION TO THESE TIMES" );
doStatement (con, iters.intValue(), 1000, "STMT" );
doPreparedStatement(con, iters.intValue(), 1000, "PSTMT" );
System.out.println( "PAY ATTENTION TO THESE TIMES" );
con.commit();
con.close();
}
}
and a perftest script:
#!/bin/csh -f
set ntimes = $1
set iters = $2
sqlplus scott/tiger<<EOF
drop table timings;
create table timings ( which varchar2(10), elap number );
insert into timings values ( 'x', 0 );
commit;
delete from timings;
exit
EOF
while( $ntimes > 0 )
sqlplus scott/tiger <<EOF
alter system flush shared_pool;
drop table testxxxperf;
create table testxxxperf
( id number, code varchar2(25), descr varchar2(25),
insert_user varchar2(30), insert_date date );
insert into testxxxperf values(0,'x','x','x',sysdate);
commit;
delete from testxxxperf;
exit
EOF
java perftest $iters
@ ntimes = $ntimes - 1
end
sqlplus scott/tiger<<EOF
select which, count(*), avg(elap), min(elap), max(elap)
from timings
group by which;
exit
EOF
$ perftest 10 1 <<<<=== run 10, single insert tests one after the other
WHICH COUNT(*) AVG(ELAP) MIN(ELAP) MAX(ELAP)
---------- ---------- ---------- ---------- ----------
I-PSTMT 10 13.2 9 17
I-STMT 10 15.6 11 20
PSTMT 10 8.3 6 15
STMT 10 13.6 13 15
and the thing about testing is consistency -- I want to make sure that if I run this again and again -- the results are infinitely reproducible -- and they are:
WHICH COUNT(*) AVG(ELAP) MIN(ELAP) MAX(ELAP)
---------- ---------- ---------- ---------- ----------
I-PSTMT 10 11.1 8 16
I-STMT 10 16.9 12 22
PSTMT 10 7.4 6 15
STMT 10 14.3 13 22
WHICH COUNT(*) AVG(ELAP) MIN(ELAP) MAX(ELAP)
---------- ---------- ---------- ---------- ----------
I-PSTMT 10 15.2 9 25
I-STMT 10 17.3 11 28
PSTMT 10 6.7 6 7
STMT 10 16.2 13 23
WHICH COUNT(*) AVG(ELAP) MIN(ELAP) MAX(ELAP)
---------- ---------- ---------- ---------- ----------
I-PSTMT 10 21.4 9 93
I-STMT 10 17.6 12 26
PSTMT 10 8.8 6 16
STMT 10 16 13 29
it is very very consistent -- how about yours? that was 9ir2 vs 9ir2, here is 817 vs 817:
WHICH COUNT(*) AVG(ELAP) MIN(ELAP) MAX(ELAP)
---------- ---------- ---------- ---------- ----------
I-PSTMT 10 13.1 12 16
I-STMT 10 17.1 13 27
PSTMT 10 12.4 6 16
STMT 10 13.1 13 14
WHICH COUNT(*) AVG(ELAP) MIN(ELAP) MAX(ELAP)
---------- ---------- ---------- ---------- ----------
I-PSTMT 10 16.6 10 26
I-STMT 10 17.7 12 31
PSTMT 10 10.7 6 22
STMT 10 13.3 13 15
WHICH COUNT(*) AVG(ELAP) MIN(ELAP) MAX(ELAP)
---------- ---------- ---------- ---------- ----------
I-PSTMT 10 17.1 12 30
I-STMT 10 18.2 13 41
PSTMT 10 14.9 10 24
STMT 10 13.1 12 14
WHICH COUNT(*) AVG(ELAP) MIN(ELAP) MAX(ELAP)
---------- ---------- ---------- ---------- ----------
I-PSTMT 10 13 10 25
I-STMT 10 15.1 12 18
PSTMT 10 8.5 6 14
STMT 10 15.1 12 25
I seem to be noticing some rather wide variations.... but the numbers are rather close (benefitting pstmts mostly tho).
but the bottom line is again NOT ABOUT a single user.
I guess if you want to build single user systems -- go for it. If you want to build mulit-user systems that actually scale and work -- you better forget about the existence of Statements in jdbc....
Run your test 100 times and average it out over the long haul. See what happens there. If you see something quirky (like the two examples above) explore it -- explain it.
Just for grins, I did 100 iterations of a single statement against 817:
WHICH COUNT(*) AVG(ELAP) MIN(ELAP) MAX(ELAP)
---------- ---------- ---------- ---------- ----------
I-PSTMT 100 16.23 9 45
I-STMT 100 19.69 11 206
PSTMT 100 12.41 6 23
STMT 100 13.82 12 50
and remember my advice from
</code>
http://asktom.oracle.com/~tkyte/runstats.html <code>
...
o How many resources each approach takes. This can be more meaningful then even the wall clock timings.
For example, if one approach is faster then the other but it takes thousands of latches (locks), I might avoid it simply because it will not scale as well.....
single user tests -- elapsed times are virtually meaningless.