Your query generates a few questions as the original discrepancy is quite large and people would have noticed that type of performance hit.
We use stateless session beans to retrieve binary objects as they are efficient and quick enough for our purposes and we have some cached embedded query strings. It runs lightning fast and we're running against Postgresql.
So I would try to get some measurements of the various phases of the bean lifecycle - time at creation, time to get connection and so on. If you print them, the information goes to default output stream, so screen if you are in Windows and I also think logs. You can pipe in Unix. But that would be a start. It at least would tell you where the time is being spent.
The time values listed above are measured by the JVM within the method that performs the database call, so I know exactly where the discrepancy lies:
I check the elapsed time during Connection.prepareStatement() and executeQuery(); executeQuery() is the culprit. The search does not return any rows, so it's not a problem with serialization latency.
Yes, the original discrepancy does seem large enough that if it wasn't a problem specific to my JBoss configuration it would have been noticed elsewhere. I'd be very interested to hear any ideas or troubleshoot checklists that might help me track that down.
However, I don't immediately see how a misconfiguration could explain the massive time drop measured when switching from embedded SQL to stored proc. In both cases, the java code was essentially identical... it's just that a different String was sent across the Connection to the database: "execute foo_proc ?" vs. "select * were name like ?"... in this case, ? was set to "%foo%".
I would have expected that if the discrepancy was based on the SQL being sent, I would see the latency in the Connection.prepareStatement() call. This is definitely not the case, though... at least, not so far as I can determine from within my bean.
Well, the connection you are using in JBoss is simply a WrappedConnection for the underlying JDBC connection. Although from evidence, it doesn't appear to be connection related, you could try creating a real connection in the EJB and performing the same function. At least, empirically you would rule out the database under the same conditions.
If that is the case that there is a difference, under the same platform, it may link into the transaction management I would guess and perhaps then, transaction settings.
Thank you very much for the suggestion to connect directly from the bean method! Doing that, I found *no difference* in speed between the JBoss managed connection and the direct one. Obviously, JBoss was not at fault.
Knowing this, I did some more digging and found out where the problem was. It turns out that WebLogic was not using the jConnect driver I thought it was. The WL5.1 server shipped with its own (version 4) jConnect jar and was using this, rather than the newer (version 5) jConnect jar I installed. It was also using a different driver from within that jar:
as specified in the example sybase-ds.xml shipped with JBoss. By switching to the v4 jConnect jar and driver, my embedded SQL query execution time dropped from ~4.5 seconds to 40 ms!!!!!!!
Interestingly, the stored proc execution times did not change.
Note: I did test the com.sybase.jdbc2.jdbc.SybDriver that shipped with the v5 jConnect... it was just as slow as the SybDataSource
I'd be interested in some of the things you learned in converting from WL 5.1 to jBoss....I'm about to attempt the same thing here (I will be using PostGres
on jBoss, and I was using Oracle on WL)....
Did you convert your EJB's to use the 2.0 spec, or did you just keep 'em at 1.1?