Hi Michael,
Thanks for your reply. It looks like this is a case where trying to be
clever has ended up wasting my time and not resulted any real
performance gains. Oh well, it happens, and I don't have very much work
to do to remove my customizations =). Thanks for the prompt assistance.
And thanks to the whole community here. This mailing list never ceases
to impress me!
Shea.
Hi Mike,
I don't think it is an issue of an extra round-trip to the database
(I'd hope the MySQL JDBC adapter was smart enough to get that value as
part of the insert). The more important issue is the number of
individual inserts that must be done (and possibly updates). When
Cayenne knows all of the PKs upfront (using sequences or the PK
table), it has already assigned all of the PKs to the newly inserted
objects prior to sending to the DB. This allows everything to be sent
over quickly without a lot of back-and-forth communication. With the
AUTOINCREMENT scheme, it is more one-at-a-time and generates more
chatter. And keep in mind those PKs might need to be assigned to
other objects, too (as FKs, for example). This back-and-forth chatter
is what would slow it down more than anything else. Of course, if you
are typically dealing with only inserting one or two records at a
time, it'll be negligible.
As for the PK batching, it is definitely done globally. Each
DataContext will request a PK when it needs one from the PK generator.
There is a PK generator per DataNode. You can have many DataContexts
all funneling through a single DataNode and PK generator.
Hope that helps. If not, ask more questions!
Thanks,
/dev/mrg
On Tue, Oct 21, 2008 at 11:07 AM, Michael Shea <[EMAIL PROTECTED]> wrote:
Does getting the primary keys via the Statement.getGeneratedKeys() require
an extra round-trip to the database? If not, I would think it wouldn't
generate very much overhead, since I'm already sending a statement to the
database, and receiving a response. Otherwise, yes, I can definitely see how
it would be more efficient to batch primary keys.
Questions about Cayenne's primary key batching: Is this done globally,
across contexts? Or would each DataContext instance manage its own batch of
keys to use?
Thanks,
Shea.
Interestingly, using autoincrement in mysql is probably a /hit/ in
performance, compared to the default strategy. Cayenne has to ask mysql what
the id was after every insert.
In the default PK generation scheme, cayenne manages the ids. Hence,
cayenne can batch ids and not have to talk to the db about every pk for
every object.
Robert
On Oct 21, 2008, at 10/219:12 AM , Michael Shea wrote:
Hi Andrus,
Thanks for your reply... Of course, this is almost exactly what Cayenne
is for. I erred in the question I was asking, unfortunately, so I will ask
it properly this time... =). I really just have a minor question to ask:
My two database schemas are *almost* identical, but not quite. The MySQL
database creates primary keys on certain tables by using an AUTOINCREMENT
column. The Oracle database creates primary keys on the equivalent tables by
using a custom sequence. Is there any way for me to change the primary key
generation strategy depending on which DBMS is in use? Or will I have to
choose a strategy that works equally on both Oracle and on MySQL (ie, switch
to using the default strategy?).
I would ideally like to continue using the auto-increment columns on
MySQL, since that intuitively seems like the most efficient method to me. I
have experimented with customizing the mapping.xml file at runtime based on
which type of database I'll be using, before making any calls to cayenne,
that seems to work. But it's also pretty hacky, and will make maintenance
more painful.
Thanks,
Mike Shea.
Hi Michael,
Cayenne will detect the database type automatically on startup and use
the right adapter. So yes, use JNDI to abstract connection information,
beyond that no need for any extra tricks. You generic Cayenne mapping will
just work regardless of the db type.
Andrus
On Oct 20, 2008, at 11:42 AM, Michael Shea wrote:
Hi guys,
I am working on a project in which I have to support multiple database
types. They do not have to be supported simultaneously - I need my program
to be able to run whether I am using an Oracle database or a MySQL database
(the databases will have the same schema), but the database type does not
change at runtime.
I am wondering if there are any best practices or recommendations for
how to approach this problem. Currently, I am planning on simply creating
two sets of cayenne configuration files in different directories (ie, a
map.xml and a cayenne.xml file), and just adding one or the other of these
directories to the classpath, depending on some initial configuration
parameters that tell me what type of database I'm accessing. The datasource
is defined via JNDI.
Does anyone think that this is a good idea or a bad idea, or have a
better way of doing this? =) I am using Cayenne 2.0.4.
Thanks!
Mike Shea.
