5 Replies Latest reply on May 29, 2003 3:57 AM by jbrosenberg

    Query handling vs. WebLogic

    jtrichards

      Hello,

      I'm working on a migration off WebLogic 5.1 to JBoss 3.2.1. I've gotten my application deployed and running comparably to the WebLogic installation and am now testing to ensure equivalent performance. As part of that, I found that one query ran significantly slower on JBoss than WL... 4.5 seconds, as opposed to 30 ms., with the same class/code on the same server (SMP Linux 2.4.7) and database (Sybase ASE 11).

      The query originally used SQL code generated in a session bean and prepared into a statement and executed on a non-transactional pooled connection (no-tx-datasource). As part of my investigation, I changed the code to instead execute a stored procedure. This was just a change of the SQL sent across the connection; it still used Connection.prepareStatement() and PreparedStatement.executeQuery(). However, as soon as I did that, the JBoss query time dropped to 66ms.

      This leaves me with a few questions: first, what is JBoss doing with the java-embedded SQL that WL isn't, so as to require all that extra time? Second, is it unwise to be using embedded SQL with JBoss because of the performance hit? Third, even with the stored proc, the JBoss query still takes twice as long as the embedded WL query... where should I be looking to shrink this gap?

      Thank you for any thoughts.

        • 1. Re: Query handling vs. WebLogic
          jonlee

          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.

          • 2. Re: Query handling vs. WebLogic
            jtrichards

            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:

            PreparedStatement.executeQuery()

            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.

            • 3. Re: Query handling vs. WebLogic
              jonlee

              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.

              • 4. Re: Query handling vs. WebLogic
                jtrichards

                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:

                com.sybase.jdbc.SybDriver

                instead of:

                com.sybase.jdbc2.jdbc.SybDataSource

                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

                • 5. Re: Query handling vs. WebLogic
                  jbrosenberg

                  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?

                  Jason