Typeorm: Hydration performance issue on complex dataset

Created on 21 Jun 2018 · 37Comments · Source: typeorm/typeorm

Issue type:

[X] question
[ ] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[ ] mysql / mariadb
[ ] oracle
[X] postgres
[ ] sqlite
[ ] sqljs
[ ] react-native

TypeORM version:

[ ] latest
[ ] @next
[X] 0.2.6

*Steps to reproduce or a small repository showing the problem:

Hi, we have a performance issue in hydration for a (quite) complex query like this:

const ids = [655, 8888, 9999, 12324, 1111, 78, 7, 6, 5, 4, 3, 2, 1, 15, 14, 13, 12, 11, 10, 9];
const res = await this._entityRepository
    .createQueryBuilder("a");
    .leftJoinAndSelect(`a.table2`, "am")
    .leftJoinAndSelect(`a.table1`, "o")
    .leftJoinAndSelect(`am.table3`, "m")
    .leftJoinAndSelect(`a.table4List`, "e")
    .leftJoinAndSelect(`e.table5`, "em")
    .leftJoin(`e.table6`, "el")
    .addSelect("el.id")
    .leftJoinAndSelect(`em.table7`, "emm")
    .leftJoin(`a.table8`, "ap")
    .addSelect("ap.id")
    .leftJoin(`a.table9`, "as")
    .addSelect("as.id")
    .leftJoin(`a.table10`, "l")
    .addSelect("l.id")
    .leftJoin(`a.table11`, "s")
    .addSelect("s.id")
    .leftJoin(`a.table12`, "pc")
    .addSelect("pc.id")
    .where(`a.id IN (${ids.join(",")})`)
    .getMany();

This generates a SQL query like the following:


select
    "a"."id" as "a_id",
    "a"."f1" as "a_f1",
    "a"."f2" as "a_f2",
    "a"."f3" as "a_f3",
    "a"."f4" as "a_f4",
    "a"."f5" as "a_f5",
    "a"."f6" as "a_f6",
    "a"."f7" as "a_f7",
    "a"."f8" as "a_f8",
    "a"."f9" as "a_f9",
    "a"."f10" as "a_f10",
    "a"."f11" as "a_f11",
    "a"."f12" as "a_f12",
    "a"."f13" as "a_f13",
    "a"."f14" as "a_f14",
    "a"."f15" as "a_f15",
    "a"."f16" as "a_f16",
    "a"."f17" as "a_f17",
    "a"."f18" as "a_f18",
    "a"."f19" as "a_f19",
    "a"."f20" as "a_f20",
    "a"."f21" as "a_f21",
    "a"."f22" as "a_f22",
    "a"."f23" as "a_f24",
    "a"."table1Id" as "a_table1Id",
    "a"."table2Id" as "a_table2Id",
        "am"."id" as "am_id",
    "am"."f1" as "am_f1",
    "am"."f2" as "am_f2",
    "am"."f3" as "am_f3",
    "am"."f4" as "am_f4",
    "am"."table3Id" as "am_table3Id",
    "o"."id" as "o_id",
    "o"."f1" as "o_f1",
    "m"."id" as "m_id",
    "m"."f1" as "m_f1",
    "m"."f2" as "m_f2",
    "m"."f3" as "m_f3",
    "e"."id" as "e_id",
    "e"."esn" as "e_esn",
    "e"."table0Id" as "e_table0Id",
    "e"."table5Id" as "e_table5Id",
    "em"."id" as "em_id",
    "em"."f1" as "em_f1",
    "em"."f2" as "em_f2",
    "em"."f3" as "em_f3",
    "em"."f4" as "em_f4",
    "em"."table7Id" as "em_table7Id",
    "el"."id" as "el_id",
    "emm"."id" as "emm_id",
    "emm"."f1" as "emm_f1",
    "emm"."f2" as "emm_f2",
    "emm"."f3" as "emm_f3",
    "ap"."id" as "ap_id",
    "as"."id" as "as_id",
    "l"."id" as "l_id",
    "s"."id" as "s_id",
    "pc"."id" as "pc_id"
from
    "main"."table0" "a"
