cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Shea <>
Subject Re: Talking to multiple database types with Cayenne.
Date Tue, 21 Oct 2008 16:14:33 GMT
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!

> 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 <> 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
>>>> 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.
>>>> have experimented with customizing the mapping.xml file at runtime based
>>>> 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
>>>>> 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
>>>>>> to be able to run whether I am using an Oracle database or a MySQL
>>>>>> (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
>>>>>> how to approach this problem. Currently, I am planning on simply
>>>>>> 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
>>>>>> is defined via JNDI.
>>>>>> Does anyone think that this is a good idea or a bad idea, or have
>>>>>> better way of doing this? =)  I am using Cayenne 2.0.4.
>>>>>> Thanks!
>>>>>> Mike Shea.

View raw message