Re: Multi row insert with On Duplicate Update (Microsoft Sql Server)
Thanks a lot, Victoria.
I'll investigate and answer directly on the stack overflow question.
Cheers,
Lukas
2018-03-05 13:34 GMT+01:00 :
> Thank you for the reply, Lukas!
>
> I've posted a question on StackOverflow with concrete examples and
> explanation:
> https://stackoverflow.com/questions/49110621/batch-
> insert-onduplcatekeyignore-jooq
>
> Thanks in advance,
> Victoria
>
> понедельник, 5 марта 2018 г., 12:59:39 UTC+3 пользователь Lukas Eder
> написал:
>>
>> Hi Victoria,
>>
>> Thank you very much for your message.
>>
>> I'm not sure yet if this is really related to the original discussion -
>> if in doubt, you can always start a new discussion on the user group.
>>
>> In order to be able to help you, I will need quite a bit of additional
>> information, ideally a minimal, complete, verifiable example. See this
>> website here for some details:
>> https://stackoverflow.com/help/mcve
>>
>> You've mentioned the loader API and batch, and that you've run into some
>> errors. But how can I reproduce those errors? What does your class /
>> composite key look like? What does your schema look like? What does your
>> jOOQ code look like?
>>
>> The more information you provide, the easier it will be to help.
>>
>> Thanks,
>> Lukas
>>
>>
>> 2018-03-02 17:42 GMT+01:00 :
>>
>>> Dear Lucas!
>>>
>>> I've faced such issue with the loader api: I have a class that contains
>>> only id values. This class is actually a composite primary key itself. I
>>> want to make batch insert for this class using loader api. But if I use
>>> onDuplicateKeyUpdate - I receive an error like there is no fields to
>>> update(truth, there is only part of primary key). If I use
>>> onDuplicateKeyIgnore - I receive an error Cannot apply batch loading with
>>> onDuplicateKeyIgnore flag. If I use nothing - sometimes I face duplicate
>>> key violation exception. Can you advice me any approach to deal with this
>>> case? I will really appreciate any help.
>>>
>>> The point is that I want to make insertInto(TABLE).set(rec
>>> ord).onDuplicateKeyUpdate().set(record).execute() in batch
>>>
>>> Thanks in advance,
>>>Victoria
>>>
>>> вторник, 16 августа 2016 г., 0:03:56 UTC+3 пользователь Lukas Eder
>>> написал:
Hi Benjamin,
I'm sorry for the delay. Yes, there is a way to tell jOOQ that.
Field.equal(T) is just convenience for Field.equal(val(T)), but you can
also pass an "inline" bind variable by using DSL.inline(T):
http://www.jooq.org/javadoc/latest/org/jooq/impl/DSL.html#in
line-java.lang.Integer-
This helps having fine-grained control over which bind variable gets to
be inlined and which variable gets to be passed as a "?". The other option,
as you've already discovered, is to force all bind variables to be inlined
via STATIC_STATEMENTS. That flag also executes a java.sql.Statement, rather
than a java.sql.PreparedStatement
Both options are documented here, for the reference:
http://www.jooq.org/doc/latest/manual/sql-building/bind-
values/inlined-parameters
I'm curious about what takes so long when you use bind variables. Have
you profiled your SQL usage? How many bind variables do you have, and how
big are they? Do your actual queries look very different from the example
that you've posted?
Thanks for the loader hint. I'll investigate that. Quite possibly,
there's a bug that prevents using that combination of flags. Note, though,
you don't have to use STATIC_STATEMENTS for everything. You can use it only
for those few queries that run slowly.
Hope this helps,
Lukas
2016-08-11 12:17 GMT+02:00 Benjamin Lang :
> We had another problem with sql server and prepared statements, where
> it took forever to execute a statement.
>
> Looking at the example from the docs:
>
> create.select().from(BOOK).where(BOOK.ID.equal(5)).and(BOOK.TITLE.
> equal("Animal Farm")).fetch();
>
> Is there a way to tell jooq, that for example BOOK.ID is not a
> prepared statement param ? The opposite of val() ...
>
> Because of this problem, we switched completely to STATIC_STATEMENTS
> and everything works fast (one statement down from several minutes to 3
> sec).
> Is there a benefit with prepared statements anyway, if you send the
> same basically static sql query (without any params) over and over again
> ... ?
>
> But then we have a problem with the Loader, the source code:
>
> getContext()
> .loadInto(table)
> .batchAll()
> .onDuplicateKeyUpdate()
> .loadRecords(records)
> .fields(fields)
> .execute();
>
> Doesn't work anymore, because it doesn't fill the queries correctly
> with the records anymore.
> If I remove the onDuplicateKeyUpdate() it works for inserts as
> expected.
>
> Am I doing it wrong or is there
Re: Multi row insert with On Duplicate Update (Microsoft Sql Server)
Thank you for the reply, Lukas!
I've posted a question on StackOverflow with concrete examples and
explanation:
https://stackoverflow.com/questions/49110621/batch-insert-onduplcatekeyignore-jooq
Thanks in advance,
Victoria
понедельник, 5 марта 2018 г., 12:59:39 UTC+3 пользователь Lukas Eder
написал:
>
> Hi Victoria,
>
> Thank you very much for your message.
>
> I'm not sure yet if this is really related to the original discussion - if
> in doubt, you can always start a new discussion on the user group.
>
> In order to be able to help you, I will need quite a bit of additional
> information, ideally a minimal, complete, verifiable example. See this
> website here for some details:
> https://stackoverflow.com/help/mcve
>
> You've mentioned the loader API and batch, and that you've run into some
> errors. But how can I reproduce those errors? What does your class /
> composite key look like? What does your schema look like? What does your
> jOOQ code look like?
>
> The more information you provide, the easier it will be to help.
>
> Thanks,
> Lukas
>
> 2018-03-02 17:42 GMT+01:00 >:
>
>> Dear Lucas!
>>
>> I've faced such issue with the loader api: I have a class that contains
>> only id values. This class is actually a composite primary key itself. I
>> want to make batch insert for this class using loader api. But if I use
>> onDuplicateKeyUpdate - I receive an error like there is no fields to
>> update(truth, there is only part of primary key). If I use
>> onDuplicateKeyIgnore - I receive an error Cannot apply batch loading with
>> onDuplicateKeyIgnore flag. If I use nothing - sometimes I face duplicate
>> key violation exception. Can you advice me any approach to deal with this
>> case? I will really appreciate any help.
>>
>> The point is that I want to make
>> insertInto(TABLE).set(record).onDuplicateKeyUpdate().set(record).execute()
>> in
>> batch
>>
>> Thanks in advance,
>>Victoria
>>
>> вторник, 16 августа 2016 г., 0:03:56 UTC+3 пользователь Lukas Eder
>> написал:
>>>
>>> Hi Benjamin,
>>>
>>> I'm sorry for the delay. Yes, there is a way to tell jOOQ that.
>>> Field.equal(T) is just convenience for Field.equal(val(T)), but you can
>>> also pass an "inline" bind variable by using DSL.inline(T):
>>>
>>> http://www.jooq.org/javadoc/latest/org/jooq/impl/DSL.html#inline-java.lang.Integer-
>>>
>>> This helps having fine-grained control over which bind variable gets to
>>> be inlined and which variable gets to be passed as a "?". The other option,
>>> as you've already discovered, is to force all bind variables to be inlined
>>> via STATIC_STATEMENTS. That flag also executes a java.sql.Statement, rather
>>> than a java.sql.PreparedStatement
>>>
>>> Both options are documented here, for the reference:
>>>
>>> http://www.jooq.org/doc/latest/manual/sql-building/bind-values/inlined-parameters
>>>
>>> I'm curious about what takes so long when you use bind variables. Have
>>> you profiled your SQL usage? How many bind variables do you have, and how
>>> big are they? Do your actual queries look very different from the example
>>> that you've posted?
>>>
>>> Thanks for the loader hint. I'll investigate that. Quite possibly,
>>> there's a bug that prevents using that combination of flags. Note, though,
>>> you don't have to use STATIC_STATEMENTS for everything. You can use it only
>>> for those few queries that run slowly.
>>>
>>> Hope this helps,
>>> Lukas
>>>
>>> 2016-08-11 12:17 GMT+02:00 Benjamin Lang :
>>>
We had another problem with sql server and prepared statements, where
it took forever to execute a statement.
Looking at the example from the docs:
create.select().from(BOOK).where(BOOK.ID.equal(5)).and(BOOK.TITLE.equal
("Animal Farm")).fetch();
Is there a way to tell jooq, that for example BOOK.ID is not a
prepared statement param ? The opposite of val() ...
Because of this problem, we switched completely to STATIC_STATEMENTS
and everything works fast (one statement down from several minutes to 3
sec).
Is there a benefit with prepared statements anyway, if you send the
same basically static sql query (without any params) over and over again
... ?
But then we have a problem with the Loader, the source code:
getContext()
.loadInto(table)
.batchAll()
.onDuplicateKeyUpdate()
.loadRecords(records)
.fields(fields)
.execute();
Doesn't work anymore, because it doesn't fill the queries correctly
with the records anymore.
If I remove the onDuplicateKeyUpdate() it works for inserts as expected.
Am I doing it wrong or is there maybe an error in connection with
STATIC - Loader - OnDuplicatedKey - SQL Server ?
Best Regards
-- Benjamin
Am Dienstag, 5. Juli 2016 22:43:41 UTC+2 schrieb Benjamin Lang:
>
> Hello together,
Re: Multi row insert with On Duplicate Update (Microsoft Sql Server)
Hi Victoria,
Thank you very much for your message.
I'm not sure yet if this is really related to the original discussion - if
in doubt, you can always start a new discussion on the user group.
In order to be able to help you, I will need quite a bit of additional
information, ideally a minimal, complete, verifiable example. See this
website here for some details:
https://stackoverflow.com/help/mcve
You've mentioned the loader API and batch, and that you've run into some
errors. But how can I reproduce those errors? What does your class /
composite key look like? What does your schema look like? What does your
jOOQ code look like?
The more information you provide, the easier it will be to help.
Thanks,
Lukas
2018-03-02 17:42 GMT+01:00 :
> Dear Lucas!
>
> I've faced such issue with the loader api: I have a class that contains
> only id values. This class is actually a composite primary key itself. I
> want to make batch insert for this class using loader api. But if I use
> onDuplicateKeyUpdate - I receive an error like there is no fields to
> update(truth, there is only part of primary key). If I use
> onDuplicateKeyIgnore - I receive an error Cannot apply batch loading with
> onDuplicateKeyIgnore flag. If I use nothing - sometimes I face duplicate
> key violation exception. Can you advice me any approach to deal with this
> case? I will really appreciate any help.
>
> The point is that I want to make insertInto(TABLE).set(
> record).onDuplicateKeyUpdate().set(record).execute() in batch
>
> Thanks in advance,
>Victoria
>
> вторник, 16 августа 2016 г., 0:03:56 UTC+3 пользователь Lukas Eder написал:
>>
>> Hi Benjamin,
>>
>> I'm sorry for the delay. Yes, there is a way to tell jOOQ that.
>> Field.equal(T) is just convenience for Field.equal(val(T)), but you can
>> also pass an "inline" bind variable by using DSL.inline(T):
>> http://www.jooq.org/javadoc/latest/org/jooq/impl/DSL.html#in
>> line-java.lang.Integer-
>>
>> This helps having fine-grained control over which bind variable gets to
>> be inlined and which variable gets to be passed as a "?". The other option,
>> as you've already discovered, is to force all bind variables to be inlined
>> via STATIC_STATEMENTS. That flag also executes a java.sql.Statement, rather
>> than a java.sql.PreparedStatement
>>
>> Both options are documented here, for the reference:
>> http://www.jooq.org/doc/latest/manual/sql-building/bind-
>> values/inlined-parameters
>>
>> I'm curious about what takes so long when you use bind variables. Have
>> you profiled your SQL usage? How many bind variables do you have, and how
>> big are they? Do your actual queries look very different from the example
>> that you've posted?
>>
>> Thanks for the loader hint. I'll investigate that. Quite possibly,
>> there's a bug that prevents using that combination of flags. Note, though,
>> you don't have to use STATIC_STATEMENTS for everything. You can use it only
>> for those few queries that run slowly.
>>
>> Hope this helps,
>> Lukas
>>
>> 2016-08-11 12:17 GMT+02:00 Benjamin Lang :
>>
>>> We had another problem with sql server and prepared statements, where it
>>> took forever to execute a statement.
>>>
>>> Looking at the example from the docs:
>>>
>>> create.select().from(BOOK).where(BOOK.ID.equal(5)).and(BOOK.TITLE.equal("Animal
>>> Farm")).fetch();
>>>
>>> Is there a way to tell jooq, that for example BOOK.ID is not a prepared
>>> statement param ? The opposite of val() ...
>>>
>>> Because of this problem, we switched completely to STATIC_STATEMENTS and
>>> everything works fast (one statement down from several minutes to 3 sec).
>>> Is there a benefit with prepared statements anyway, if you send the same
>>> basically static sql query (without any params) over and over again ... ?
>>>
>>> But then we have a problem with the Loader, the source code:
>>>
>>> getContext()
>>> .loadInto(table)
>>> .batchAll()
>>> .onDuplicateKeyUpdate()
>>> .loadRecords(records)
>>> .fields(fields)
>>> .execute();
>>>
>>> Doesn't work anymore, because it doesn't fill the queries correctly with
>>> the records anymore.
>>> If I remove the onDuplicateKeyUpdate() it works for inserts as expected.
>>>
>>> Am I doing it wrong or is there maybe an error in connection with STATIC
>>> - Loader - OnDuplicatedKey - SQL Server ?
>>>
>>> Best Regards
>>> -- Benjamin
>>>
>>>
>>> Am Dienstag, 5. Juli 2016 22:43:41 UTC+2 schrieb Benjamin Lang:
Hello together,
we are currently evaluating Jooq in a prototype context in order
to find out if we can use it later in production. So far it seems
pretty nice, with an almost perfect documentation.
We need to support different PostgreSql and Sql Server types.
One problem we could not yet solve, is the following:
We want to insert a batch of 1000 entries into a table. If
some entries already exist, we want to update some of the fields
instead.
Re: Multi row insert with On Duplicate Update (Microsoft Sql Server)
Dear Lucas!
I've faced such issue with the loader api: I have a class that contains
only id values. This class is actually a composite primary key itself. I
want to make batch insert for this class using loader api. But if I use
onDuplicateKeyUpdate - I receive an error like there is no fields to
update(truth, there is only part of primary key). If I use
onDuplicateKeyIgnore - I receive an error Cannot apply batch loading with
onDuplicateKeyIgnore flag. If I use nothing - sometimes I face duplicate
key violation exception. Can you advice me any approach to deal with this
case? I will really appreciate any help.
The point is that I want to make
insertInto(TABLE).set(record).onDuplicateKeyUpdate().set(record).execute() in
batch
Thanks in advance,
Victoria
вторник, 16 августа 2016 г., 0:03:56 UTC+3 пользователь Lukas Eder написал:
>
> Hi Benjamin,
>
> I'm sorry for the delay. Yes, there is a way to tell jOOQ that.
> Field.equal(T) is just convenience for Field.equal(val(T)), but you can
> also pass an "inline" bind variable by using DSL.inline(T):
>
> http://www.jooq.org/javadoc/latest/org/jooq/impl/DSL.html#inline-java.lang.Integer-
>
> This helps having fine-grained control over which bind variable gets to be
> inlined and which variable gets to be passed as a "?". The other option, as
> you've already discovered, is to force all bind variables to be inlined via
> STATIC_STATEMENTS. That flag also executes a java.sql.Statement, rather
> than a java.sql.PreparedStatement
>
> Both options are documented here, for the reference:
>
> http://www.jooq.org/doc/latest/manual/sql-building/bind-values/inlined-parameters
>
> I'm curious about what takes so long when you use bind variables. Have you
> profiled your SQL usage? How many bind variables do you have, and how big
> are they? Do your actual queries look very different from the example that
> you've posted?
>
> Thanks for the loader hint. I'll investigate that. Quite possibly, there's
> a bug that prevents using that combination of flags. Note, though, you
> don't have to use STATIC_STATEMENTS for everything. You can use it only for
> those few queries that run slowly.
>
> Hope this helps,
> Lukas
>
> 2016-08-11 12:17 GMT+02:00 Benjamin Lang >:
>
>> We had another problem with sql server and prepared statements, where it
>> took forever to execute a statement.
>>
>> Looking at the example from the docs:
>>
>> create.select().from(BOOK).where(BOOK.ID.equal(5)).and(BOOK.TITLE.equal("Animal
>>
>> Farm")).fetch();
>>
>> Is there a way to tell jooq, that for example BOOK.ID is not a prepared
>> statement param ? The opposite of val() ...
>>
>> Because of this problem, we switched completely to STATIC_STATEMENTS and
>> everything works fast (one statement down from several minutes to 3 sec).
>> Is there a benefit with prepared statements anyway, if you send the same
>> basically static sql query (without any params) over and over again ... ?
>>
>> But then we have a problem with the Loader, the source code:
>>
>> getContext()
>> .loadInto(table)
>> .batchAll()
>> .onDuplicateKeyUpdate()
>> .loadRecords(records)
>> .fields(fields)
>> .execute();
>>
>> Doesn't work anymore, because it doesn't fill the queries correctly with
>> the records anymore.
>> If I remove the onDuplicateKeyUpdate() it works for inserts as expected.
>>
>> Am I doing it wrong or is there maybe an error in connection with STATIC
>> - Loader - OnDuplicatedKey - SQL Server ?
>>
>> Best Regards
>> -- Benjamin
>>
>>
>> Am Dienstag, 5. Juli 2016 22:43:41 UTC+2 schrieb Benjamin Lang:
>>>
>>> Hello together,
>>>
>>> we are currently evaluating Jooq in a prototype context in order
>>> to find out if we can use it later in production. So far it seems
>>> pretty nice, with an almost perfect documentation.
>>>
>>> We need to support different PostgreSql and Sql Server types.
>>>
>>> One problem we could not yet solve, is the following:
>>>
>>> We want to insert a batch of 1000 entries into a table. If
>>> some entries already exist, we want to update some of the fields instead.
>>>
>>> We used:
>>>
>>> InsertValuesStep insertStep = context.insertInto(Table, SomeFields);
>>> List records = SomeInitialzedRecords();
>>>
>>> records.stream().foreach(record -> {
>>> insertStep.values(record.getField1(), record.getField2()...);
>>> insertStep.onDuplicateKeyUpdate().set(Field1, record.getField1());
>>> }
>>>
>>> insertStep.execute();
>>>
>>> This works for PostgresSql as expected, but in sql server it does insert
>>> only one row per execution instead of 1000.
>>> We also tried:
>>>
>>> context.batchStore(records).execute();
>>>
>>> But this throws the DuplicateKey Exception instead of updating the row
>>> entry and it seems somewhat slower than the other option under PostgreSql.
>>>
>>> Is this the right way to do what we like to achieve or is there
>>> something better ?
>>>
>>> Thanks in advance - Benjamin
>>>
>
Re: Multi row insert with On Duplicate Update (Microsoft Sql Server)
Hi Benjamin,
I'm sorry for the delay. Yes, there is a way to tell jOOQ that.
Field.equal(T) is just convenience for Field.equal(val(T)), but you can
also pass an "inline" bind variable by using DSL.inline(T):
http://www.jooq.org/javadoc/latest/org/jooq/impl/DSL.html#inline-java.lang.Integer-
This helps having fine-grained control over which bind variable gets to be
inlined and which variable gets to be passed as a "?". The other option, as
you've already discovered, is to force all bind variables to be inlined via
STATIC_STATEMENTS. That flag also executes a java.sql.Statement, rather
than a java.sql.PreparedStatement
Both options are documented here, for the reference:
http://www.jooq.org/doc/latest/manual/sql-building/bind-values/inlined-parameters
I'm curious about what takes so long when you use bind variables. Have you
profiled your SQL usage? How many bind variables do you have, and how big
are they? Do your actual queries look very different from the example that
you've posted?
Thanks for the loader hint. I'll investigate that. Quite possibly, there's
a bug that prevents using that combination of flags. Note, though, you
don't have to use STATIC_STATEMENTS for everything. You can use it only for
those few queries that run slowly.
Hope this helps,
Lukas
2016-08-11 12:17 GMT+02:00 Benjamin Lang :
> We had another problem with sql server and prepared statements, where it
> took forever to execute a statement.
>
> Looking at the example from the docs:
>
> create.select().from(BOOK).where(BOOK.ID.equal(5)).and(BOOK.TITLE.equal("Animal
> Farm")).fetch();
>
> Is there a way to tell jooq, that for example BOOK.ID is not a prepared
> statement param ? The opposite of val() ...
>
> Because of this problem, we switched completely to STATIC_STATEMENTS and
> everything works fast (one statement down from several minutes to 3 sec).
> Is there a benefit with prepared statements anyway, if you send the same
> basically static sql query (without any params) over and over again ... ?
>
> But then we have a problem with the Loader, the source code:
>
> getContext()
> .loadInto(table)
> .batchAll()
> .onDuplicateKeyUpdate()
> .loadRecords(records)
> .fields(fields)
> .execute();
>
> Doesn't work anymore, because it doesn't fill the queries correctly with
> the records anymore.
> If I remove the onDuplicateKeyUpdate() it works for inserts as expected.
>
> Am I doing it wrong or is there maybe an error in connection with STATIC -
> Loader - OnDuplicatedKey - SQL Server ?
>
> Best Regards
> -- Benjamin
>
>
> Am Dienstag, 5. Juli 2016 22:43:41 UTC+2 schrieb Benjamin Lang:
>>
>> Hello together,
>>
>> we are currently evaluating Jooq in a prototype context in order
>> to find out if we can use it later in production. So far it seems
>> pretty nice, with an almost perfect documentation.
>>
>> We need to support different PostgreSql and Sql Server types.
>>
>> One problem we could not yet solve, is the following:
>>
>> We want to insert a batch of 1000 entries into a table. If
>> some entries already exist, we want to update some of the fields instead.
>>
>> We used:
>>
>> InsertValuesStep insertStep = context.insertInto(Table, SomeFields);
>> List records = SomeInitialzedRecords();
>>
>> records.stream().foreach(record -> {
>> insertStep.values(record.getField1(), record.getField2()...);
>> insertStep.onDuplicateKeyUpdate().set(Field1, record.getField1());
>> }
>>
>> insertStep.execute();
>>
>> This works for PostgresSql as expected, but in sql server it does insert
>> only one row per execution instead of 1000.
>> We also tried:
>>
>> context.batchStore(records).execute();
>>
>> But this throws the DuplicateKey Exception instead of updating the row
>> entry and it seems somewhat slower than the other option under PostgreSql.
>>
>> Is this the right way to do what we like to achieve or is there something
>> better ?
>>
>> Thanks in advance - Benjamin
>>
>> --
> You received this message because you are subscribed to the Google Groups
> "jOOQ User Group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to jooq-user+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to jooq-user+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: Multi row insert with On Duplicate Update (Microsoft Sql Server)
We had another problem with sql server and prepared statements, where it
took forever to execute a statement.
Looking at the example from the docs:
create.select().from(BOOK).where(BOOK.ID.equal(5)).and(BOOK.TITLE.equal("Animal
Farm")).fetch();
Is there a way to tell jooq, that for example BOOK.ID is not a prepared
statement param ? The opposite of val() ...
Because of this problem, we switched completely to STATIC_STATEMENTS and
everything works fast (one statement down from several minutes to 3 sec).
Is there a benefit with prepared statements anyway, if you send the same
basically static sql query (without any params) over and over again ... ?
But then we have a problem with the Loader, the source code:
getContext()
.loadInto(table)
.batchAll()
.onDuplicateKeyUpdate()
.loadRecords(records)
.fields(fields)
.execute();
Doesn't work anymore, because it doesn't fill the queries correctly with
the records anymore.
If I remove the onDuplicateKeyUpdate() it works for inserts as expected.
Am I doing it wrong or is there maybe an error in connection with STATIC -
Loader - OnDuplicatedKey - SQL Server ?
Best Regards
-- Benjamin
Am Dienstag, 5. Juli 2016 22:43:41 UTC+2 schrieb Benjamin Lang:
>
> Hello together,
>
> we are currently evaluating Jooq in a prototype context in order
> to find out if we can use it later in production. So far it seems
> pretty nice, with an almost perfect documentation.
>
> We need to support different PostgreSql and Sql Server types.
>
> One problem we could not yet solve, is the following:
>
> We want to insert a batch of 1000 entries into a table. If
> some entries already exist, we want to update some of the fields instead.
>
> We used:
>
> InsertValuesStep insertStep = context.insertInto(Table, SomeFields);
> List records = SomeInitialzedRecords();
>
> records.stream().foreach(record -> {
> insertStep.values(record.getField1(), record.getField2()...);
> insertStep.onDuplicateKeyUpdate().set(Field1, record.getField1());
> }
>
> insertStep.execute();
>
> This works for PostgresSql as expected, but in sql server it does insert
> only one row per execution instead of 1000.
> We also tried:
>
> context.batchStore(records).execute();
>
> But this throws the DuplicateKey Exception instead of updating the row
> entry and it seems somewhat slower than the other option under PostgreSql.
>
> Is this the right way to do what we like to achieve or is there something
> better ?
>
> Thanks in advance - Benjamin
>
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to jooq-user+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: Multi row insert with On Duplicate Update (Microsoft Sql Server)
Great to hear, Benjamin!
Thanks for the feedback
2016-07-12 14:38 GMT+02:00 Benjamin Lang :
> Thanks Lukas,
>
> we changed the implementation to:
>
>getContext()
> .loadInto(table)
> .batchAll()
> .onDuplicateKeyUpdate()
> .loadRecords(records)
> .fields(fields)
> .execute();
>
>
> It seems to work so far for PostgresSql and for SqlServer.
>
> Best Regards
> -- Benjamin
>
> Am Mittwoch, 6. Juli 2016 15:44:15 UTC+2 schrieb Lukas Eder:
>>
>> Hi Benjamin,
>>
>> Thank you very much for your enquiry and for your interest in jOOQ. I
>> will comment directly inline
>>
>> 2016-07-05 16:25 GMT+02:00 :
>>
>>> Hello together,
>>>
>>> we are currently evaluating Jooq in a prototype context in order
>>> to find out if we can use it later in production. So far it seems
>>> pretty nice, with an almost perfect documentation.
>>>
>>> We need to support different PostgreSql and Sql Server types.
>>>
>>> One problem we could not yet solve, is the following:
>>>
>>> We want to insert a batch of 1000 entries into a table. If
>>> some entries already exist, we want to update some of the fields instead.
>>>
>>> We used:
>>>
>>> InsertValuesStep insertStep = context.insertInto(Table, SomeFields);
>>> List records = SomeInitialzedRecords();
>>>
>>> records.stream().foreach(record -> {
>>> insertStep.values(record.getField1(), record.getField2()...);
>>> insertStep.onDuplicateKeyUpdate().set(Field1, record.getField1());
>>> }
>>>
>>> insertStep.execute();
>>>
>>> This works for PostgresSql as expected, but in sql server it does insert
>>> only one row per execution instead of 1000.
>>>
>>
>> Hmm, this currently works because the jOOQ API is not (yet) immutable, so
>> you can keep calling methods on your insertStep to modify its internal
>> model, even if there isn't really any database that supports adding several
>> VALUES() clauses and ON DUPLICATE KEY UPDATE clauses. I would strongly
>> recommend not following such a pattern. I'm actually surprised that this
>> would work for PostgreSQL.
>>
>>
>>> We also tried:
>>>
>>> context.batchStore(records).execute();
>>>
>>> But this throws the DuplicateKey Exception instead of updating the row
>>> entry and it seems somewhat slower than the other option under PostgreSql.
>>>
>>
>> With this approach, the decision whether an INSERT or an UPDATE is
>> executed is made in the client based on how you created the individual
>> records. If you never fetched any record from the database, this will only
>> produce INSERT statements - just like when you call record.store();
>>
>> The reason why it might be slower is because each record currently
>> generates an individual SQL string.
>>
>> Is this the right way to do what we like to achieve or is there something
>>> better ?
>>>
>>
>> I think you might prefer the Loader API, which works with CSV data:
>>
>> http://www.jooq.org/doc/latest/manual/sql-execution/importing/importing-csv
>>
>> ... or also with records, as in your case:
>>
>> http://www.jooq.org/doc/latest/manual/sql-execution/importing/importing-records
>>
>> The Loader API lets you fine tune the transaction commit size, batch size
>> and bulk size, as you might want to use different sizes for each database.
>>
>> Let me know if this helps, and if you have any additional questions about
>> the loader API.
>>
>> Best Regards,
>> Lukas
>>
> --
> You received this message because you are subscribed to the Google Groups
> "jOOQ User Group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to jooq-user+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to jooq-user+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: Multi row insert with On Duplicate Update (Microsoft Sql Server)
Thanks Lukas,
we changed the implementation to:
getContext()
.loadInto(table)
.batchAll()
.onDuplicateKeyUpdate()
.loadRecords(records)
.fields(fields)
.execute();
It seems to work so far for PostgresSql and for SqlServer.
Best Regards
-- Benjamin
Am Mittwoch, 6. Juli 2016 15:44:15 UTC+2 schrieb Lukas Eder:
>
> Hi Benjamin,
>
> Thank you very much for your enquiry and for your interest in jOOQ. I will
> comment directly inline
>
> 2016-07-05 16:25 GMT+02:00 >:
>
>> Hello together,
>>
>> we are currently evaluating Jooq in a prototype context in order
>> to find out if we can use it later in production. So far it seems
>> pretty nice, with an almost perfect documentation.
>>
>> We need to support different PostgreSql and Sql Server types.
>>
>> One problem we could not yet solve, is the following:
>>
>> We want to insert a batch of 1000 entries into a table. If
>> some entries already exist, we want to update some of the fields instead.
>>
>> We used:
>>
>> InsertValuesStep insertStep = context.insertInto(Table, SomeFields);
>> List records = SomeInitialzedRecords();
>>
>> records.stream().foreach(record -> {
>> insertStep.values(record.getField1(), record.getField2()...);
>> insertStep.onDuplicateKeyUpdate().set(Field1, record.getField1());
>> }
>>
>> insertStep.execute();
>>
>> This works for PostgresSql as expected, but in sql server it does insert
>> only one row per execution instead of 1000.
>>
>
> Hmm, this currently works because the jOOQ API is not (yet) immutable, so
> you can keep calling methods on your insertStep to modify its internal
> model, even if there isn't really any database that supports adding several
> VALUES() clauses and ON DUPLICATE KEY UPDATE clauses. I would strongly
> recommend not following such a pattern. I'm actually surprised that this
> would work for PostgreSQL.
>
>
>> We also tried:
>>
>> context.batchStore(records).execute();
>>
>> But this throws the DuplicateKey Exception instead of updating the row
>> entry and it seems somewhat slower than the other option under PostgreSql.
>>
>
> With this approach, the decision whether an INSERT or an UPDATE is
> executed is made in the client based on how you created the individual
> records. If you never fetched any record from the database, this will only
> produce INSERT statements - just like when you call record.store();
>
> The reason why it might be slower is because each record currently
> generates an individual SQL string.
>
> Is this the right way to do what we like to achieve or is there something
>> better ?
>>
>
> I think you might prefer the Loader API, which works with CSV data:
> http://www.jooq.org/doc/latest/manual/sql-execution/importing/importing-csv
>
> ... or also with records, as in your case:
>
> http://www.jooq.org/doc/latest/manual/sql-execution/importing/importing-records
>
> The Loader API lets you fine tune the transaction commit size, batch size
> and bulk size, as you might want to use different sizes for each database.
>
> Let me know if this helps, and if you have any additional questions about
> the loader API.
>
> Best Regards,
> Lukas
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to jooq-user+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: Multi row insert with On Duplicate Update (Microsoft Sql Server)
Hi Benjamin,
Thank you very much for your enquiry and for your interest in jOOQ. I will
comment directly inline
2016-07-05 16:25 GMT+02:00 :
> Hello together,
>
> we are currently evaluating Jooq in a prototype context in order
> to find out if we can use it later in production. So far it seems
> pretty nice, with an almost perfect documentation.
>
> We need to support different PostgreSql and Sql Server types.
>
> One problem we could not yet solve, is the following:
>
> We want to insert a batch of 1000 entries into a table. If
> some entries already exist, we want to update some of the fields instead.
>
> We used:
>
> InsertValuesStep insertStep = context.insertInto(Table, SomeFields);
> List records = SomeInitialzedRecords();
>
> records.stream().foreach(record -> {
> insertStep.values(record.getField1(), record.getField2()...);
> insertStep.onDuplicateKeyUpdate().set(Field1, record.getField1());
> }
>
> insertStep.execute();
>
> This works for PostgresSql as expected, but in sql server it does insert
> only one row per execution instead of 1000.
>
Hmm, this currently works because the jOOQ API is not (yet) immutable, so
you can keep calling methods on your insertStep to modify its internal
model, even if there isn't really any database that supports adding several
VALUES() clauses and ON DUPLICATE KEY UPDATE clauses. I would strongly
recommend not following such a pattern. I'm actually surprised that this
would work for PostgreSQL.
> We also tried:
>
> context.batchStore(records).execute();
>
> But this throws the DuplicateKey Exception instead of updating the row
> entry and it seems somewhat slower than the other option under PostgreSql.
>
With this approach, the decision whether an INSERT or an UPDATE is executed
is made in the client based on how you created the individual records. If
you never fetched any record from the database, this will only produce
INSERT statements - just like when you call record.store();
The reason why it might be slower is because each record currently
generates an individual SQL string.
Is this the right way to do what we like to achieve or is there something
> better ?
>
I think you might prefer the Loader API, which works with CSV data:
http://www.jooq.org/doc/latest/manual/sql-execution/importing/importing-csv
... or also with records, as in your case:
http://www.jooq.org/doc/latest/manual/sql-execution/importing/importing-records
The Loader API lets you fine tune the transaction commit size, batch size
and bulk size, as you might want to use different sizes for each database.
Let me know if this helps, and if you have any additional questions about
the loader API.
Best Regards,
Lukas
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to jooq-user+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
