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 <mik..itido.com> 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.
>>>>>>
>>>>>>
>>
This archive was generated by hypermail 2.0.0 : Tue Oct 21 2008 - 12:15:23 EDT