You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@groovy.apache.org by Daniel Price <da...@gmail.com> on 2015/11/30 16:33:53 UTC
Batch Insert with Sequence?
Hi all. I've been using Groovy to batch insert data into SQL Server, and
it works very well. The syntax I've been using is:
//batch insert
int dbThrottle = 25000
db.withTransaction{
def result = db.withBatch(dbThrottle, insertString){ ps ->
output.each{
ps.addBatch(it)
}
}
}
This code is used to insert data from list 'output' into my Sql Server DB.
The 'insertString' is just the typical insert statement dynamically derived
from the target table and columns.
I now have a need to use a sequence generator to populate one or more
columns in some tables. I can do this by putting sequence numbers in a
list and inserting such sequence lists into my 'output' data list, but this
is very slow. Is there a way I can include the 'NEXT VALUE FOR' sequence
query in the batch insert query so that it is evaluated during batch
insert? Will this be any faster?
Thanks!
D
Re: Batch Insert with Sequence?
Posted by David Durham <da...@gmail.com>.
OK, so if you're not using the database sequence, then you don't need
the NEXT VALUE FOR function.
On Tue, Dec 1, 2015 at 12:09 PM, Daniel Price <da...@gmail.com> wrote:
> Not in my use case. I basically extract a whole table of data into a list,
> perform some manipulations/transformations/translations, then batch insert
> that list into a DB. What I've been doing works well and is fast--up to
> this point since I've just started using sequence values. I'm converting
> the data in one DB into another DB and I'm trying to use sequence values as
> unique keys (I need several per table) to form linking relationships that
> don't exist in the client DB but are expected by the destination DB--the DBs
> have very different structures. The alternative, and what I've done to this
> point, is to use DB views and intermediate tables to put the source DB into
> the same structure as the destination DB, but that takes a long time and
> none of the effort can be reused.
>
> On Tue, Dec 1, 2015 at 12:25 PM, David Durham <da...@gmail.com>
> wrote:
>>
>> What list of values? The sequence values are from the database
>> itself, and are not fed into the batch by your Groovy code.
>>
>> On Tue, Dec 1, 2015 at 7:13 AM, Daniel Price <da...@gmail.com>
>> wrote:
>> > Thanks, David! I read many MSDN pages but didn't encounter that exact
>> > syntax. I'll give it a try and report back. Since I'm using this
>> > insert
>> > statement with withBatch(), I wonder how I should handle the list of
>> > values
>> > that are being fed into it...
>> >
>> > On Mon, Nov 30, 2015 at 11:41 PM, David Durham
>> > <da...@gmail.com>
>> > wrote:
>> >>
>> >> Ah, no, it looks like it's example E. from this page:
>> >>
>> >> https://msdn.microsoft.com/en-us/library/ff878370.aspx
>> >>
>> >> INSERT Test.TestTable (CounterColumn,Name)
>> >> VALUES (NEXT VALUE FOR Test.CountBy1, 'Syed') ;
>> >>
>> >> On Mon, Nov 30, 2015 at 10:38 PM, David Durham
>> >> <da...@gmail.com> wrote:
>> >> > I think your insertString will have something like "insert into
>> >> > myTable (...) values (mysequence.nextval, ?,?,?)"
>> >> >
>> >> > On Mon, Nov 30, 2015 at 9:33 AM, Daniel Price <da...@gmail.com>
>> >> > wrote:
>> >> >> Hi all. I've been using Groovy to batch insert data into SQL
>> >> >> Server,
>> >> >> and it
>> >> >> works very well. The syntax I've been using is:
>> >> >>
>> >> >> //batch insert
>> >> >> int dbThrottle = 25000
>> >> >> db.withTransaction{
>> >> >> def result = db.withBatch(dbThrottle, insertString){ ps ->
>> >> >> output.each{
>> >> >> ps.addBatch(it)
>> >> >> }
>> >> >> }
>> >> >> }
>> >> >>
>> >> >> This code is used to insert data from list 'output' into my Sql
>> >> >> Server
>> >> >> DB.
>> >> >> The 'insertString' is just the typical insert statement dynamically
>> >> >> derived
>> >> >> from the target table and columns.
>> >> >>
>> >> >> I now have a need to use a sequence generator to populate one or
>> >> >> more
>> >> >> columns in some tables. I can do this by putting sequence numbers
>> >> >> in a
>> >> >> list
>> >> >> and inserting such sequence lists into my 'output' data list, but
>> >> >> this
>> >> >> is
>> >> >> very slow. Is there a way I can include the 'NEXT VALUE FOR'
>> >> >> sequence
>> >> >> query
>> >> >> in the batch insert query so that it is evaluated during batch
>> >> >> insert?
>> >> >> Will
>> >> >> this be any faster?
>> >> >>
>> >> >> Thanks!
>> >> >> D
>> >
>> >
>
>
Re: Batch Insert with Sequence?
Posted by Daniel Price <da...@gmail.com>.
Not in my use case. I basically extract a whole table of data into a list,
perform some manipulations/transformations/translations, then batch insert
that list into a DB. What I've been doing works well and is fast--up to
this point since I've just started using sequence values. I'm converting
the data in one DB into another DB and I'm trying to use sequence values as
unique keys (I need several per table) to form linking relationships that
don't exist in the client DB but are expected by the destination DB--the
DBs have very different structures. The alternative, and what I've done to
this point, is to use DB views and intermediate tables to put the source DB
into the same structure as the destination DB, but that takes a long time
and none of the effort can be reused.
On Tue, Dec 1, 2015 at 12:25 PM, David Durham <da...@gmail.com>
wrote:
> What list of values? The sequence values are from the database
> itself, and are not fed into the batch by your Groovy code.
>
> On Tue, Dec 1, 2015 at 7:13 AM, Daniel Price <da...@gmail.com>
> wrote:
> > Thanks, David! I read many MSDN pages but didn't encounter that exact
> > syntax. I'll give it a try and report back. Since I'm using this insert
> > statement with withBatch(), I wonder how I should handle the list of
> values
> > that are being fed into it...
> >
> > On Mon, Nov 30, 2015 at 11:41 PM, David Durham <
> david.durham.jr@gmail.com>
> > wrote:
> >>
> >> Ah, no, it looks like it's example E. from this page:
> >>
> >> https://msdn.microsoft.com/en-us/library/ff878370.aspx
> >>
> >> INSERT Test.TestTable (CounterColumn,Name)
> >> VALUES (NEXT VALUE FOR Test.CountBy1, 'Syed') ;
> >>
> >> On Mon, Nov 30, 2015 at 10:38 PM, David Durham
> >> <da...@gmail.com> wrote:
> >> > I think your insertString will have something like "insert into
> >> > myTable (...) values (mysequence.nextval, ?,?,?)"
> >> >
> >> > On Mon, Nov 30, 2015 at 9:33 AM, Daniel Price <da...@gmail.com>
> >> > wrote:
> >> >> Hi all. I've been using Groovy to batch insert data into SQL Server,
> >> >> and it
> >> >> works very well. The syntax I've been using is:
> >> >>
> >> >> //batch insert
> >> >> int dbThrottle = 25000
> >> >> db.withTransaction{
> >> >> def result = db.withBatch(dbThrottle, insertString){ ps ->
> >> >> output.each{
> >> >> ps.addBatch(it)
> >> >> }
> >> >> }
> >> >> }
> >> >>
> >> >> This code is used to insert data from list 'output' into my Sql
> Server
> >> >> DB.
> >> >> The 'insertString' is just the typical insert statement dynamically
> >> >> derived
> >> >> from the target table and columns.
> >> >>
> >> >> I now have a need to use a sequence generator to populate one or more
> >> >> columns in some tables. I can do this by putting sequence numbers
> in a
> >> >> list
> >> >> and inserting such sequence lists into my 'output' data list, but
> this
> >> >> is
> >> >> very slow. Is there a way I can include the 'NEXT VALUE FOR'
> sequence
> >> >> query
> >> >> in the batch insert query so that it is evaluated during batch
> insert?
> >> >> Will
> >> >> this be any faster?
> >> >>
> >> >> Thanks!
> >> >> D
> >
> >
>
Re: Batch Insert with Sequence?
Posted by David Durham <da...@gmail.com>.
What list of values? The sequence values are from the database
itself, and are not fed into the batch by your Groovy code.
On Tue, Dec 1, 2015 at 7:13 AM, Daniel Price <da...@gmail.com> wrote:
> Thanks, David! I read many MSDN pages but didn't encounter that exact
> syntax. I'll give it a try and report back. Since I'm using this insert
> statement with withBatch(), I wonder how I should handle the list of values
> that are being fed into it...
>
> On Mon, Nov 30, 2015 at 11:41 PM, David Durham <da...@gmail.com>
> wrote:
>>
>> Ah, no, it looks like it's example E. from this page:
>>
>> https://msdn.microsoft.com/en-us/library/ff878370.aspx
>>
>> INSERT Test.TestTable (CounterColumn,Name)
>> VALUES (NEXT VALUE FOR Test.CountBy1, 'Syed') ;
>>
>> On Mon, Nov 30, 2015 at 10:38 PM, David Durham
>> <da...@gmail.com> wrote:
>> > I think your insertString will have something like "insert into
>> > myTable (...) values (mysequence.nextval, ?,?,?)"
>> >
>> > On Mon, Nov 30, 2015 at 9:33 AM, Daniel Price <da...@gmail.com>
>> > wrote:
>> >> Hi all. I've been using Groovy to batch insert data into SQL Server,
>> >> and it
>> >> works very well. The syntax I've been using is:
>> >>
>> >> //batch insert
>> >> int dbThrottle = 25000
>> >> db.withTransaction{
>> >> def result = db.withBatch(dbThrottle, insertString){ ps ->
>> >> output.each{
>> >> ps.addBatch(it)
>> >> }
>> >> }
>> >> }
>> >>
>> >> This code is used to insert data from list 'output' into my Sql Server
>> >> DB.
>> >> The 'insertString' is just the typical insert statement dynamically
>> >> derived
>> >> from the target table and columns.
>> >>
>> >> I now have a need to use a sequence generator to populate one or more
>> >> columns in some tables. I can do this by putting sequence numbers in a
>> >> list
>> >> and inserting such sequence lists into my 'output' data list, but this
>> >> is
>> >> very slow. Is there a way I can include the 'NEXT VALUE FOR' sequence
>> >> query
>> >> in the batch insert query so that it is evaluated during batch insert?
>> >> Will
>> >> this be any faster?
>> >>
>> >> Thanks!
>> >> D
>
>
Re: Batch Insert with Sequence?
Posted by Daniel Price <da...@gmail.com>.
Thanks, David! I read many MSDN pages but didn't encounter that exact
syntax. I'll give it a try and report back. Since I'm using this insert
statement with withBatch(), I wonder how I should handle the list of values
that are being fed into it...
On Mon, Nov 30, 2015 at 11:41 PM, David Durham <da...@gmail.com>
wrote:
> Ah, no, it looks like it's example E. from this page:
>
> https://msdn.microsoft.com/en-us/library/ff878370.aspx
>
> INSERT Test.TestTable (CounterColumn,Name)
> VALUES (NEXT VALUE FOR Test.CountBy1, 'Syed') ;
>
> On Mon, Nov 30, 2015 at 10:38 PM, David Durham
> <da...@gmail.com> wrote:
> > I think your insertString will have something like "insert into
> > myTable (...) values (mysequence.nextval, ?,?,?)"
> >
> > On Mon, Nov 30, 2015 at 9:33 AM, Daniel Price <da...@gmail.com>
> wrote:
> >> Hi all. I've been using Groovy to batch insert data into SQL Server,
> and it
> >> works very well. The syntax I've been using is:
> >>
> >> //batch insert
> >> int dbThrottle = 25000
> >> db.withTransaction{
> >> def result = db.withBatch(dbThrottle, insertString){ ps ->
> >> output.each{
> >> ps.addBatch(it)
> >> }
> >> }
> >> }
> >>
> >> This code is used to insert data from list 'output' into my Sql Server
> DB.
> >> The 'insertString' is just the typical insert statement dynamically
> derived
> >> from the target table and columns.
> >>
> >> I now have a need to use a sequence generator to populate one or more
> >> columns in some tables. I can do this by putting sequence numbers in a
> list
> >> and inserting such sequence lists into my 'output' data list, but this
> is
> >> very slow. Is there a way I can include the 'NEXT VALUE FOR' sequence
> query
> >> in the batch insert query so that it is evaluated during batch insert?
> Will
> >> this be any faster?
> >>
> >> Thanks!
> >> D
>
Re: Batch Insert with Sequence?
Posted by David Durham <da...@gmail.com>.
Ah, no, it looks like it's example E. from this page:
https://msdn.microsoft.com/en-us/library/ff878370.aspx
INSERT Test.TestTable (CounterColumn,Name)
VALUES (NEXT VALUE FOR Test.CountBy1, 'Syed') ;
On Mon, Nov 30, 2015 at 10:38 PM, David Durham
<da...@gmail.com> wrote:
> I think your insertString will have something like "insert into
> myTable (...) values (mysequence.nextval, ?,?,?)"
>
> On Mon, Nov 30, 2015 at 9:33 AM, Daniel Price <da...@gmail.com> wrote:
>> Hi all. I've been using Groovy to batch insert data into SQL Server, and it
>> works very well. The syntax I've been using is:
>>
>> //batch insert
>> int dbThrottle = 25000
>> db.withTransaction{
>> def result = db.withBatch(dbThrottle, insertString){ ps ->
>> output.each{
>> ps.addBatch(it)
>> }
>> }
>> }
>>
>> This code is used to insert data from list 'output' into my Sql Server DB.
>> The 'insertString' is just the typical insert statement dynamically derived
>> from the target table and columns.
>>
>> I now have a need to use a sequence generator to populate one or more
>> columns in some tables. I can do this by putting sequence numbers in a list
>> and inserting such sequence lists into my 'output' data list, but this is
>> very slow. Is there a way I can include the 'NEXT VALUE FOR' sequence query
>> in the batch insert query so that it is evaluated during batch insert? Will
>> this be any faster?
>>
>> Thanks!
>> D
Re: Batch Insert with Sequence?
Posted by David Durham <da...@gmail.com>.
I think your insertString will have something like "insert into
myTable (...) values (mysequence.nextval, ?,?,?)"
On Mon, Nov 30, 2015 at 9:33 AM, Daniel Price <da...@gmail.com> wrote:
> Hi all. I've been using Groovy to batch insert data into SQL Server, and it
> works very well. The syntax I've been using is:
>
> //batch insert
> int dbThrottle = 25000
> db.withTransaction{
> def result = db.withBatch(dbThrottle, insertString){ ps ->
> output.each{
> ps.addBatch(it)
> }
> }
> }
>
> This code is used to insert data from list 'output' into my Sql Server DB.
> The 'insertString' is just the typical insert statement dynamically derived
> from the target table and columns.
>
> I now have a need to use a sequence generator to populate one or more
> columns in some tables. I can do this by putting sequence numbers in a list
> and inserting such sequence lists into my 'output' data list, but this is
> very slow. Is there a way I can include the 'NEXT VALUE FOR' sequence query
> in the batch insert query so that it is evaluated during batch insert? Will
> this be any faster?
>
> Thanks!
> D