left join "main"."table2" "am" on "am"."id" = "a"."table2Id"
left join "main"."table1" "o" on "o"."id" = "a"."table1Id"
left join "main"."table3" "m" on "m"."id" = "am"."table3Id"
left join "main"."table4" "e" on "e"."table0Id" = "a"."id"
left join "main"."table5" "em" on "em"."id" = "e"."table5Id"
left join "main"."table6" "el" on "el"."table4Id" = "e"."id"
left join "main"."table7" "emm" on "emm"."id" = "em"."table7Id"
left join "main"."table8" "ap" on "ap"."table0Id" = "a"."id"
left join "main"."table9" "as" on "as"."table0Id" = "a"."id"
left join "main"."table10" "l" on "l"."table0Id" = "a"."id"
left join "main"."table11" "s" on "s"."table0Id" = "a"."id"
left join "main"."table12" "pc" on "pc"."table0Id" = "a"."id"
where "a"."id" in (655, 8888, 9999, 12324, 1111, 78, 7, 6, 5, 4, 3, 2, 1, 15, 14, 13, 12, 11, 10, 9)

The SQL query in our development database takes approximately 32ms and returns a raw resultset of approx 4400 rows, as you can see it's very fast. On the other hand the hydration process takes approx 4500ms running inside an hi-end Intel I7 CPU. Have you any clues why the hydration process takes so long ? Could be something related to the entities config ?

Thanks in adavnce for your help,
Leonardo

postgres performance question

Source

leoperria

👍6

Most helpful comment

Hi, I work with @leoperria who started this thread. (I’m CEO at Stellwagen Technology, a two year-old start-up with a team of 11. We are huge fans of TypeScript, using it for both the front-end and back-end of our platform).

@pleerock et al: first, thanks all for the great work on TypeOrm - it’s a fantastic tool in the TypeScript ecosystem.

We are thinking about how to proceed with projects over the coming months and would appreciate your thinking related to this issue:

Is there a view yet on how difficult it would be to make hydration of queries like this much faster? (e.g. similar to query time)
Is there a view yet on likely prioritization/timing of this work?
How challenging would it be for a developer not familiar with the internals of TypeOrm to contribute to a solution here?

We have requirements coming up which will involve 10-20x the burden of the query Leo mentions above. The current TypeOrm hydration speed would be too slow for this. We could stop using TypeOrm and write and process queries manually if necessary, but this will be very tedious so will be our option of last resort!

We would appreciate any colour you can provide which could help in us planning our next few months.

Many thanks,
Dan

devison on 10 Aug 2018

👍10

All 37 comments

No, I have no idea why its that long. How many sql rows being returned by your SQL query?

pleerock on 22 Jun 2018

Hi, about 4500 raw rows

leoperria on 22 Jun 2018

ah sorry I missed that part in message.

Okay, so we need to debug to figure out source of issue. Can you contribute and create a reproduction git with minimal possible code to reproduce the issue? (or better a PR with a test)

pleerock on 22 Jun 2018

generally I recommend to avoid so many left joins with selections and execute separate queries since its more performant in many of cases.

pleerock on 22 Jun 2018

Hi, thanks for your answer, I will create a git with the code to reproduce the issue and post it here

leoperria on 25 Jun 2018

Hi, I've managed to create a reproduction git repo here: https://github.com/stelltec/typeorm_testcase1
This is not exactly the same DB schema we have but resembles it a lot in terms of complexity, so to be clear this is just a tree of entities, each entity has a subEntities properties with a @OneToMany annotation.
In the real use case we are modeling aircraft and part of them, like the engines, parts of the engine and so on. The point is that, even if it's different (e.g. multiple @OneToMany relations in the same entity, some @OneToMany etc), our real use case has similar level of nesting and this looks like to cause similar performance issues.

I created a case with 13 entities connected one to each other with a "@ ManyToOne" relation, so basically we have 13 levels of "nesting". For each entity in one level I create a given number of children in the next level following this rule:

