You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cayenne.apache.org by John Huss <jo...@gmail.com> on 2019/06/04 14:39:51 UTC
Re: Auto-generated columns on Oracle
Just to follow up on this, after some experimentation it looks like using
PKs generated by the database can be much slower when doing bulk inserts,
because Cayenne doesn't (can't?) do a batch bind to execute the inserts as
a batch and instead has to execute them one by one. With a large batch of
row for the same entity this can make a huge difference. So moving to
generated PKs might not be the best idea for many use cases.
On Wed, May 29, 2019 at 4:45 PM Hugi Thordarson <hu...@karlmenn.is> wrote:
> /me instantly starts modifying DBs.
>
> Thanks for pointing this out John!
>
> - hugi
>
>
> > On 29 May 2019, at 15:23, John Huss <jo...@gmail.com> wrote:
> >
> > Doh! Turns out Cayenne already supports this for Postgres. Who knew! :-P
> >
> > 2621 [main] INFO org.apache.cayenne.log.JdbcEventLogger - INSERT INTO
> test
> > (payload) VALUES (?)
> >
> > 2621 [main] INFO org.apache.cayenne.log.JdbcEventLogger - [bind:
> > 1->payload:'test']
> >
> > 2632 [main] INFO org.apache.cayenne.log.JdbcEventLogger - Generated PK:
> > test.id = 1
> >
> > On Tue, May 28, 2019 at 12:30 PM Andrus Adamchik <andrus@objectstyle.org
> >
> > wrote:
> >
> >> Thanks for the pointer. If PG passes generated PK back through the JDBC
> >> driver and we can read it after an INSERT, we most definitely should.
> >> Creating extraneous objects in DB for the sake of PK generation feels
> old.
> >>
> >> Andrus
> >>
> >>> On May 28, 2019, at 6:47 PM, John Huss <jo...@gmail.com> wrote:
> >>>
> >>> Postgresql also recently improved their support in this area (in
> version
> >>> 10). It's more like syntactic sugar on top of things you could already
> >> do,
> >>> but I'd be interested to know if it can be used by Cayenne in the same
> >> way
> >>> as the MySQL auto-increment columns.
> >>>
> >>> The DDL syntax is supposedly an SQL standard:
> >>>
> >>> CREATE TABLE test_new (
> >>> id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
> >>> payload text
> >>> );
> >>>
> >>>
> >>> On Mon, May 27, 2019 at 1:49 AM Andrus Adamchik <
> andrus@objectstyle.org>
> >>> wrote:
> >>>
> >>>> Looks like Oracle started supporting auto-increment columns since 12c:
> >>>>
> >>>>
> >>>>
> >>
> https://www.arungudelli.com/tutorial/oracle/auto-increment-identity-column-in-oracle-table-primary-key/
> >>>>
> >>>> We should try using that in the adapter.
> >>>>
> >>>> Andrus
> >>
> >>
>
>
Re: Auto-generated columns on Oracle
Posted by Arseni Bulatski <ab...@objectstyle.com>.
I've checked case of oracle auto-incremented columns in cayenne.
To start use auto-incremented columns first need to set
setSupportGeneratedKeys(true) in OracleAdapter, second have tables with
auto-incremented columns in database.
And it started to work.
On Tue, Jun 4, 2019 at 5:45 PM John Huss <jo...@gmail.com> wrote:
> Yep, Postgres.
>
> On Tue, Jun 4, 2019 at 9:43 AM Andrus Adamchik <an...@objectstyle.org>
> wrote:
>
> > Hmm.. In theory auto-pk should be compatible with batching. Is this on
> > PostgreSQL?
> >
> > Andrus
> >
> > > On Jun 4, 2019, at 5:39 PM, John Huss <jo...@gmail.com> wrote:
> > >
> > > Just to follow up on this, after some experimentation it looks like
> using
> > > PKs generated by the database can be much slower when doing bulk
> inserts,
> > > because Cayenne doesn't (can't?) do a batch bind to execute the inserts
> > as
> > > a batch and instead has to execute them one by one. With a large batch
> of
> > > row for the same entity this can make a huge difference. So moving to
> > > generated PKs might not be the best idea for many use cases.
> > >
> > > On Wed, May 29, 2019 at 4:45 PM Hugi Thordarson <hu...@karlmenn.is>
> > wrote:
> > >
> > >> /me instantly starts modifying DBs.
> > >>
> > >> Thanks for pointing this out John!
> > >>
> > >> - hugi
> > >>
> > >>
> > >>> On 29 May 2019, at 15:23, John Huss <jo...@gmail.com> wrote:
> > >>>
> > >>> Doh! Turns out Cayenne already supports this for Postgres. Who knew!
> > :-P
> > >>>
> > >>> 2621 [main] INFO org.apache.cayenne.log.JdbcEventLogger - INSERT
> INTO
> > >> test
> > >>> (payload) VALUES (?)
> > >>>
> > >>> 2621 [main] INFO org.apache.cayenne.log.JdbcEventLogger - [bind:
> > >>> 1->payload:'test']
> > >>>
> > >>> 2632 [main] INFO org.apache.cayenne.log.JdbcEventLogger - Generated
> > PK:
> > >>> test.id = 1
> > >>>
> > >>> On Tue, May 28, 2019 at 12:30 PM Andrus Adamchik <
> > andrus@objectstyle.org
> > >>>
> > >>> wrote:
> > >>>
> > >>>> Thanks for the pointer. If PG passes generated PK back through the
> > JDBC
> > >>>> driver and we can read it after an INSERT, we most definitely
> should.
> > >>>> Creating extraneous objects in DB for the sake of PK generation
> feels
> > >> old.
> > >>>>
> > >>>> Andrus
> > >>>>
> > >>>>> On May 28, 2019, at 6:47 PM, John Huss <jo...@gmail.com>
> wrote:
> > >>>>>
> > >>>>> Postgresql also recently improved their support in this area (in
> > >> version
> > >>>>> 10). It's more like syntactic sugar on top of things you could
> > already
> > >>>> do,
> > >>>>> but I'd be interested to know if it can be used by Cayenne in the
> > same
> > >>>> way
> > >>>>> as the MySQL auto-increment columns.
> > >>>>>
> > >>>>> The DDL syntax is supposedly an SQL standard:
> > >>>>>
> > >>>>> CREATE TABLE test_new (
> > >>>>> id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
> > >>>>> payload text
> > >>>>> );
> > >>>>>
> > >>>>>
> > >>>>> On Mon, May 27, 2019 at 1:49 AM Andrus Adamchik <
> > >> andrus@objectstyle.org>
> > >>>>> wrote:
> > >>>>>
> > >>>>>> Looks like Oracle started supporting auto-increment columns since
> > 12c:
> > >>>>>>
> > >>>>>>
> > >>>>>>
> > >>>>
> > >>
> >
> https://www.arungudelli.com/tutorial/oracle/auto-increment-identity-column-in-oracle-table-primary-key/
> > >>>>>>
> > >>>>>> We should try using that in the adapter.
> > >>>>>>
> > >>>>>> Andrus
> > >>>>
> > >>>>
> > >>
> > >>
> >
> >
>
Re: Auto-generated columns on Oracle
Posted by John Huss <jo...@gmail.com>.
Yep, Postgres.
On Tue, Jun 4, 2019 at 9:43 AM Andrus Adamchik <an...@objectstyle.org>
wrote:
> Hmm.. In theory auto-pk should be compatible with batching. Is this on
> PostgreSQL?
>
> Andrus
>
> > On Jun 4, 2019, at 5:39 PM, John Huss <jo...@gmail.com> wrote:
> >
> > Just to follow up on this, after some experimentation it looks like using
> > PKs generated by the database can be much slower when doing bulk inserts,
> > because Cayenne doesn't (can't?) do a batch bind to execute the inserts
> as
> > a batch and instead has to execute them one by one. With a large batch of
> > row for the same entity this can make a huge difference. So moving to
> > generated PKs might not be the best idea for many use cases.
> >
> > On Wed, May 29, 2019 at 4:45 PM Hugi Thordarson <hu...@karlmenn.is>
> wrote:
> >
> >> /me instantly starts modifying DBs.
> >>
> >> Thanks for pointing this out John!
> >>
> >> - hugi
> >>
> >>
> >>> On 29 May 2019, at 15:23, John Huss <jo...@gmail.com> wrote:
> >>>
> >>> Doh! Turns out Cayenne already supports this for Postgres. Who knew!
> :-P
> >>>
> >>> 2621 [main] INFO org.apache.cayenne.log.JdbcEventLogger - INSERT INTO
> >> test
> >>> (payload) VALUES (?)
> >>>
> >>> 2621 [main] INFO org.apache.cayenne.log.JdbcEventLogger - [bind:
> >>> 1->payload:'test']
> >>>
> >>> 2632 [main] INFO org.apache.cayenne.log.JdbcEventLogger - Generated
> PK:
> >>> test.id = 1
> >>>
> >>> On Tue, May 28, 2019 at 12:30 PM Andrus Adamchik <
> andrus@objectstyle.org
> >>>
> >>> wrote:
> >>>
> >>>> Thanks for the pointer. If PG passes generated PK back through the
> JDBC
> >>>> driver and we can read it after an INSERT, we most definitely should.
> >>>> Creating extraneous objects in DB for the sake of PK generation feels
> >> old.
> >>>>
> >>>> Andrus
> >>>>
> >>>>> On May 28, 2019, at 6:47 PM, John Huss <jo...@gmail.com> wrote:
> >>>>>
> >>>>> Postgresql also recently improved their support in this area (in
> >> version
> >>>>> 10). It's more like syntactic sugar on top of things you could
> already
> >>>> do,
> >>>>> but I'd be interested to know if it can be used by Cayenne in the
> same
> >>>> way
> >>>>> as the MySQL auto-increment columns.
> >>>>>
> >>>>> The DDL syntax is supposedly an SQL standard:
> >>>>>
> >>>>> CREATE TABLE test_new (
> >>>>> id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
> >>>>> payload text
> >>>>> );
> >>>>>
> >>>>>
> >>>>> On Mon, May 27, 2019 at 1:49 AM Andrus Adamchik <
> >> andrus@objectstyle.org>
> >>>>> wrote:
> >>>>>
> >>>>>> Looks like Oracle started supporting auto-increment columns since
> 12c:
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>
> >>
> https://www.arungudelli.com/tutorial/oracle/auto-increment-identity-column-in-oracle-table-primary-key/
> >>>>>>
> >>>>>> We should try using that in the adapter.
> >>>>>>
> >>>>>> Andrus
> >>>>
> >>>>
> >>
> >>
>
>
Re: Auto-generated columns on Oracle
Posted by Andrus Adamchik <an...@objectstyle.org>.
Hmm.. In theory auto-pk should be compatible with batching. Is this on PostgreSQL?
Andrus
> On Jun 4, 2019, at 5:39 PM, John Huss <jo...@gmail.com> wrote:
>
> Just to follow up on this, after some experimentation it looks like using
> PKs generated by the database can be much slower when doing bulk inserts,
> because Cayenne doesn't (can't?) do a batch bind to execute the inserts as
> a batch and instead has to execute them one by one. With a large batch of
> row for the same entity this can make a huge difference. So moving to
> generated PKs might not be the best idea for many use cases.
>
> On Wed, May 29, 2019 at 4:45 PM Hugi Thordarson <hu...@karlmenn.is> wrote:
>
>> /me instantly starts modifying DBs.
>>
>> Thanks for pointing this out John!
>>
>> - hugi
>>
>>
>>> On 29 May 2019, at 15:23, John Huss <jo...@gmail.com> wrote:
>>>
>>> Doh! Turns out Cayenne already supports this for Postgres. Who knew! :-P
>>>
>>> 2621 [main] INFO org.apache.cayenne.log.JdbcEventLogger - INSERT INTO
>> test
>>> (payload) VALUES (?)
>>>
>>> 2621 [main] INFO org.apache.cayenne.log.JdbcEventLogger - [bind:
>>> 1->payload:'test']
>>>
>>> 2632 [main] INFO org.apache.cayenne.log.JdbcEventLogger - Generated PK:
>>> test.id = 1
>>>
>>> On Tue, May 28, 2019 at 12:30 PM Andrus Adamchik <andrus@objectstyle.org
>>>
>>> wrote:
>>>
>>>> Thanks for the pointer. If PG passes generated PK back through the JDBC
>>>> driver and we can read it after an INSERT, we most definitely should.
>>>> Creating extraneous objects in DB for the sake of PK generation feels
>> old.
>>>>
>>>> Andrus
>>>>
>>>>> On May 28, 2019, at 6:47 PM, John Huss <jo...@gmail.com> wrote:
>>>>>
>>>>> Postgresql also recently improved their support in this area (in
>> version
>>>>> 10). It's more like syntactic sugar on top of things you could already
>>>> do,
>>>>> but I'd be interested to know if it can be used by Cayenne in the same
>>>> way
>>>>> as the MySQL auto-increment columns.
>>>>>
>>>>> The DDL syntax is supposedly an SQL standard:
>>>>>
>>>>> CREATE TABLE test_new (
>>>>> id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
>>>>> payload text
>>>>> );
>>>>>
>>>>>
>>>>> On Mon, May 27, 2019 at 1:49 AM Andrus Adamchik <
>> andrus@objectstyle.org>
>>>>> wrote:
>>>>>
>>>>>> Looks like Oracle started supporting auto-increment columns since 12c:
>>>>>>
>>>>>>
>>>>>>
>>>>
>> https://www.arungudelli.com/tutorial/oracle/auto-increment-identity-column-in-oracle-table-primary-key/
>>>>>>
>>>>>> We should try using that in the adapter.
>>>>>>
>>>>>> Andrus
>>>>
>>>>
>>
>>