New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Efficient Bulk Create/Insert With One Database Call #3357
Comments
I am agree with the point of @shadyanwar . I am also looking for the same . |
any update on the same ? |
Talked to @jannyHou, this will be at the connector level. |
@dhmlau |
@dhmlau I’m happy to help |
I'm using Mongodb facing the same issue.... have any one found the solution? |
@dhmlau @achrinza This is a very significant issue. We fixed it by overriding the createAll method in repository. But that's not right, because I have to do this at multiple places. I can create a mixin and do this too, but since it might be a performance bug for most of the databases, should we not have it in LB4 itself ? Having said that, I tried to look for a solution to this in connector level. I checked postgres connector, for instance, but there was no reference of createAll here. Then I checked its base class in loopback-connector. There also I can only see create method. I checked juggler-bridge then. There I saw the createAll method making an internal call to create method. The create method invokes modelclass.create, which eventually invokes the connector create method. See below.
I am basically trying to raise a PR to have this capability of either using create method multiple times or using insert query for multiple records. This definitely should go to connectors actually. Can you confirm, I have to modify the juggler bridge function first and then move to connectors, or am I getting it wrong ? |
A summary of the code flow is:
So:
|
Thanks @achrinza . However, I think mongodb connector also uses the same sql connector. Mongo DB has different query for insert multiple. I'll check and confirm. |
@samarpan-b The MongoDB connector inherits from Though it does implement the necessary CRUD functions to be compatible with Juggler DataAccessObject. |
@achrinza I checked if the multiple insert in one query dialect is available in different databases or not and also if its available, is it same or not ? So here is what I am thinking
|
Point 1 to 5 are done and PRs are raised. @achrinza please do take a look. |
…uery fix loopbackio#3357 Signed-off-by: Samarpan Bhattacharya <this.is.samy@gmail.com>
Point 6 is also done. But its in draft. We need connector and juggler to release first. Then only I can update dependency in @loopback/repository. |
…uery fix loopbackio#3357 Signed-off-by: Samarpan Bhattacharya <this.is.samy@gmail.com>
This is now released for postgresql in version 6.0.0 of loopback-connector-postgresql. See here - https://github.com/loopbackio/loopback-connector-postgresql/blob/master/CHANGES.md |
Suggestion
The createAll() method provides the ability to bulk create records, however, it is translated into multiple insert statements since the dao layer inserts records one by one. I suggested that, in MySQL for example, LB4 uses the multiple-row INSERT syntax which aims at reducing the communication overhead between the client and the server.
Use Cases
Less server overhead and better performance. In case of inserting around 10k records, the performance impact due to the current implementation is huge. A query might take something like 10 seconds or more and raise the server utilization extensively. Whereas with the multiple-row INSERT syntax the same query could take something like 1 second.
Examples
Optimally, instead of:
We would have:
INSERT INTO `tbl`(`fld1`,`fld2`,`fld3`,`fld4`) VALUES(1,2,3,4),(10,20,30,40),(100,200,300,400);
Acceptance criteria
TBD - will be filled by the team.
The text was updated successfully, but these errors were encountered: