Is perl better? :(((

[Chris Smith]

he should not be calling an sql statement 2000 times, when he can do it in
the server, and return a cursor.

Clearly this is only true in a limited set of situations. Neither you
nor I know enough about Alex's application to know if it's true or not.
Nevertheless, it's not at all relevant to the question of why the
performance between Java and Perl might differ.

It appears the point of your post is that you feel smart. I'm glad you
feel smart. Self-esteem is always a good thing. When you follow up
with incorrect advice, it makes you seem even smarter. When you
accentuate your self-esteem with random cursing in public to show your
disdain for others, it makes you seem like a really nice guy.

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

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation

 

[Lee Fesperman]

Steve, you're a little late here, but just to clarify: that's not true.
Omitting rs.close() in the code mentioned here will not leave open
cursors anywhere, because the cursor will be closed when the next loop
iteration calls PreparedStatement.execute(). The last cursor is closed
when the connection is closed.

That's what the spec says, but I think that closing resultsets is needed for some
backends (Oracle). Also, you should close the prepared statement instead of waiting for
the connection to do the job. IOW, the best practice is always close resultsets,
statements and connections and do it as soon as you don't need them anymore. For a
decent backend, this should not be more costly and will free up resources on the server
.... to everyone's benefit.

 

[Chris Smith]

That's what the spec says, but I think that closing resultsets is needed for some
backends (Oracle).

You're saying that Oracle is providing terribly broken JDBC drivers that
cause a normal and correct JDBC application to cause severe problems on
their server, and hasn't fixed this in nearly 10 years? That would
really be rather astonishing, quite frankly. Any reference to provide
evidence for that?

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

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation

 

[Joe Weinstein]

You're saying that Oracle is providing terribly broken JDBC drivers that
cause a normal and correct JDBC application to cause severe problems on
their server, and hasn't fixed this in nearly 10 years? That would
really be rather astonishing, quite frankly. Any reference to provide
evidence for that?

It's a function of DBMS-side cursors which are associated with open statements
and open result sets. It is common, correct JDBC hygiene to close such objects
ASAP, and for Oracle it is more important than for some DBMSes. Oracle permits
a fixed (configurable) number of cursors that a client can have open at one
time. An application that caches or ignores too many open statements or result
sets can get errord if it exceeds the DBMS limit. It's not a JDBC issue as such.

Joe Weinstein at BEA

 

[Chris Smith]

It's a function of DBMS-side cursors which are associated with open statements
and open result sets. It is common, correct JDBC hygiene to close such objects
ASAP, and for Oracle it is more important than for some DBMSes. Oracle permits
a fixed (configurable) number of cursors that a client can have open at one
time. An application that caches or ignores too many open statements or result
sets can get errord if it exceeds the DBMS limit. It's not a JDBC issue as such.

Apparently you haven't been reading the thread. Oracle's JDBC driver
should not be holding a ResultSet open across a Connection.close() --
nor a commit() unless a non-default holdability was set using JDBC3's
new overloads of createStatement and prepareStatement. Nor should it
survive a Statement.execute() or getMoreResults() (the version with no
parameters). If the Oracle driver is producing ResultSet instances that
sutvive these things, then it is in violation of the spec, including
(but not limited to) sections 10.1, 14.1.3, 14.2.5, Oracle's driver is
broken, and it's broken in a particularly gruesome way.

The spec (somewhat confusingly, and for no good reason that I can see)
allows for greater leeway for Statement.close() -- requiring the driver
to immediately invalidate and "close" the ResultSet, but permitting it
to hold off on releasing resources until the next garbage collection.
That's not a particularly useful allowance, and I imagine that there are
probably no drivers that take advantage of it but still comply with the
spec. It appears that the author of the specification does not actually
understand garbage collection in Java. That theory is confirmed when
section 14.2.5 specifies that calling ResultSet.close() makes the
ResultSet object immediately available for garbage collection -- a
ludicrous requirement that invalidates the entirety of the Java security
model, and requires a heavily modified virtual machine to actually
implement correctly (luckily, no one actually takes this requirement of
the spec seriously).

Clearly that is in fact a JDBC issue, since it's about whether Oracle's
driver complies certain requirements of the JDBC specification or not.
If it is confirmed that this is a problem with Oracle's JDBC driver,
then apparently it will become necessary to make calls to
ResultSet.close() in the middle of a tight loop of PreparedStatement's
execute() calls. That would then be classified as a work-around for a
serious bug, *not* as good general practice for JDBC.

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

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation

 

[Joe Weinstein]

Apparently you haven't been reading the thread.

you're right, sorry.

Oracle's JDBC driver

should not be holding a ResultSet open across a Connection.close() --
nor a commit() unless a non-default holdability was set using JDBC3's
new overloads of createStatement and prepareStatement.

I'll catch up on the thread. Is that happening?

Nor should it
survive a Statement.execute() or getMoreResults() (the version with no
parameters). If the Oracle driver is producing ResultSet instances that
survive these things, then it is in violation of the spec, including
(but not limited to) sections 10.1, 14.1.3, 14.2.5, Oracle's driver is
broken, and it's broken in a particularly gruesome way.

I agree. When last I checked, the result sets that came from standard
calls, such as executeQuery() *were* closed properly when the statement
was closed or re-executed, but those result sets that arrived via
non-standard JDBC, such as via output parameters of procedures,
via their non-standard CallableStatement.getResultSet( int paramIndex)
were not closed. A bug indeed, if it's still true. In fact the driver
used to allow a user to execute a stored procedure via executeQuery()
which must return a result set. The result set was some odd bogus result
set that didn't relate to the procedure output.

The spec (somewhat confusingly, and for no good reason that I can see)
allows for greater leeway for Statement.close() -- requiring the driver
to immediately invalidate and "close" the ResultSet, but permitting it
to hold off on releasing resources until the next garbage collection.
That's not a particularly useful allowance, and I imagine that there are
probably no drivers that take advantage of it but still comply with the
spec. It appears that the author of the specification does not actually
understand garbage collection in Java. That theory is confirmed when
section 14.2.5 specifies that calling ResultSet.close() makes the
ResultSet object immediately available for garbage collection -- a
ludicrous requirement that invalidates the entirety of the Java security
model, and requires a heavily modified virtual machine to actually
implement correctly (luckily, no one actually takes this requirement of
the spec seriously).

Yep, silly. I still have a reference to it. *Try* gc()'ing it...
Even if it is closed and the reference is gone, until getMoreResults()
is called, one could conceivably call getResultSet() again and expect to
get the same already-closed result set, so the statement has to keep a
reference for a while too.

Clearly that is in fact a JDBC issue, since it's about whether Oracle's
driver complies certain requirements of the JDBC specification or not.
If it is confirmed that this is a problem with Oracle's JDBC driver,
then apparently it will become necessary to make calls to
ResultSet.close() in the middle of a tight loop of PreparedStatement's
execute() calls. That would then be classified as a work-around for a
serious bug, *not* as good general practice for JDBC.

I always close everything asap... Chaqu'un a son gout... but yes, I wasn't
reading the thread.
Joe Weinstein at BEA

 

[Chris Smith]

Oracle's JDBC driver

I'll catch up on the thread. Is that happening?

I don't know. Lee suggested that it is happening. I hope he's wrong.

Yep, silly. I still have a reference to it. *Try* gc()'ing it...
Even if it is closed and the reference is gone, until getMoreResults()
is called, one could conceivably call getResultSet() again and expect to
get the same already-closed result set, so the statement has to keep a
reference for a while too.

I agree with all but the getResultSet() piece. getResultSet() is only
guaranteed to work once per result. The very first time you call it for
a given result, the Statement is entirely within its rights to throw
away any references to that ResultSet. After that, it can throw
SQLException instead of returning a ResultSet.

I always close everything asap... Chaqu'un a son gout... but yes, I wasn't
reading the thread.

I certainly would not argue for leaving things open indefinitely.
Specifically, though, we're discussing whether it's reasonable to omit a
call to ResultSet.close() in the middle of a tight loop of calls to
PreparedStatement.executeQuery(). As far as I'm concerned, that IS
basically closing things ASAP; the next call to executeQuery is going to
happen immediately, and it will close the ResultSet. Unless Lee is
right about Oracle's driver bug...

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

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation

 

[Dimitri Maziuk]

Chris Smith sez:

I don't know. Lee suggested that it is happening. I hope he's wrong.

Yep, he is. I don't know about result sets returned from stored
procedures, but regular ones get closed as per specs (at least
in 8i and 10g).

IME it's statement.close() that releases server-side cursor;
you want to explicitly close your statements ASAP, if you leave
that to gc you're likely to run out of cursors. Moreover, it
seems that gc'ing unclosed statements does not always close
server side of the connection, sometimes it sits there hours
waiting to time out.

I certainly would not argue for leaving things open indefinitely.
Specifically, though, we're discussing whether it's reasonable to omit a
call to ResultSet.close() in the middle of a tight loop of calls to
PreparedStatement.executeQuery(). As far as I'm concerned, that IS
basically closing things ASAP; the next call to executeQuery is going to
happen immediately, and it will close the ResultSet.

I always close the statements and usually close associated result
set just before stat.close(), just to be sure. I never close the
result set in a middle of a loop (tight or not) and so far I haven't
run into any problems.

.... Unless Lee is

right about Oracle's driver bug...

There's 2 thin drivers in 10g: new ...OracleDriver and old
....driver.OracleDriver. The old one is retained for compatibility
backwards, all bugfixes and new features go into new one. Plus
there's not-thin driver(s?), RowSet implementation jar, etc.
There may be that Lee's bug is in one or two of them, who knows...

Dima

 

[Lee Fesperman]

You're saying that Oracle is providing terribly broken JDBC drivers that
cause a normal and correct JDBC application to cause severe problems on
their server, and hasn't fixed this in nearly 10 years? That would
really be rather astonishing, quite frankly. Any reference to provide
evidence for that?

Don't be naive. Do you really think Oracle gives a fig about compatibility with specs?
SQL Server is much worse, and DB2 is not so hot. These are the 800 pound gorillas. If
you want to use them, you have to put up with their eccentricities. After that there is
a fairly large number of other JDBC drivers, including more than one for some DBMSs and
then there are different versions. Add all the ODBC drivers accessible through JDBC-ODBC
bridges, you'll find that compatibility is all over the map.

Remember that the next time you recommend a feature like resultset holdability (new in
JDBC 3). The rate of adoption is slow and spotty. Some will never support new features.

At least with JDBC, much of this can be covered with defensive programming. Have you
ever written a JDBC application intended to run with a variety of drivers and backends?
I have (several in fact). It is possible to get reasonable coverage if you're careful.
The situation with SQL is much worse. Everyone has their own proprietary version of the
language.

 

[Dimitri Maziuk]

Lee Fesperman sez:
....

At least with JDBC, much of this can be covered with defensive programming. Have you
ever written a JDBC application intended to run with a variety of drivers and backends?
I have (several in fact). It is possible to get reasonable coverage if you're careful.
The situation with SQL is much worse. Everyone has their own proprietary version of the
language.

I love how Oracle's thin driver doesn't have
DatabaseMetadata.getDatabaseXXXVersion(). So that you can't do
if( version >= 10 ) stat.executeUpdate( "drop table foo purge" );
else stat.executeUpdate( "drop table foo" );
without a pattern match on the product name string.
Hatess 'em, we doess...

Dima

 

[Lee Fesperman]

Lee Fesperman sez:
...

I love how Oracle's thin driver doesn't have
DatabaseMetadata.getDatabaseXXXVersion(). So that you can't do
if( version >= 10 ) stat.executeUpdate( "drop table foo purge" );
else stat.executeUpdate( "drop table foo" );
without a pattern match on the product name string.
Hatess 'em, we doess...

Interesting. What does "drop table foo purge" mean?

 

[Walter Mitty]

And we're talking about a process that runs for as much as ten minutes,
so it should not be significant.

Since he's creating/deleting all the time (probably), it might well be.

 

[Dimitri Maziuk]

Lee Fesperman sez:
....

Interesting. What does "drop table foo purge" mean?

It doesn't delete tables anymore, it renames them to
RECYCLEQWJQ#(*$@!#JR.BIN instead. "Purge" means
"don't clutter my tablespace with junk, I really mean
to delete the fscking thing and I know what I'm doing".

Dima

 

[Lee Fesperman]

Lee Fesperman sez:
...

It doesn't delete tables anymore, it renames them to
RECYCLEQWJQ#(*$@!#JR.BIN instead. "Purge" means
"don't clutter my tablespace with junk, I really mean
to delete the fscking thing and I know what I'm doing".

Thanks for the explanation. The 800 pound gorilla at work again ;^)

 

[Walter Mitty]

Profile doesn't have sense for me. It's obvious.

There is no such thing as obvious.

Post code that compiles and runs or you'll find that help dries up
pretty damn fast.

Profilers are there for a reason : one such as yours.

 

[Walter Mitty]

What is your definition of "properly indented"?
Do you mean that hideous "K&R" standard of placing the brace
at the end of the line?

Which makes perfect sense since blocks of code are bordered by only two
border - much more easy to see than three lines:

void NoItsNot(){
while(int i=0;i<10{
// blah
// blah
i++;
}
}

void myMethodIsBetterThanYours()
// a light-hearted dig at the style wars...
{
if ( youThinkYoursIsBetter() )
{
// flame wars begin ...
}
}
regards,
Dar7yl.

--
Walter Mitty
-
Useless, waste of money research of the day : http://tinyurl.com/3tdeu
" Format wars could 'confuse users'"
http://www.tinyurl.com