Topic
1 reply Latest Post - ‏2013-06-21T21:14:30Z by hclewelad
Developer11
Developer11
64 Posts
ACCEPTED ANSWER

Pinned topic Updating table

‏2013-06-21T11:40:21Z |

Hi All,

 

Currently we are using a for-each loop & dp:sql-execute() to update records in the sql-server table. We are fetching these records from a "DB2" database. 

In order to update around 5k records in each run, it's taking close to 2 hours.

 

Is there any other alternative approach (without using the for-each loop) with which we could reduce the processing time?

 

Thanks

  • hclewelad
    hclewelad
    13 Posts
    ACCEPTED ANSWER

    Re: Updating table

    ‏2013-06-21T21:14:30Z  in response to Developer11

    I've had great results with just using big sql statements that manipulate multiple rows since this minimizes the connection overhead.

    Here's a good stackoverflow link:

    http://stackoverflow.com/questions/2528181/update-multiple-rows-with-one-query

    This approach works better for inserts, but since there doesn't seem to be anyway to turn autocommit off, which I suspect in db2 is your nemesis on doing all those individual updates. We use DP and DB2 where I work as well and I have seen this kind of performance issue. 

    I hope this helps, and I hope you find a setting in the datapower Control Panel->Objects->Network Settings->SQL Data Sources that helps. The only one I see that might help is maximum connections, but it looks like you are doing things sequentially.

    Take care,

    dale