Efficient Bulk Create/Insert With One Database Call

[shadyanwar]

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:

INSERT INTO `tbl`(`fld1`,`fld2`,`fld3`,`fld4`) VALUES(1,2,3,4);
INSERT INTO `tbl`(`fld1`,`fld2`,`fld3`,`fld4`) VALUES(10,20,30,40);
INSERT INTO `tbl`(`fld1`,`fld2`,`fld3`,`fld4`) VALUES(100,200,300,400);

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.

[pankajcheema]

I am agree with the point of @shadyanwar . I am also looking for the same .

[pankajcheema]

any update on the same ?

[dhmlau]

Talked to @jannyHou, this will be at the connector level.
@shadyanwar @pankajcheema, which connector are you using?

[shadyanwar]

Talked to @jannyHou, this will be at the connector level.
@shadyanwar @pankajcheema, which connector are you using?

@dhmlau
MySQL

[shadyanwar]

@dhmlau I’m happy to help

[dishantr16]

I'm using Mongodb facing the same issue.... have any one found the solution?

[samarpanB]

@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.

async createAll(entities: DataObject<T>[], options?: Options): Promise<T[]> {
    // perform persist hook
    const data = await Promise.all(
      entities.map(e => this.entityToData(e, options)),
    );
    const models = await ensurePromise(this.modelClass.create(data, options));
    return this.toEntities(models);
  }

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 ?

[achrinza]

A summary of the code flow is:

1. LB4 Repository calls Juggler Model Class .create
2. Juggler Model Class gets the above method (and others) from being mixin-ed Juggler DataAccessObject by default
3. Juggler DataAccessObject .create calls the Juggler SQL Connectors' .create
4. The Juggler SQL Connectors inherit from loopback-connector's SQLConnector

So:

1. "crux" of the issue is that the connector needs to implement .createAll.
2. Since the SQL Connectors inherit from a common SQLConnector, updating that should be sufficient (assuming the SQL dialect isn't too different).
3. The Juggler DataAccessObject also needs to be updated to implement .createAll, which would be mixin-ed into the Juggler Model Class during runtime
4. Finally, LB4 DefaultCrudRepository's .createAll needs to be updated in a backwards-compatible way.