groovy-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Owen Rubel <oru...@gmail.com>
Subject Re: Groovy Script withBatch() Hang?
Date Tue, 14 Jul 2015 17:55:27 GMT
Make sure to read through documentation and that you are implementing
everything properly. Documentation is your friend. ;)

Owen Rubel
415-971-0976
orubel@gmail.com

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

> Much improved batch insert performance, but still experiencing hangs--not
> just after withBatch(), though that's still the most common case.  I've
> also seen hangs when reading an Excel file and parsing it into XML, and
> while manipulating the contents of a large 2D list (of lists)...  I'm not
> doing any logging, or writing to any files, for that matter.  Anything that
> could cause such systemic hangs?  The script actually seems to continue and
> finish its work even though output is frozen (and it doesn't exit) until I
> ctrl-c it...
>
> Thanks,
> D
>
> On Tue, Jul 14, 2015 at 12:55 PM, Owen Rubel <orubel@gmail.com> wrote:
>
>> No prob. I find whenever I'm stuck its either sleep or documentation I am
>> missing myself ;)
>> On Jul 14, 2015 9:00 AM, "Daniel Price" <danprice303@gmail.com> wrote:
>>
>>> 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