Is perl better? :(((

[akizub]

I'm proud and certified Java programmer since 1998.
Trust me, a lot of experience! And I prefer Java.
Also I work with other languages too.
For example we support/develop big system with around 300 perl scripts.
I try to move them to Java, but...

Recently I tried port one perl script. It does nothing but creates
thousands SQLs
retrieve and store/summarize rezults. SQLs are complicated a little but
return only pair of values. And this pair is stored in hashtable.
That's all. Simple enough.

Why not to rewrite?
But in Java it runs at least twice slower!
In my understanding there is no Java/perl calculations. Store couple
values! Phoo!

All time, I assume, is wasted on Oracle 9 connection. In middleware.
The SQLs are the same, connection is the same, Oracle is the same,
machine is the same. Everything is the same. Except time. perl is
faster somehow.

Then I used prepared statements. It became worse. At least 2.5 times
slower...
What's the deal?
Any ideas why it happens?

I believe I use latest Java 1.4, JDBC (ojdbc14.jar) from Oracle as well
as latest perl DBD or Oraperl modules.
Who have better experience?

Alex Kizub.

 

[KiLVaiDeN]

I'm proud and certified Java programmer since 1998.
Trust me, a lot of experience! And I prefer Java.
Also I work with other languages too.
For example we support/develop big system with around 300 perl scripts.
I try to move them to Java, but...

Recently I tried port one perl script. It does nothing but creates
thousands SQLs
retrieve and store/summarize rezults. SQLs are complicated a little but
return only pair of values. And this pair is stored in hashtable.
That's all. Simple enough.

Why not to rewrite?
But in Java it runs at least twice slower!
In my understanding there is no Java/perl calculations. Store couple
values! Phoo!

All time, I assume, is wasted on Oracle 9 connection. In middleware.
The SQLs are the same, connection is the same, Oracle is the same,
machine is the same. Everything is the same. Except time. perl is
faster somehow.

Then I used prepared statements. It became worse. At least 2.5 times
slower...
What's the deal?
Any ideas why it happens?

I believe I use latest Java 1.4, JDBC (ojdbc14.jar) from Oracle as well
as latest perl DBD or Oraperl modules.
Who have better experience?

Alex Kizub.

Do you use Connection pool ? maybe the time involved is lost in connecting
all the time

K

 

[akizub]

One connection. Always alive. All these hours ((
Alex Kizub

 

[Robert Klemme]

One connection. Always alive. All these hours ((
Alex Kizub

If the SQL is exactly the same and you used the same machine for boths tests
the most likely cause is probably your Java code. Did you profile it?

robert

 

[akizub]

100%

 

[Juha Laiho]

(e-mail address removed) said:

Recently I tried port one perl script. It does nothing but creates
thousands SQLs retrieve and store/summarize rezults. SQLs are
complicated a little but return only pair of values. And this pair is
stored in hashtable. That's all. Simple enough.

Why not to rewrite?
But in Java it runs at least twice slower!

If I recall correctly, JDBC will at some point do conversion from
the database character set to Unicode - and perl will not, unless
your environment is set up to use/require use of UTF8 character
encoding. This could be at least part of what you're seeing.

Are your programs CPU-bound? That is, are they using (almost) one second
of CPU time for each wallclock second? If so, that'd sound even more
like the problem would be in character set conversions.

Btw, is it the JDBC thin driver you're using? You might wish to compare
results when using JDBC OCI driver; there could be a difference. The
perl DBD::Oracle driver uses the OCI driver layer anyway.

 

[Dimitri Maziuk]

(e-mail address removed) sez:
....

All time, I assume, is wasted on Oracle 9 connection. In middleware.
The SQLs are the same, connection is the same, Oracle is the same,
machine is the same. Everything is the same. Except time. perl is
faster somehow.

Then I used prepared statements. It became worse. At least 2.5 times
slower...
What's the deal?
Any ideas why it happens?

Oracle's JDBC driver is very fast -- I have a Java program that
uses prepared statements to bulk-load data from text file, it's
as fast as using oracle loader.

You can reasonably expect Java to be a little slower, but not
much. A 2.5 times slowdown with prepared statements is a sure
sign that your code is broken.

Dima

 

[Tom Dyess]

I'm proud and certified Java programmer since 1998.
Trust me, a lot of experience! And I prefer Java.
Also I work with other languages too.
For example we support/develop big system with around 300 perl scripts.
I try to move them to Java, but...

Recently I tried port one perl script. It does nothing but creates
thousands SQLs
retrieve and store/summarize rezults. SQLs are complicated a little but
return only pair of values. And this pair is stored in hashtable.
That's all. Simple enough.

Why not to rewrite?
But in Java it runs at least twice slower!
In my understanding there is no Java/perl calculations. Store couple
values! Phoo!

All time, I assume, is wasted on Oracle 9 connection. In middleware.
The SQLs are the same, connection is the same, Oracle is the same,
machine is the same. Everything is the same. Except time. perl is
faster somehow.

Then I used prepared statements. It became worse. At least 2.5 times
slower...
What's the deal?
Any ideas why it happens?

I believe I use latest Java 1.4, JDBC (ojdbc14.jar) from Oracle as well
as latest perl DBD or Oraperl modules.
Who have better experience?

Alex Kizub.

Yes, I would have to take a look at the code of the Java application. Was
the original PERL script hitting an Oracle database? Why are you calling
thousands of SQL statements that just return a pairs? Is there any way to
call several of these statements in a single call? PERL is great for text
manipulation, but I don't think this fact would cover the gammut of ~2.5x
difference in speed. Did you run an explain plan on your Oracle SQL? Is your
Oracle box the same DB that the perl scripts are calling? (Ie perl hitting
production, java hitting some crappy 90mhz oracle box someone threw
together). Are you using SQL hints when tuning your SQL? When you step
through a debug, are the SQL calls taking the most time? Are you using the
same Oracle client software on both test scenarios? Are you using a
persistant connection? (ie not dropping and reconnecting every script). Are
you creating a Java application for every script, or are you creating a
large single Java app that calls all scripts?

I don't want the answers to all these, just suggesting some things to try. I
know you said you were using the same Oracle, but if all you are doing is
sending a SQL statement to Oracle, Java is done until you get the result. It
doesn't sound the same. By same I mean you are connecting to the same
server, same instance with the same client on the same workstation, just two
different languages. Does your workstation have enough free memory to be
running Java?

On OraclePower.com (ya ya, shameless plug, but I *do* have a point) I run 17
queries on the home page, and it comes back instantly. Oracle hauls-ass with
Java.

 

[steve]

(e-mail address removed) sez:
...

Oracle's JDBC driver is very fast -- I have a Java program that
uses prepared statements to bulk-load data from text file, it's
as fast as using oracle loader.

You can reasonably expect Java to be a little slower, but not
much. A 2.5 times slowdown with prepared statements is a sure
sign that your code is broken.

Dima

oracles jdbc is not fast, it is a piece of crap.
try using
1. the demo version from datadirect ,it is good for 15 days, but will allow
you to bench mark
2. try using the thin driver from java.
3. post your code ( minus passwords & domain), ill take a look.

steve

 

[Chris Smith]

100%

Huh? Do you mean it's using 100% of the CPU? If so, then you
*definitely* need to aquire a profiler and figure out where that time is
being spent. It shouldn't be taking nearly so long to issue queries.
There are a number of mistakes you could be making, but without seeing
your code or your profiling results, there's little that we can do.

--
www.designacourse.com
The Easiest Way To Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation

 

[akizub]

Here is code. Exactly. Except the SQL.
Hashtable ht=new Hashtable();
PreparedStatement ps =
conection.getPreparedStatement("select 'key', sysdate+? from
dual");

for (int i=0; i<2000; i++)
ps.setInt(1, i);

rs = ps.executeQuery();
if (rs.next()) {
String key=rs.getString(1);
double value=0;
Double d=(Double)ht.get(key);
if (d!=null) value=d.doubleValue();
value+=rs.getDouble(2);
ht.put(key,new Double(value));
}
rs.close();
}

That's all. Exceptions are outside and handled once. No existing null
values.
Hashtable is 1000 or less keys. Not big deal. As well as possible
impovements of code like mutable Objects instead Double or similar.

And that's all! No Unicode expenses or huge network trafic.
Thin driver.
I'll try OCI driver. Always was sure that thin is good enough.
Keep guessing! Because it's the shock for me.

Alex Kizub.

 

[Chris Smith]

You can reasonably expect Java to be a little slower, but not
much.

I don't know about that; a little slower than C or FORTRAN in the
average case, sure. A little slower than Perl, though? Not unless you
tune the test specifically to exploit the strengths of Perl and
weaknesses of Java; and even then I wouldn't guarantee you could produce
these results easily.

In any case, something is clearly wrong here besides the language.

--
www.designacourse.com
The Easiest Way To Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation

 

[akizub]

Tom:
Thanks for sharing you experience. I really appreciate it.
Especially these are question which I would ask too.
As you already read I have big experience too.

Wasthe original PERL script hitting an Oracle database?

Exactly the same.

Why are you calling thousands of SQL statements that just return a

pairs?
I hate such design too. Let say in another query from different
database (and different time) we have these pairs.

Is there any way to call several of these statements in a single call?

No. pairs are different.

PERL is great for text manipulation, but I don't think this fact

would cover the gammut of ~2.5x
difference in speed.

That was my impression too. Just before yetaerday's night.

Did you run an explain plan on your Oracle SQL?

I know I can improve this query. But that's not the point. It will be
improved in both languages and it still will be no difference. SQLs are
exactly the same.
Stupid, impractical but the same.

Is your Oracle box the same DB that the perl scripts are calling? (Ie

perl hitting
production, java hitting some crappy 90mhz oracle box someone threw
together).

Oracle on one box, Java and perl on another. Both production.

Are you using SQL hints when tuning your SQL?

Yes and no. The same result. See previous answer about SQL.

When you step through a debug, are the SQL calls taking the most

time?
Don't use debug only start and end time. Code is simple. See my another
answer in the thread.

Are you using the same Oracle client software on both test scenarios? Yes.

Are you using a persistant connection? (ie not dropping and

reconnecting every script).
Yes. One connection and it's alive for hours. No bad connections or
another exceptions.
Results are the same in perl and Java. But perl runs 2.5 faster.
I run two windows on the same Solaris box. In one I run java. In
another in the same time (it's about one hour) I can run perl program
twice for the same result and have another 10-15 minutes for third run.

Are you creating a Java application for every script, or are you

creating a
large single Java app that calls all scripts?
All SQLs are in one thread and in one cycle. One by one. Just like
perl.
Of course creating of thousand of threads could be more useful. But
what about poor Oracle? To be honest, our DBAs already called me and
asked to stop my SQL and run in the middle of the night. It was first
call from them in 5 years.

I don't want the answers to all these, just suggesting some things to

try.
I tried these and a lot of others.
BECAUSE I DON"t WANT TO USE PERL!
I like it too, but support of hundreds of scripts is a nightmare!
And what arguments do I have to switch to Java?
20 minutes vs. hour!

I know you said you were using the same Oracle, but if all you are

doing is
sending a SQL statement to Oracle, Java is done until you get the
result. It
doesn't sound the same. By same I mean you are connecting to the same
server, same instance with the same client on the same workstation,
just two different languages.

Don't use threads. As well as perl. See previous answer.

Does your workstation have enough free memory to be running Java?

This is Solaris with 8Gb. Sure enough to store 1000 keys Hashtable.

On OraclePower.com (ya ya, shameless plug, but I *do* have a point) I

run 17 queries on the home page, and it comes back instantly. Oracle
hauls-ass with Java.

Sorry, don't understand. A lot of resources. Don't know how they can
help me. Mostly I know all this and always was sure that Oracale loves
Java as well as me. But it doesn't help me to prove my point for
managers.
Actually what point? Fact is fact. I can't deny it even I don't like
it.

Maybe it's OCI. But I can't install it on produstion server. I should
use what already exists. So, perl has OCI, JDBC doesn't and perl
forever?
Very sad Certified and with huge experience Java programmer
Alex Kizub.

 

[akizub]

oracles jdbc is not fast, it is a piece of crap.
Looks like this for me now ((

1. the demo version from datadirect ,it is good for 15 days, but will

allow
you to bench mark

See this is production and, actually, third party application.
Do you think they will install something new for Java when perl is
already faster?

2. try using the thin driver from java.

Don't understand this. I tried few different JDBC thin drivers
including latest Oracle ojdbc14.jar for our particlular Oracle9i
9.2.0.4 JDBC Drivers.

3. post your code ( minus passwords & domain), ill take a look.

See another anwers in this thraed. Pretty simple.
Here is code. Exactly. Except the SQL.
Hashtable ht=new Hashtable();
PreparedStatement ps =
conection.getPreparedStatement("select 'key', sysdate+? from
dual");

for (int i=0; i<2000; i++)
ps.setInt(1, i);

rs = ps.executeQuery();
if (rs.next()) {
String key=rs.getString(1);
double value=0;
Double d=(Double)ht.get(key);
if (d!=null) value=d.doubleValue();
value+=rs.getDouble(2);
ht.put(key,new Double(value));

}
rs.close();
}

Still have a little hope. But less and less...
Alex Kizub.

 

[akizub]

Sorry.
100% that all is the same.
100% that Java is not wasting the time.
And I'm sure in 100% that it is something with JDBC and Java
middleware.

See my code in this tread. I posted it twice altready.
And it's so pity when I open two windows for the same Solaris and see
that every 100'th SQL comes from perl in 5 seconds when Java produce
the same answer in the same exactly time and for the same exactly SQLs
(so there is no Oracle cache or hints or statistics, tune and so on are
different) 2-3 times slower.
And, trust me. There is no more pain for me that type the same perl
command in perl windows 3 times when it is still first run for Java and
for exactly the same result total.

Loosing hope.
Alex Kizub.

 

[Tom Dyess]

Tom:
Thanks for sharing you experience. I really appreciate it.
Especially these are question which I would ask too.
As you already read I have big experience too.

Exactly the same.

pairs?
I hate such design too. Let say in another query from different
database (and different time) we have these pairs.

No. pairs are different.

would cover the gammut of ~2.5x
difference in speed.

That was my impression too. Just before yetaerday's night.

I know I can improve this query. But that's not the point. It will be
improved in both languages and it still will be no difference. SQLs are
exactly the same.
Stupid, impractical but the same.


perl hitting
production, java hitting some crappy 90mhz oracle box someone threw
together).

Oracle on one box, Java and perl on another. Both production.

Yes and no. The same result. See previous answer about SQL.

time?
Don't use debug only start and end time. Code is simple. See my another
answer in the thread.

reconnecting every script).
Yes. One connection and it's alive for hours. No bad connections or
another exceptions.
Results are the same in perl and Java. But perl runs 2.5 faster.
I run two windows on the same Solaris box. In one I run java. In
another in the same time (it's about one hour) I can run perl program
twice for the same result and have another 10-15 minutes for third run.

creating a
large single Java app that calls all scripts?
All SQLs are in one thread and in one cycle. One by one. Just like
perl.
Of course creating of thousand of threads could be more useful. But
what about poor Oracle? To be honest, our DBAs already called me and
asked to stop my SQL and run in the middle of the night. It was first
call from them in 5 years.

try.
I tried these and a lot of others.
BECAUSE I DON"t WANT TO USE PERL!
I like it too, but support of hundreds of scripts is a nightmare!
And what arguments do I have to switch to Java?
20 minutes vs. hour!

doing is
sending a SQL statement to Oracle, Java is done until you get the
result. It
doesn't sound the same. By same I mean you are connecting to the same
server, same instance with the same client on the same workstation,
just two different languages.

Don't use threads. As well as perl. See previous answer.

This is Solaris with 8Gb. Sure enough to store 1000 keys Hashtable.


run 17 queries on the home page, and it comes back instantly. Oracle
hauls-ass with Java.

Sorry, don't understand. A lot of resources. Don't know how they can
help me. Mostly I know all this and always was sure that Oracale loves
Java as well as me. But it doesn't help me to prove my point for
managers.
Actually what point? Fact is fact. I can't deny it even I don't like
it.

Maybe it's OCI. But I can't install it on produstion server. I should
use what already exists. So, perl has OCI, JDBC doesn't and perl
forever?
Very sad Certified and with huge experience Java programmer
Alex Kizub.

The point with the Oraclepower comment was that it is written in Java and
runs very fast with Oracle. It was also a shameless plug, so you can ignore
it. Lol. I'm using oraclejdbc14.jar and connect using this string:

jdbcracle:thin:" + username + "/" + password + "@" + server + ":" + port +
":" + sid;

using this driver:

oracle.jdbc.driver.OracleDriver

I would suggest stepping through your code and see if there is any
distinguishable pause between the line before the execution and after. This
may help to determine if its the connection or the post-processing that is
making the difference. I would guess the latter, but it's better to not
guess at this point.

I'm gonna see if I can optimize the code a little, but I'm relatively new to
Java compared to Delphi, so I don't know that I will be able to do much.

Tom Dyess
OraclePower.com

 

[Tom Dyess]

The point with the Oraclepower comment was that it is written in Java and
runs very fast with Oracle. It was also a shameless plug, so you can
ignore it. Lol. I'm using oraclejdbc14.jar and connect using this string:

jdbcracle:thin:" + username + "/" + password + "@" + server + ":" + port
+ ":" + sid;

using this driver:

oracle.jdbc.driver.OracleDriver

I would suggest stepping through your code and see if there is any
distinguishable pause between the line before the execution and after.
This may help to determine if its the connection or the post-processing
that is making the difference. I would guess the latter, but it's better
to not guess at this point.

I'm gonna see if I can optimize the code a little, but I'm relatively new
to Java compared to Delphi, so I don't know that I will be able to do
much.

Tom Dyess
OraclePower.com

Ok, after looking at the code, I see some mistakes, so would you send the
exact code including the SQL statement? For example

for (int i=0; i<2000; i++)
ps.setInt(1, i);

rs = ps.executeQuery();

I think you mean to put the rs = ps.executeQuery() in a block when it
currently isn't:

for (int i=0; i<2000; i++) {
ps.setInt(1, i);

rs = ps.executeQuery();
}

Please send the exact code so we can run it and establish a time baseline
before tuning. Also,

connection.getPreparedStatement("select 'key', sysdate+? from dual");

If you are using java.sql.Connection, it's prepareStatement(String), but I
don't know if you have the java.sql.Connection wrapped in an aggregating
object or not (I do).

 

[kjc]

The point with the Oraclepower comment was that it is written in Java and
runs very fast with Oracle. It was also a shameless plug, so you can
ignore it. Lol. I'm using oraclejdbc14.jar and connect using this string:

jdbcracle:thin:" + username + "/" + password + "@" + server + ":" + port
+ ":" + sid;

using this driver:

oracle.jdbc.driver.OracleDriver

I would suggest stepping through your code and see if there is any
distinguishable pause between the line before the execution and after.
This may help to determine if its the connection or the post-processing
that is making the difference. I would guess the latter, but it's better
to not guess at this point.

I'm gonna see if I can optimize the code a little, but I'm relatively new
to Java compared to Delphi, so I don't know that I will be able to do
much.

Tom Dyess
OraclePower.com

Ok, after looking at the code, I see some mistakes, so would you send the
exact code including the SQL statement? For example

for (int i=0; i<2000; i++)
ps.setInt(1, i);

rs = ps.executeQuery();

I think you mean to put the rs = ps.executeQuery() in a block when it
currently isn't:

for (int i=0; i<2000; i++) {
ps.setInt(1, i);

rs = ps.executeQuery();
}

Please send the exact code so we can run it and establish a time baseline
before tuning. Also,

connection.getPreparedStatement("select 'key', sysdate+? from dual");

If you are using java.sql.Connection, it's prepareStatement(String), but I
don't know if you have the java.sql.Connection wrapped in an aggregating
object or not (I do).
[/QUOTE]
Dude, I think you badly want to retract your comments.
The code posted by the OP is correct along one dimension.
He is setting a query value 2K times FIRST, THEN, issuing the query.


Your code example sends a query 2K times to the DB server.
Defintely, NOT a good idea.

 

[Robert Klemme]

Looks like this for me now ((

allow
you to bench mark

See this is production and, actually, third party application.
Do you think they will install something new for Java when perl is
already faster?

Don't understand this. I tried few different JDBC thin drivers
including latest Oracle ojdbc14.jar for our particlular Oracle9i
9.2.0.4 JDBC Drivers.

See another anwers in this thraed. Pretty simple.
Here is code. Exactly. Except the SQL.
Hashtable ht=new Hashtable();
PreparedStatement ps =
conection.getPreparedStatement("select 'key', sysdate+? from
dual");

for (int i=0; i<2000; i++)
ps.setInt(1, i);

rs = ps.executeQuery();
if (rs.next()) {
String key=rs.getString(1);
double value=0;
Double d=(Double)ht.get(key);
if (d!=null) value=d.doubleValue();
value+=rs.getDouble(2);
ht.put(key,new Double(value));

}
rs.close();
}

Still have a little hope. But less and less...
Alex Kizub.

It's quite inefficient to recreate Doubles all the time although this is
the straightforward approach. You can easily avoid that by creating a
mutable double class and put that into the map. This will easily be
faster.

Kind regards

robert

 

[akizub]

It's quite inefficient to recreate Doubles all the time
It's not big deal. I have 2000 SQL so I have 2000 new Doubles.
Sure it's not the point to lost 10 minutes.
I'm talking about significant difference.