Loopback-datasource-juggler: Huge slowdown introduced in release 2.36.0 when using include filter

[GlennMurphy]

Attempting to get ~1000 items with an include filter including ~10 items per item. On version 3.35.3 this request takes around 1 second.

Once updated to 3.36.0 this request hangs for over 1 minute with node maintaining >98% CPU usage throughout.

It seems the more includes the slower this request takes in the newer versions. Without includes, just returning the ~1000 documents is almost instant.

[wertlex]

Same for me.

[GlennMurphy]

The solution for me was to downgrade to 3.35.3. Which I think is necessary until the problem is solved.

[wertlex]

@GlennMurphy I digged code a bit - and found few intresting things:
1) looks like join processing is always done on app's side. It is okay for mongo, but looks very strange for SQL.
2) Join done in very strange manner - it includes objects one-by-one and call processTargetObj on each call. This is not very efficent.
3) processTargetObj (function which includes one objects to another) takes about 230ms on my machine on each call.

I fixed 2nd, and hope to fix 3rd somehow, but I'm not LoopBack guru, just solving my own problems. I can issue PR if somebody intrested in.

[wertlex]

@GlennMurphy this is how I solved problem in my app:
https://github.com/strongloop/loopback-datasource-juggler/pull/704

[raymondfeng]

@wertlex I'll look into your PR. Thanks!

[thaiat]

any news on this issue ?

[wertlex]

@thaiat I have some kind of fix which works for me, but still have no time to finish creating proper PR.

[thaiat]

I m wondering if the include should not be part of the data connector so it can have a specific implementation for each data source?

[wertlex]

@thaiat IMHO it is better to keep current include as fallback and use specific drivers for each DB. I spent about a week digging code and I looks like everything is bad for relational DBs. Joins are made on the app side. Correct me if I'm wrong.

[thaiat]

what I was thinking is that for specific db there is an optimized way to resolve the relationships instead of doing multiple calls and joining in memory
for the caller this should still be transparent

I m
not sure how to override the include in the data connector...

[beeman]

I think that we've encountered similar behavior.

In our app we have functionality for exporting the data to CSV. We use the paginate mixin to fetch the data, than transform it and output it to CSV.

I've enhanced te Paginate mixin to overwrite the limit and the results are clearly better when I decreased the limit from 1000 to 100, as shown in the stats below. We're quite sure that this worked well when it was initially developed.

In our app the sweet spot is to work with pages of 100 items.

Below is an overview of the exporting and transforming of about 4000 records:

Limit Summary
1000  Memory usage: 135.30Mb. Execution time: 97s 77.930221ms
500   Memory usage: 130.02Mb. Execution time: 54s 350.375469ms
250   Memory usage: 130.18Mb. Execution time: 33s 454.669137ms
125   Memory usage: 123.91Mb. Execution time: 26s 180.354973ms
100   Memory usage: 71.04Mb.  Execution time: 27s 51.882395ms
50    Memory usage: 68.40Mb.  Execution time: 34s 220.485596ms

Another thing I did is try the same code with different loopback versions as I was investigating another issue.

With the versions below this is working as expected too (with limit set to 1000), inspired by this comment https://github.com/strongloop/loopback-datasource-juggler/issues/610#issuecomment-134735554

loopback                   2.17.0 instead of 2.22.2
loopback-boot               2.7.1 instead of 2.14.0
loopback-connector-mongodb  1.8.0 instead of 1.13.0

Downgrading to these versions is not a real option but it's interesting to see that it seems to fix some issues.

[emilvirkki]

This seems to be fixed in 2.44.0. Maybe close this issue?

[jannyHou]

Since pr https://github.com/strongloop/loopback-datasource-juggler/pull/704 merged. I am closing it. Please feel free to reopen it if you it's still slow for you or have other suggestions to discuss :)