many updates at once

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

many updates at once

martin spevak
Hi all,

I have specific problem with DBIx update (Oracle driver). I need to update let say 200.000 rows. Each row should have different number of changed columns. DBIx->update creates request one by one like this: "update table set column1='value' where id=1". Query itself is really small (few bytes), but for each query is created packet. So I have to transfer 200.000 packets. Ping to DB server is 4ms, what mean that 1000 updates are done in 4 seconds and the job is done in 13 minutes. I need to somehow decrease number of packets, let say to send more updates at once. Do you have some idea how to do it? I don't care if update is success or not. Some kind of bulk update. My idea was to send all UPDATE queries as strings into storage procedure (let say per 10 queries). So I try to get this query from DBIx somehow as ResultSet->as_query (but this is not possible for update command).

Do you have any other ideas, how to send few update commands in one packet?

Many thanks.

--
Martin (singer) Spevak
HPES Software Development Engineer
HPES Network Management Solutions
Location: Galvaniho 7/A, Bratislava, Slovakia
Tel.: +421 2 5752 5390
Email: [hidden email]

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: many updates at once

Len Jaffe


On Thu, Aug 20, 2015 at 8:11 AM, Martin Spevak <[hidden email]> wrote:
Hi all,

[edit] I need to update let say 200.000 rows. Each row should have different number of changed columns.
[edit] I don't care if update is success or not. Some kind of bulk update. My idea was to send all UPDATE queries as strings into storage procedure (let say per 10 queries). So I try to get this query from DBIx somehow as ResultSet->as_query (but this is not possible for update command).

Do you have any other ideas, how to send few update commands in one packet?

I think you're on a good path.

Send a data structure to a stored proc.
execute stored proc using literal SQL "EXECUTE foo() ....."

Since you don't care about results, it's fire and forget. 



--
Len Jaffe -  [hidden email] 
614-404-4214    @LenJaffe  www.lenjaffe.com
Curator of Advent Planet - An Aggregation of Online Advent Calendars.


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Loading...