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 17:51:27 GMT
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