#TypeOrm Performance multiple relations

1 messages · Page 1 of 1 (latest)

foggy geyser
#

Hey there,
I am performing a query with multiple relations and the performance is so low,

The query is:

const company = await this.dataSource
      .getRepository(CompanyEntity)
      .createQueryBuilder('company')
      .leftJoinAndSelect('company.financial', 'financial')
      .leftJoinAndSelect('company.default', 'default')
      .leftJoinAndSelect('company.management', 'management')
      .leftJoinAndSelect('company.board', 'board')
      .leftJoinAndSelect('company.shareholder', 'shareholder')
      .leftJoinAndSelect('company.subsidiary', 'subsidiary')
      .leftJoinAndSelect('company.segment', 'segment')
      .leftJoinAndSelect('company.client', 'client')
      .leftJoinAndSelect('company.project', 'project')
      .leftJoinAndSelect('company.projectList', 'projectList')
      .leftJoinAndSelect('company.bid', 'bid')
      .where('company.ratingId = :ratingId', { ratingId })
      .orderBy('company.id', 'DESC')
      .getOne();

Copying the raw sql code from this query in DBEaver tooks about 50ms, but creating the current object tooks about 27segs,

If in CompanyEntity change all the relations with lazy = true and query like:

const company = await this.dataSource
      .getRepository(CompanyEntity)
      .createQueryBuilder('company')
      .where('company.ratingId = :ratingId', { ratingId })
      .orderBy('company.id', 'DESC')
      .getOne();

await Promise.all([
      company.financial,
      company.default,
      company.management,
      company.board,
      company.shareholder,
      company.subsidiary,
      company.segment,
      company.client,
      company.project,
      company.projectList,
      company.bid,
    ]);

This way executing separated queries only took 151ms,

The question is:
¿Anyone knows why take so long when there are so many relations?
¿Any method to get whole data in a single query?

Thanks in advance,

foggy geyser
#

¿Anyone facing this same problem?
¿Anyone has found a solution for a similar problem?

mystic crest
foggy geyser
#

Ey @mystic crest Thanks for the reply,
Do you know how could make a similar query but getting many results avoiding to n+1 queries problem?

Maybe a good approach would be:

const company = await this.dataSource
      .getRepository(CompanyEntity)
      .createQueryBuilder('company')
      .select('company.id')
      .where('company.ratingId = :ratingId', { ratingId })
      .orderBy('company.id', 'DESC')
      .getMany();

const financial = await this.datasource
      .getRepository(CompanyFinancialEntity)
      .createQueryBuilder('financial')
      .whereInIds((r) => r.id)
      .getMany()
#

And then mapping relations to the companyEntity?

company.forEach(c => c.financial = finanacial.filter(f => f.companyId === c.id))

Sorry for asking to much, its the first time getting this issue and dont know how to solve it trying to get the highest performance

hexed drift
#

You hit the spot where a lot of people start realizing that using an ORM has its limitations and that the (object-relational impedance mismatch)[https://en.wikipedia.org/wiki/Object–relational_impedance_mismatch] is a real problem.

Often, it better to have a different read model than write model - and it seems that you also realized that by using a hand-crafted query.

For read queries, it is also not always necessary to morph them back into "entities", because you don't need the entire models, just a few properties.