groovy-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel Price <danprice...@gmail.com>
Subject Re: Groovy Script withBatch() Hang?
Date Tue, 14 Jul 2015 15:59:26 GMT
I'd been using an explicit call to executeBatch() based on a tutorial I
found somewhere, but when I removed that call, not only did I get the
expected return, but row insert performance increased by an incredible
amount--180k rows used to take about 200s, but now about 10s.  No hangs
yet...  Thanks, Owen, for directing me to look at method implementation,
which had me seek guidance right from the source.

Regards,
D

On Tue, Jul 14, 2015 at 11:23 AM, Daniel Price <danprice303@gmail.com>
wrote:

> I've implemented withTransaction() now, and I'm running continually
> waiting for another hang.  If there were an error, wouldn't it crash the
> script--or does hang == crash?  I still can't get a return from withBatch()
> with an actual input list, which consists of many different datatypes, only
> with 'sanitized' input lists of Strings and ints...
>
> Regards,
> D
>
> On Tue, Jul 14, 2015 at 11:02 AM, Owen Rubel <orubel@gmail.com> wrote:
>
>> The hangs are probably a result of:
>>
>> For integrity and performance reasons, you may wish to consider executing
>> your batch command(s) within a transaction:
>>
>>  sql.withTransaction {
>>      def result1 = sql.withBatch { ... }
>>      ...
>>  }
>>
>>
>> Owen Rubel
>> 415-971-0976
>> orubel@gmail.com
>>
>> On Tue, Jul 14, 2015 at 7:28 AM, Dinko Srko─Ź <dinko.srkoc@gmail.com>
>> wrote:
>>
>>> On 14 July 2015 at 15:20, Daniel Price <danprice303@gmail.com> wrote:
>>> > [...]
>>> > I'm wondering why I'm not seeing a non-empty return from my batch code:
>>> >
>>> > myList = [['a','b'],['c','d']]
>>> >
>>> > def result = sql.withBatch(someInt, "insert into myDB.dbo.myTable
>>> (column1,
>>> > column2) values (?,?)"){ ps ->
>>> >    myList.each{
>>> >       ps.addBatch(it)
>>> >    }
>>> >    ps.executeBatch()
>>>       ^^^^^^^^^^^^^^^^
>>> > }
>>> > println "result: " + result
>>> >
>>> > prints:
>>> > result: []
>>> >
>>> > Shouldn't result == [1] * myList.size()?
>>>
>>> The reason for `[]` result might be the explicit call to
>>> `ps.executeBatch()`. That method is actually called implicitly by
>>> `sql.withBatch`, after the Closure parameter is invoked, and it resets
>>> the internal results storage.
>>>
>>> So, your explicit call to `executeBatch` executes whatever is left to
>>> execute, then resets the internal results storage and returns the
>>> collected result. Unfortunately, that result is lost, as
>>> `sql.withBatch` calls `executeBatch` again, but this time there is
>>> nothing left to execute, so the final result is empty array of ints
>>> that is added to the emptied internal results storage.
>>>
>>> tl;dr;
>>> Remove the `ps.executeBatch()` from your code.
>>>
>>> I can't say anything about the hangs, sorry.
>>>
>>> Cheers,
>>> Dinko
>>>
>>> >
>>> > Hangs always happen upon completion of the batch...
>>> >
>>> > Regards,
>>> > D
>>> >
>>> >
>>> >
>>> > On Mon, Jul 13, 2015 at 4:06 PM, Daniel Price <danprice303@gmail.com>
>>> wrote:
>>> >>
>>> >> Good Afternoon,
>>> >>
>>> >>      I'm experiencing infrequent (1/20) hangs with a Groovy script
>>> that
>>> >> uses withBatch() to insert and update a SQL Server DB using stored
>>> >> procedures.  In each case (insert or update), the batch completes as
>>> >> indicated by database content, but the script hangs and does not
>>> continue
>>> >> until I ctrl-c it.  This script is the only user of the table, so DB
>>> locking
>>> >> isn't involved.  The batches are only about 200k rows, but I set the
>>> >> withBatch() parameter to 25k to be nice to the DB.  Has anybody seen
>>> this
>>> >> before?  Any suggestions?  Thanks!
>>> >>
>>> >> D
>>> >
>>> >
>>>
>>
>>
>

Mime
View raw message