const ENTITIES = [
    { EntityConstructor: Entity1, nChildren: 20 },
    { EntityConstructor: Entity2, nChildren: 1 },
    { EntityConstructor: Entity3, nChildren: 2 },
    { EntityConstructor: Entity4, nChildren: 16 },
    { EntityConstructor: Entity5, nChildren: 1 },
    { EntityConstructor: Entity6, nChildren: 1 },
    { EntityConstructor: Entity7, nChildren: 2 },
    { EntityConstructor: Entity8, nChildren: 2 },
    { EntityConstructor: Entity9, nChildren: 1 },
    { EntityConstructor: Entity10, nChildren: 1 },
    { EntityConstructor: Entity11, nChildren: 1 },
    { EntityConstructor: Entity12, nChildren: 2 },
    { EntityConstructor: Entity13, nChildren: 0 }
];

So for example I created 20 entities of type Entity1, each of them will have 1 subEntity of type Entity2. Each Entity2 entity will have 2 subEntities of type Entities3 and so on... the exact number of raw rows from the DB is slightly higher, around 5100, but I think it's not so different from the original one (around 4600)

I really don't know the internals of TypeORM hydration stage: I suspect it's a very complex code, but, just as a reference, I show how long does it takes to build in memory a tree of JS object of the same complexity of the previous one and we can see this is very fast.

Could it be possible that the culprit is the way we query the DB with the query builder ?

The end result is something like this:

