Luke:
And I really appreciate it.
I'm only a little bit upset when people say that I'm unwiling to
work...
Let's start again with willings.
Profile doesn't have sense for me. It's obvious.
We are waiting for Oracle answer:
java -Xrunhprof:cpu=samples -classpath .
jdbc14.jar DA
rank self accum count trace method
1 74.08% 74.08% 889 102 java.net.SocketInputStream.socketRead0
2 5.42% 79.50% 65 133
java.net.SocketOutputStream.socketWrite0
3 1.33% 80.83% 16 145 oracle.jdbc.ttc7.TTCItem.unmarshal
4 1.17% 82.00% 14 4 java.lang.ClassLoader.defineClass0
5 0.92% 82.92% 11 19
java.lang.ClassLoader.findBootstrapClass
6 0.83% 83.75% 10 143
oracle.jdbc.ttc7.MAREngine.unmarshalCLRforREFS
7 0.58% 84.33% 7 159
oracle.jdbc.ttc7.TTCAdapter.createNonPlsqlTTCColumnArray
8 0.58% 84.92% 7 150
oracle.jdbc.ttc7.TTC7Protocol.createDBItem
9 0.50% 85.42% 6 158 java.lang.StringBuffer.toString
I do not believe in profiling. It takes time.
And during this time Oracle is working too!
So, I prefer more simple way - just count milliseconds.
For whole program, for database, (for sql), for cycle.
See code.
And here is exact code (except sql):
//========= DA.java =============
import java.io.*;
import java.sql.*;
import java.util.*;
public class DA {
public Connection con;
public Statement stmt;
static {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (Exception ex) {
System.out.println("static driver not found: "
+ ex.getMessage());
}
}
public void openCon(String url,
String user, String password) {
try {
con = DriverManager.getConnection(url, user, password);
stmt = con.createStatement();
} catch (Exception ex) {
System.out.println(ex);
}
}
public void closeCon() {
try {
stmt.close();
con.close();
} catch (Exception ex) {
System.out.println(ex);
}
}
public static void main(String[] args) {
new DA().getData();
System.out.println("Java="+System.getProperty("java.version"));
}
public PreparedStatement getPreparedStatement(String sql)
throws java.sql.SQLException {
return con.prepareStatement(sql);
}
//////////// method itself ///////////////
public Vector table; // vector of String[4]; 2000 elements
// collecting_code|userid|start|end
public Hashtable totaled_hrs=new Hashtable();
public void getData()
{
try{
double mth_hours=23*7.75; // for December 2004
String oldCode=null;
table=new Vector();
String tmp[]=null;
BufferedReader br=new BufferedReader(new FileReader("load"));
String line;
while ((line=br.readLine())!=null){
StringTokenizer st=new StringTokenizer(line,"|");
tmp=new String[4];
tmp[0]=st.nextToken();
tmp[1]=st.nextToken();
tmp[2]=st.nextToken();
tmp[3]=st.nextToken();
table.add(tmp);
}
br.close();
System.out.println(table.size());
long tall,tdb,tsql,tcycle,v2,v3;
tall=tdb=tsql=tcycle=0;
DA da=new DA();
ResultSet rs;
Properties pr=new Properties();
pr.load(new FileInputStream("common.properties"));
//openCon("jdbc
racle:thin
yahoo.com:1521:users",
// "scott","tiger");
da.openCon(pr.getProperty("url"),
pr.getProperty("user"),pr.getProperty("password"));
double value=0,total=0;
String type="";
String sql=
"SELECT SUM(DECODE(TO_CHAR(e.date, 'MM'), \n"+
" '12', e.hours,0)) hours, \n"+
" d.person_type \n" +
" FROM projects a, tasks b, assignments c, \n"+
" resources d, hours e, \n"+
" super_projects h, users g \n"+
" WHERE \n"+
" d.id = ? \n"+
" AND (e.date >= TO_DATE(?, 'mm/dd/yyyy')) \n"+
" AND (e.date <= TO_DATE(?, 'mm/dd/yyyy')) \n"+
" AND a.id = b.projectid \n"+
" AND b.id = c.taskid \n"+
" AND c.resourceid = d.id \n"+
" AND c.id = e.assignmentid \n"+
" AND e.approved = 'yes' \n"+
" AND a.superid = h.id \n"+
" AND h.name like 'Billed %' \n"+
" AND a.name not like 'Local %' \n"+
" GROUP BY d.person_type \n";
PreparedStatement ps = da.getPreparedStatement(sql);
tall=System.currentTimeMillis();
v2=System.currentTimeMillis();
for(int i=0; i<table.size(); i++)
{
tmp=(String[])(table.get(i));
v3=System.currentTimeMillis();
tcycle+=v3-v2;
ps.setString(1, tmp[1]);
ps.setString(2, tmp[2]);
ps.setString(3, tmp[3]);
rs = ps.executeQuery();
tsql+=System.currentTimeMillis()-v3;
if (!rs.next()) continue;
value=rs.getDouble(1);
type=rs.getString(2);
rs.close();
v2=System.currentTimeMillis();
tdb += v2-v3;
if (value>mth_hours&&"Billed".equals(type)) value=mth_hours;
total+=value;
MyDouble d=(MyDouble)totaled_hrs.get(tmp[0]);
if (d!=null) d.value+=value;
else {
d=new MyDouble();
d.value=value;
totaled_hrs.put(tmp[0],d);
}
if (!(tmp[0].equals(oldCode))&&oldCode!=null)
System.out.println(
oldCode+" "+
((MyDouble)totaled_hrs.get(oldCode)).value+
" "+total);
oldCode=tmp[0];
}// end cycle
da.closeCon();
tall=System.currentTimeMillis()-tall;
System.out.println("Hours: "+total+
" time all="+tall+
" time db="+tdb+
" (sql="+tsql+")"+
" time cycle="+tcycle);
} catch (Exception e){
e.printStackTrace();
}
}
class MyDouble{public double value=0;}
}
//////////// end DA.java /////////////
I hope it will post well. I'll double check it and
if it will be bad I'll put it somewhere in my web site.
And here is perl code:
################# perl ########################
#!/perl/5.6/bin/perl
use lib "/perl/5.6/DBD/oracle8.1.6/lib/site_perl/5.6.0/sun4-solaris";
use Oraperl;
... open connection ....
sub getData{
my(@persons)=@_;
$total=0;
%totaled_hrs=();
foreach $key(@persons){
@one_person=split(/:/,$key);
$sql=...;
$db_list=&ora_open($lda,$sql) || die "\nCan't open cursor
1: $ora_errstr\n";
($time, $type)=&ora_fetch($db_list);
if ($time>178.25) {
if ( "Billed" eq $type ) {
$time=178.25;
}}
$totaled_hrs{$one_person[0]}+=$time;
$total+=$time;
}
############# end of perl ################
Result of java is
Hours: 154930.95 time all=5436908 time db=5297897 (sql=5434009) time
cycle=243005
Java=1.4.2_01
Which means 90 minutes for whole run.
Where 88 minutes for Oracle waiting.
Total Java expenses 4.5 minutes.
(Please, don't be too strict to numbers.
I just cut&paste them. Milliseconds are a little big time period.
Be indulgent to this quick and not precision way.)
perl result is:
Hours: 154930.95 for 1487 seconds.
Which is 25 minutes today.
Which is 3.6 times faster.
So, I'm open to any suggestions how to improve my Java code
or how to force managers to use Java in this project.
Alex Kizub.
