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
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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 of the time the case) then the batch insert does
> not appear to be much beneficial.

No, the point is that once the client and the database are not on the
same machine the network that they communicate across becomes a
consideration. In a contrived example the client could be in the same
same data center as the database server and talking to the server via a
dialup modem and the data transfer would be worse then the same client
talking to a database server a 1000 miles away across a fiber optic
connection. This gets back to plan --> test.
/|\ |
| <-- \|/

>
> Which also means , people here were afraid of having triggers in such a
> high dml table as because this will make the "batch insert"
> automatically  converted into "row by row" behind the scene, but
> considering the above results, it looks fine to go with a row by row
> approach (but just having batch commit in place in place of row by row
> commit). And not to worry about implementing the true batch insert
> approach as that is not making a big difference here in data load
> performance.

This is getting ahead of the game. The immediate issue is the deadlock
issues with the concurrent sessions and duplicate data.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com