6 Replies Latest reply on Apr 28, 2002 1:28 PM by Jürgen Zeller

    customize jboss to speed up creates and updates

    Dirk Bogun Newbie

      Hi,

      how can jboss be customized to perform better when using creates or updates? For example, I saw in the log, that befor a create is written to the database, a select * ... is queried. Why does jboss use this query? The database will generate a duplicate key error, when a row already exists.

      Before I tried jboss, I worked with bea weblogic server 6.1. A create under jboss takes twice as much time as under weblogic. What is the reason for this performance leak?

      I disabled all traces, but I think, that a trace system can not be the reason.

      Tx

      shogun

        • 1. Re: customize jboss to speed up creates and updates
          David Jencks Master

          As far as I can tell, there is no way using jdbc to reliably distinguish a primary key violation from network failure or someone unplugging the db server. Therefore the only plausible response to an exception is to rollback the transaction.

          If you can suggest a way around this problem I'm all ears. The only thing I've thought of so far is to write a jca wrapper for each jdbc driver that understands the exceptions from that driver and can distinguish between communication layer/fatal errors and non-fatal errors. I'm still not sure how this information could be propagated to the cmp engine, however.

          • 2. Re: customize jboss to speed up creates and updates
            Dain Sundstrom Master

            I was thinking about allowing this exact solution for JBoss 4.0. I would just look for a special exception from the JCA connection.

            • 3. Re: customize jboss to speed up creates and updates
              Dirk Bogun Newbie

              What's about the sqlstate in the SQLException. The code describes a lot about the state of the db. Under http://users.ox.ac.uk/~univ0938/sqlstate.html this codes are listed. This state follows the xopen sql standard and should be supported by all dbs or driver (like jdbc).

              It's true, that there is no exception for a duplicate key error, but is this important. When an exception occurs and it is no warning (or even for a warning), the transaction had to be rolled back, because you could not say what is really happend.

              • 4. Re: customize jboss to speed up creates and updates
                Dirk Bogun Newbie

                What I would to say is, the db tells you, if the row is created or not. When a query is send befor, it is no guarantee, that the create will be successfully executed. Do you ask also for tables with no primary key?

                But, what I actually want to know is, if there are possibilities to tune up jboss with existing parameters?

                • 5. Re: customize jboss to speed up creates and updates
                  David Jencks Master

                  "should" and "are, even by one vendor" are rather different. The reference you cite appears to be oracle specific, due to its frequent citation of things like "PL/SQL" and "Oracle". Do you have any evidence that there is a non-proprietary standard that is followed by even one vendor?

                  I don't know that much about the x/open activities, but the primary goal of the sql committe seems to be to allow all paying vendors to claim compliance while selling completely incompatible "sql" "solutions". Perhaps unfortunately, and presumably in the interests of having more than 0 implementations, sun did not really choose to fix this in the jdbc standard. I really think it wouldn't have been all that much to ask to have different exceptions for "this connection is now totally unusable, forever" and "The connections fine, but there was a problem with the last request"


                  Dain-- We might not even need the special exceptions -- the resource adapter is supposed to notify the ConnectionManager (and anyone who registers a listener) with a connectionErrorOccurred event when it detects that a connection died; after this event, the adapter throws an exception. Maybe you can register for this either directly with the adapter or with the ConnectionManager.

                  • 6. Re: customize jboss to speed up creates and updates
                    Jürgen Zeller Newbie

                    I also noted that behaviour.

                    In most cases, the performance of a typical application using
                    O/R mapping is proportional to the number of SQL commands it
                    generates.
                    I did some performance testing, and it's nearly half the time
                    for doing bulk inserts when you disable the select's.

                    A possible workaround would be to revert the select/insert sequence:
                    insert the row first, and when it fails, check with a select if that
                    primary key existed.

                    I know that there are situations/database drivers where this
                    is not 100% o.k., so it should be an option.

                    Bye,

                    Jürgen

                    P.S.: I also noted that JBOSS creates a new PreparedStatement for each
                    INSERT. Depending on your database driver this a very expensive
                    operation.
                    I'm not shure how to cache that PreparedStatement in
                    JDBCCreateEntityCommand, but it would help a lot.