customize jboss to speed up creates and updates

[shogun]

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

[davidjencks]

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.

[dsundstrom]

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

[shogun]

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.

[shogun]

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?

[davidjencks]

"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.

[zetzet]

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.