```
$ npm start

[email protected] start /home/leonardo/code/repos/typeorm_test
npm run restart_db && sleep 3 && npm run run_app

[email protected] restart_db /home/leonardo/code/repos/typeorm_test
./scripts/restart_db.sh

cd0ebcbfcfff
cd0ebcbfcfff
e3922b098b8b341ce84e65afe5ea5c7aefea5c00399dc01022b9015f013b6917

[email protected] run_app /home/leonardo/code/repos/typeorm_test
ts-node src/index.ts

######################################## Create DB objects

Creating level=0 parentEntityId=null
Creating level=1 parentEntityId=1
Creating level=1 parentEntityId=1
Creating level=1 parentEntityId=1

..............

######################################## Query the DB

query: SELECT "e1"."id" AS "e1_id", "e1"."field0" AS "e1_field0", "e1"."field1" AS "e1_field1", "e1"."field2" AS "e1_field2", "e1"."field3" AS "e1_field3", "e2"."id" AS "e2_id", "e2"."field0" AS "e2_field0", "e2"."field1" AS "e2_field1", "e2"."field2" AS "e2_field2", "e2"."field3" AS "e2_field3", "e2"."parentEntityId" AS "e2_parentEntityId", "e3"."id" AS "e3_id", "e3"."field0" AS "e3_field0", "e3"."field1" AS "e3_field1", "e3"."field2" AS "e3_field2", "e3"."field3" AS "e3_field3", "e3"."parentEntityId" AS "e3_parentEntityId", "e4"."id" AS "e4_id", "e4"."field0" AS "e4_field0", "e4"."field1" AS "e4_field1", "e4"."field2" AS "e4_field2", "e4"."field3" AS "e4_field3", "e4"."parentEntityId" AS "e4_parentEntityId", "e5"."id" AS "e5_id", "e5"."field0" AS "e5_field0", "e5"."field1" AS "e5_field1", "e5"."field2" AS "e5_field2", "e5"."field3" AS "e5_field3", "e5"."parentEntityId" AS "e5_parentEntityId", "e6"."id" AS "e6_id", "e6"."field0" AS "e6_field0", "e6"."field1" AS "e6_field1", "e6"."field2" AS "e6_field2", "e6"."field3" AS "e6_field3", "e6"."parentEntityId" AS "e6_parentEntityId", "e7"."id" AS "e7_id", "e7"."field0" AS "e7_field0", "e7"."field1" AS "e7_field1", "e7"."field2" AS "e7_field2", "e7"."field3" AS "e7_field3", "e7"."parentEntityId" AS "e7_parentEntityId", "e8"."id" AS "e8_id", "e8"."field0" AS "e8_field0", "e8"."field1" AS "e8_field1", "e8"."field2" AS "e8_field2", "e8"."field3" AS "e8_field3", "e8"."parentEntityId" AS "e8_parentEntityId", "e9"."id" AS "e9_id", "e9"."field0" AS "e9_field0", "e9"."field1" AS "e9_field1", "e9"."field2" AS "e9_field2", "e9"."field3" AS "e9_field3", "e9"."parentEntityId" AS "e9_parentEntityId", "e10"."id" AS "e10_id", "e10"."field0" AS "e10_field0", "e10"."field1" AS "e10_field1", "e10"."field2" AS "e10_field2", "e10"."field3" AS "e10_field3", "e10"."parentEntityId" AS "e10_parentEntityId", "e11"."id" AS "e11_id", "e11"."field0" AS "e11_field0", "e11"."field1" AS "e11_field1", "e11"."field2" AS "e11_field2", "e11"."field3" AS "e11_field3", "e11"."parentEntityId" AS "e11_parentEntityId", "e12"."id" AS "e12_id", "e12"."field0" AS "e12_field0", "e12"."field1" AS "e12_field1", "e12"."field2" AS "e12_field2", "e12"."field3" AS "e12_field3", "e12"."parentEntityId" AS "e12_parentEntityId", "e13"."id" AS "e13_id", "e13"."field0" AS "e13_field0", "e13"."field1" AS "e13_field1", "e13"."field2" AS "e13_field2", "e13"."field3" AS "e13_field3", "e13"."parentEntityId" AS "e13_parentEntityId" FROM "entity1" "e1" LEFT JOIN "entity2" "e2" ON "e2"."parentEntityId"="e1"."id" LEFT JOIN "entity3" "e3" ON "e3"."parentEntityId"="e2"."id" LEFT JOIN "entity4" "e4" ON "e4"."parentEntityId"="e3"."id" LEFT JOIN "entity5" "e5" ON "e5"."parentEntityId"="e4"."id" LEFT JOIN "entity6" "e6" ON "e6"."parentEntityId"="e5"."id" LEFT JOIN "entity7" "e7" ON "e7"."parentEntityId"="e6"."id" LEFT JOIN "entity8" "e8" ON "e8"."parentEntityId"="e7"."id" LEFT JOIN "entity9" "e9" ON "e9"."parentEntityId"="e8"."id" LEFT JOIN "entity10" "e10" ON "e10"."parentEntityId"="e9"."id" LEFT JOIN "entity11" "e11" ON "e11"."parentEntityId"="e10"."id" LEFT JOIN "entity12" "e12" ON "e12"."parentEntityId"="e11"."id" LEFT JOIN "entity13" "e13" ON "e13"."parentEntityId"="e12"."id"

Test n.0
⌚ > TIME started for Raw query
⌚ < TIME elapsed for Raw query: 117.89 msec
N. of rows returned: 5120
⌚ > TIME started for Query with hydration
⌚ < TIME elapsed for Query with hydration: 5753.91 msec

Test n.1
⌚ > TIME started for Raw query
⌚ < TIME elapsed for Raw query: 91.96 msec
N. of rows returned: 5120
⌚ > TIME started for Query with hydration
⌚ < TIME elapsed for Query with hydration: 4613.46 msec

Test n.2
⌚ > TIME started for Raw query
⌚ < TIME elapsed for Raw query: 68.04 msec
N. of rows returned: 5120
⌚ > TIME started for Query with hydration
⌚ < TIME elapsed for Query with hydration: 4467.61 msec

Test n.3
⌚ > TIME started for Raw query
⌚ < TIME elapsed for Raw query: 65.83 msec
N. of rows returned: 5120
⌚ > TIME started for Query with hydration
⌚ < TIME elapsed for Query with hydration: 4490.20 msec

Test n.4
⌚ > TIME started for Raw query
⌚ < TIME elapsed for Raw query: 126.36 msec
N. of rows returned: 5120
⌚ > TIME started for Query with hydration
⌚ < TIME elapsed for Query with hydration: 4435.87 msec

######################################## Same complexity tree build:

Test n.0
⌚ > TIME started for Manual tree build
⌚ < TIME elapsed for Manual tree build: 3.91 msec

Test n.1
⌚ > TIME started for Manual tree build
⌚ < TIME elapsed for Manual tree build: 11.13 msec

Test n.2
⌚ > TIME started for Manual tree build
⌚ < TIME elapsed for Manual tree build: 2.20 msec

Test n.3
⌚ > TIME started for Manual tree build
⌚ < TIME elapsed for Manual tree build: 10.57 msec

Test n.4
⌚ > TIME started for Manual tree build
⌚ < TIME elapsed for Manual tree build: 14.24 msec

leoperria on 2 Jul 2018

👍2

thanks I'll checkout this repo.

btw, do you know that are doing it wrong with data structure you have and joins are you are trying to apply? You should use special sql patterns if you have a tree structures in your data.

pleerock on 2 Jul 2018

👍2

Thanks ! yes I do know that for trees another pattern is used (e.g. closure table). As I mentioned we created this to reproduce the level of complexity of the data structure we have in reality, that is not a tree but a regular ER normalized schema with tables like "aircraft" , "engines" and many other parts and related entities , so not a tree. What concerns us is that hydrating 5K rows into an object tree requires 4 seconds, that's why I suspect we are missing something in the query (sorting ?) or something like that..
So, given my previous results, I was under the impression that hydrating 5K rows dataset (the flat tree) into a TypeaOrm entity object tree should be matter of milliseconds not seconds, am I missing anything ?

leoperria on 2 Jul 2018

👍2

Just adding a +1 to this post.

I have also experienced this sort of performance issue with the hydrator as well.

87,000 rows
Query time: 45ms
Typeorm Total: 11seconds

This request is to generate a report. However as a workaround (and better solution) I am running the sql directly, formatting it to my spec, instead of using typeorm.

I just wanted to chime in and +1 @leoperria 's example.

puttputt on 20 Jul 2018

👍3

Ok, we are actually hitting the same problem right now. The worst part of this is that it's blocking our server (the nestjs thread) and I have no clue why.

We have the same kind of architecture and complexity as @leoperria .

Edit:

So more info on this. After digging a bit the issue comes from the RawSqlResultsToEntityTransformer.transform() method.

The massive use of find, map and other operators is probably the root cause. But the side effect is that all this is synchronous, meaning it's blocking.

We used a dirty fix by modifying this class like so :

const promiseSerial = <T>(funcs: Array<() => Promise<T>>): Promise<T[]> =>
  funcs.reduce((promise, func) =>
    promise.then((values) => func().then((value) => [...values, value])),
    Promise.resolve([] as T[]));
const sleep = (ms: number) => new Promise(resolve => setTimeout(resolve, ms));

    async transform(rawResults: any[], alias: Alias): Promise<any[]> {
        const group = this.group(rawResults, alias);
        const entities: any[] = [];

        const promises: Array<() => Promise<void>> = [];
        group.forEach(results => {
            promises.push(async () => {
                await sleep(0);
                const entity = await this.transformRawResultsGroup(results, alias);
                if (entity !== undefined)
                    entities.push(entity);
            });
        });
        await promiseSerial(promises);
        return entities;
    }

and awaiting this in the SelectQueryBuilder. Its quick and dirty, but at least our thread is not blocked anymore !

RDeluxe on 25 Jul 2018

👍6

@pleerock et al: first, thanks all for the great work on TypeOrm - it’s a fantastic tool in the TypeScript ecosystem.

We are thinking about how to proceed with projects over the coming months and would appreciate your thinking related to this issue:

Is there a view yet on how difficult it would be to make hydration of queries like this much faster? (e.g. similar to query time)
Is there a view yet on likely prioritization/timing of this work?
How challenging would it be for a developer not familiar with the internals of TypeOrm to contribute to a solution here?

We would appreciate any colour you can provide which could help in us planning our next few months.

Many thanks,
Dan

devison on 10 Aug 2018

👍10

Hi, just an update here. Our concerns did materialize: the TypeOrm hydration step was in excess of a minute (!) in our application with our new use-case (loading details from a number of tables for 20 aircraft). We have been able to work around this somewhat by sheer brute force: we expanded our AWS fleet and now split the query and do hydration in parallel across 16 servers (!). We have also been experimenting with not doing joins, but doing a sequence of single-table queries (which is slower at DB level, but side-steps the TypeOrm hydration performance bottleneck). Still this is our dominant performance issue (with multi-second delays) so we are trying to figure out what to do next.
@pleerock et al: It would be very helpful to get your thoughts on the questions above (even if it's bad news!)
Many thanks,
Dan

devison on 28 Sep 2018

👍7

We've run into a similar issue. Our data isn't too complex but eager loading a single entry with all eager joins can fetch upwards of 100-400k rows that need to be resolved/hydrated.

Our current workaround is to create a custom repository with it's own Find* methods on it. Then remove any One-To-Many or Many-To-Many eager loads from the entity so we don't run into any eager loading issues. Lastly in our custom Find* functions we fetch the One-To-Many and Many-To-Many relations we need to, and stash them back on the actual entity. This is essentially what you're describing with "single-table-queries" @devison.

Since each relation can be resolved to a promise, we did see some performance increases using Promise.all vs resolving each one independently. Again, our data/tables aren't that huge, so we haven't seen a huge hit (yet) with database io/processing being a bottleneck when using single queries.

aaronstaves on 28 Sep 2018

👍1

@pleerock generally I recommend to avoid so many left joins with selections and execute separate queries since its more performant in many of cases.

Hi, I have same performance issue doing a query with max 3 joins(Left or Join) with Select, and global custom addSelect up to 10 fields... for 1000 rows = ~1sec execution time. a lot of time is spent by TypeOrm to transformColumns and transformJoins... The rawQuery done by TypeOrm take about ~3ms.. Native sql Query execution time is under 0.00068ms

dmitrirussu on 9 Oct 2018

👍2

@pleerock No, I have no idea why its that long. How many sql rows being returned by your SQL query?

may using native "for iterator" and Typescript Generators will help you to solve that issue
https://medium.com/tech-tajawal/loops-performances-in-node-js-9fbccf2d6aa6
https://basarat.gitbooks.io/typescript/docs/generators.html

dmitrirussu on 9 Oct 2018

👍1

Hi, any input on this? I'm facing similar issues. @RDeluxe solution seems promising.
Having a thread choke on a single mapping process seems undesirable.

attilaorosz on 10 Oct 2018

Did you try #2894? It's not final solution, but let us know if it helps yours performance issues too.

Kononnable on 11 Oct 2018

One strange thing is that for my particular use case I have a getOne and a getMany for nearly identical queries. The getOne was taking ~6 seconds to run but the getMany was less than 1 second even though it returned everything in the getOne plus additional results of the same type.

This was without the changes from #2894. I resolved my issue on the getOne by using a separate query to pull in some of the data I was using multiple left joins for.

jedsmith13 on 19 Oct 2018

Im going to release @Kononnable fixes in 0.2.9 this week, so please check if they work better for you.

pleerock on 20 Oct 2018

👍3 ❤2

@Kononnable: thanks for your solution! Once released in 0.2.9 we'll try it in our current code base and let know how it went!

@pleerock: thanks for including the fix in 0.2.9 !

leoperria on 22 Oct 2018

Is 0.2.9 coming out soon? I'm also having issues hydrating w/ many result rows per base entity

jakezcruit on 14 Nov 2018

0.2.9 was released 6 days ago. Tag wasn't pushed on github, but npm can install new version.

Kononnable on 19 Nov 2018

Thanks @Kononnable. We are still having 9 second hydration times for a 1 second query (5000 result rows) after updating. Any more plans to investigate this?

jakezcruit on 20 Nov 2018

It's better, but there is still some room for improvements yes

RDeluxe on 20 Nov 2018

Would you be able to provide some examples/reproduction repo with entities that take long time to hydrate? There probably are some more values which can be cached to speed up the process.
Long term goal would be to rewrite some parts of querybuilder, but for now caching values could bring performance benefits without spending much time on the issue.

Kononnable on 22 Nov 2018

We're doing a workaround with separate queries and don't have time now, but
I imagine it would happen with 5000 results from the sql query that get
reduced into 100 instances of the base entity. Each base entity class has 3
properties with a one to many relationship from the base entity to the sub
entities. For example, the base entity is a country and it has many people,
animals, and businesses.

On Thu, Nov 22, 2018 at 4:10 PM Kononnable notifications@github.com wrote:

Would you be able to provide some examples/reproduction repo with entities
that take long time to hydrate? There probably are some more values which
can be cached to speed up the process.
Long term goal would be to rewrite some parts of querybuilder, but for now
caching values could bring performance benefits without spending much time
on the issue.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/typeorm/typeorm/issues/2381#issuecomment-441126981,
or mute the thread
https://github.com/notifications/unsubscribe-auth/Ai-WmBWd9-ZJMIYn0BzfeiCqbHmGsbrTks5uxyDGgaJpZM4Ux5GG
.

jakezcruit on 23 Nov 2018

@jakezcruit
You could check https://github.com/Kononnable/typeorm/tree/2381-performance-test if it makes typeorm more performant for you. It's a simple modification - caching every computed property in JoinAttribute. It's a long shot, but there is not much we can do(in short amount of time) without an example to see what exactly is performing so slow.

To test changes from that branch:

git clone
npm i
npm run package
copy build/package to your project node_modules/typeorm

Kononnable on 2 Dec 2018

@leoperria did you try new version with changes @Kononnable applied since that?

pleerock on 15 Jan 2019

I'm seeing this same problem. 57 seconds hydration time for a 962 ms query returning 2600 rows.

This is on typeorm 0.2.10

chadjaros on 7 Oct 2019

Hi, I work with @leoperria who started this thread. (I’m CEO at Stellwagen Technology, a two year-old start-up with a team of 11. We are huge fans of TypeScript, using it for both the front-end and back-end of our platform).

@pleerock et al: first, thanks all for the great work on TypeOrm - it’s a fantastic tool in the TypeScript ecosystem.

We are thinking about how to proceed with projects over the coming months and would appreciate your thinking related to this issue:

Is there a view yet on how difficult it would be to make hydration of queries like this much faster? (e.g. similar to query time)

Is there a view yet on likely prioritization/timing of this work?

How challenging would it be for a developer not familiar with the internals of TypeOrm to contribute to a solution here?

We have requirements coming up which will involve 10-20x the burden of the query Leo mentions above. The current TypeOrm hydration speed would be too slow for this. We could stop using TypeOrm and write and process queries manually if necessary, but this will be very tedious so will be our option of last resort!

We would appreciate any colour you can provide which could help in us planning our next few months.

Many thanks,
Dan

@leoperria @devison
We are facing the same issue and we are at the point to decide whether to continue with Typeorm or try other ORM (sequelize)

It would be great if you could share what was your decision to overcome this bottleneck

Many thanks

mario-merino on 8 Feb 2020

To anybody that is stumbling upon this issue, hydration of 1600 items with TypeORM takes 6 second, same with sequalize just 300ms...

We have made the decision to migrate to sequelize for the time being

mario-merino on 11 Feb 2020

Hi @mario-merino, just to follow up on your question: we basically sidestepped the problem for now: we had to avoid joins and instead execute a sequence of single table queries. This has given acceptable performance, and worked ok for our use-case. Hi @pleerock - sorry, that's why we didn't follow up on your question above - wasn't easy to test given the significant rearrangement of our code...

devison on 11 Feb 2020

Having the same problem, 12 joins took around 15 seconds in the hydration process, switched to individual queries merging with promises.all and it tooks around 400 ms.

eechava6 on 15 May 2020

I'm having same issue. Typeorm hydration uses a lot of CPU, which in the end causing delays to the whole app. Anyone got solution?

Run query separately and merge with promise works, but for getOne() data. If getMany(), looping individual queries seems not wise.

adlanarifzr on 11 Jun 2020

👍1

Running this code using the version provided (0.2.7), and running the same code using a recent typeORM version (^0.2.25 when I writing this), you can see the hydration problem was solved

TypeORM 0.2.7 version

old

TypeORM ^0.2.25 version

new

netojose on 17 Jul 2020

👍2

Hi, great improvements!

leoperria on 17 Jul 2020

Are these changes present in the 0.3.0 RC? I'm seeing similar excessive hydration time for speedy queries. Working around it by just using getRawMany but this obviously comes at massive type safety penalty.