Re: How batch processing works

On 10/4/24 1:05 PM, Lok P wrote: On Mon, Sep 23, 2024 at 12:53 AM Peter J. Holzer > wrote: > Thank you so much. > I was expecting method-3(batch insert) to be the fastest or atleast as you said > perform with similar speed as method-2 (row by ro

Re: How batch processing works

On Mon, Sep 23, 2024 at 12:53 AM Peter J. Holzer wrote: > > > Thank you so much. > > I was expecting method-3(batch insert) to be the fastest or atleast as > you said > > perform with similar speed as method-2 (row by row insert with batch > commit) > > Oops, sorry! I wrote that the wrong way aro

Re: How batch processing works

On 2024-09-21 12:15:44 -0700, Adrian Klaver wrote: > FYI, this is less of problem with psycopg(3) and pipeline mode: > [...] > with db.pipeline(): > for i in range(1, num_inserts+1): > csr.execute("insert into parent_table values(%s, %s)", (i, 'a')) > if i % batch_size == 0: >

Re: How batch processing works

On 2024-09-21 20:55:13 +0530, Lok P wrote: > On Sat, Sep 21, 2024 at 8:07 PM Peter J. Holzer wrote: [... lots of code elided. method2 used 1 insert per row, method3 1 insert for 50 rows ...] > On my laptop, method2 is about twice as fast as method3. But if I > connect to a database on the

Re: How batch processing works

On 9/21/24 22:21, Lok P wrote: On Sun, Sep 22, 2024 at 12:46 AM Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: Thank you. So if I get it correct, if the client app(from which the data is getting streamed/inserted) is in the same data center/zone as the database (which is most

Re: How batch processing works

On Sun, Sep 22, 2024 at 12:46 AM Adrian Klaver wrote: > On 9/21/24 07:36, Peter J. Holzer wrote: > > On 2024-09-21 16:44:08 +0530, Lok P wrote: > > > > --- > > #!/usr/bin/python3 > > > > import time >

Re: How batch processing works

On 9/21/24 07:36, Peter J. Holzer wrote: On 2024-09-21 16:44:08 +0530, Lok P wrote: --- #!/usr/bin/python3 import time import psycopg2 num_inserts = 10_000 batch_size = 50 db = psycopg2.connect()

Re: How batch processing works

On Sat, Sep 21, 2024 at 8:07 PM Peter J. Holzer wrote: > On 2024-09-21 16:44:08 +0530, Lok P wrote: > > But wondering why we don't see any difference in performance between > method-2 > > and method-3 above. > > The code runs completely inside the database. So there isn't much > difference betwee

Re: How batch processing works

On 2024-09-21 16:44:08 +0530, Lok P wrote: > But wondering why we don't see any difference in performance between method-2 > and method-3 above. The code runs completely inside the database. So there isn't much difference between a single statement which inserts 50 rows and 50 statements which ins

Re: How batch processing works

On Sat, Sep 21, 2024 at 9:51 AM Michał Kłeczek wrote: > Hi, > > > On 19 Sep 2024, at 07:30, Lok P wrote: > > > [snip] > > > > Method-4 > > > > INSERT INTO parent_table VALUES (1, 'a'), (2, 'a'); > > INSERT INTO child_table VALUES (1,1, 'a'), (1,2, 'a'); > > commit; > > I’ve done some batch pr

Re: How batch processing works

Hi, > On 19 Sep 2024, at 07:30, Lok P wrote: > [snip] > > Method-4 > > INSERT INTO parent_table VALUES (1, 'a'), (2, 'a'); > INSERT INTO child_table VALUES (1,1, 'a'), (1,2, 'a'); > commit; I’ve done some batch processing of JSON messages from Kafka in Java. By far the most performant way

Re: How batch processing works

Below are the results for the posted methods. Tested it on local and it gave no difference in timing between the method-2 andmethod-3. Failed to run in dbfiddle somehow. Also I was initially worried if adding the trigger to the our target table, will worsen the performance as because , it will mak

Re: How batch processing works

On Thu, Sep 19, 2024 at 5:40 PM Ron Johnson wrote: > On Thu, Sep 19, 2024 at 5:24 AM Lok P wrote: > >> >> >> [snip] > >> DO $$ >> DECLARE >> num_inserts INTEGER := 10; >> batch_size INTEGER := 50; >> start_time TIMESTAMP; >> end_time TIMESTAMP; >> elapsed_time INTERVAL; >

Re: How batch processing works

On Thu, Sep 19, 2024 at 5:24 AM Lok P wrote: > > On Thu, Sep 19, 2024 at 11:31 AM Ron Johnson > wrote: > >> >> [snip] >> >>> >>> Method-4 >>> >>> INSERT INTO parent_table VALUES (1, 'a'), (2, 'a'); >>> INSERT INTO child_table VALUES (1,1, 'a'), (1,2, 'a'); >>> commit; >>> >> >> If I knew that

Re: How batch processing works

On Thu, Sep 19, 2024 at 11:31 AM Ron Johnson wrote: > > [snip] > >> >> Method-4 >> >> INSERT INTO parent_table VALUES (1, 'a'), (2, 'a'); >> INSERT INTO child_table VALUES (1,1, 'a'), (1,2, 'a'); >> commit; >> > > If I knew that I had to load a structured input data file (even if it had > pare

Re: How batch processing works

On Thu, Sep 19, 2024 at 1:31 AM Lok P wrote: > Hello, > Saw multiple threads around the same , so I want some clarification. As we > know row by row is slow by slow processing , so in heavy write systems(say > the client app is in Java) , people asked to do DMLS in batches rather in a > row by ro

How batch processing works

Hello, Saw multiple threads around the same , so I want some clarification. As we know row by row is slow by slow processing , so in heavy write systems(say the client app is in Java) , people asked to do DMLS in batches rather in a row by row fashion to minimize the chatting or context switches be