How to optimize slow TypeORM find() query with multiple nested relations in NestJS?

Question

I am working in the backend of an application. One part of this application (like every application nowadays) is using AI for multiple things. The application's main purpose is building data warehouse L0 layer, DWH layer, data mart, and write-back.

In many cases, we are using AI, and I need to pass the whole structure of the existing tables in staging and in DWH, along with a lot of related metadata for these tables. I implemented this already, but the problem is this: we are using TypeORM and it's a NestJS application. When I wrote a raw repository query, something that looks like this:

typescriptconst fullTable = await repository.find({
  where: { id: In(tableId) },
  relations: {
    coreColumn: true,
    fromReference: {
      toTable: true,
      fromColumn: true,
      toColumn: true,
    },
    toReference: {
      fromTable: true,
      fromColumn: true,
      toColumn: true,
    },
    dwhTable: {
      columns: {
        fkColumn: {
          coreColumn: {
            coreTable: {
              dwhTable: {
                tableQuery: {
                  sourceSystem: true,
                },
                logicalModels: true,
                sourceSystems: true,
                columns: true,
              },
            },
          },
        },
      },
      logicalModels: true,
      sourceSystems: true,
      tableQuery: {
        sourceSystem: true,
      },
    },
    stagingTable: {
      stagingColumns: true,
    },
  },
});

which looks very bad first of all, but that is a part I can deal with. It's extremely slow, which I guess was expected. I will need to use the service that runs these queries on tableId quite often, so I can't have one table taking 10+ seconds. I need to get it down to something really small, like maybe 0.5 seconds for one table, and faster per table if it gets multiple tables.

Now, there are many ways I can optimize this, as I have researched: writing selects in the query to not have everything selected fully, using query builder instead of this pattern is another (I don't think that will help much though), and running direct raw SQL is the last option. I know how to write SQL in general (I have worked as a DWH developer in the past), and I can optimize the raw query for sure in a way that it takes a small amount of time, but I don't know if I can manage to get it down to milliseconds.

I wonder what are some best practices and ways I can handle this type of problem. It seems like something that most people would face at some point. One thing that will not work though is simple caching, because the properties of the table change left and right - they will most likely be completely different for different requests.

Imam Al Arisyi · Accepted Answer · 2025-07-19 08:46:31Z

0

In my personal opinion, this isn't an issue with TypeORM itself, but rather with the database design. You need to identify the bottleneck first. Perhaps you could try optimizing your queries initially. Typically, you'd start by checking how long a query takes to execute directly from your database. If the database itself takes, say, 9 seconds, it's perfectly normal for TypeORM to receive the data in around 10 seconds due to the latency involved in communicating with your NestJS application.

A good first step might be to select only the columns you're actually using. If it's still slow, then consider adding indexes.

answered Jul 19 at 8:46

Imam Al Arisyi

11 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

ne0123. Jul 19 at 13:51

hey I tried and it takes about 5sec which is way better but still too long I wrote some very complicated query using json_agg and I am running it directly which takes about 0.1second for the same case only downside is that people who work on our backend and does not have same knoladge in the databases will have relly hard time changing anything here so I dont relly know how to approach this

Collectives™ on Stack Overflow

How to optimize slow TypeORM find() query with multiple nested relations in NestJS?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related