Very slow getMany() entity mapping (not caused by SQL query time)

[dustin-rcg]

Issue type:

[x] bug report

Database system/driver:

[x] mssql

TypeORM version:

[x] 0.2.25

Steps to reproduce or a small repository showing the problem:

We are getting very slow performance on getMany() when the number of records returned is over 1000. We see getMany() taking 5-10 SECONDS for only one join and only 9 columns selected for 3000 records. We know it is not caused by the SQL query itself because we can run the same query with getRawMany() and it runs in approx 100 ms.

After TypeORM returns results, our GraphQL resolver processes the same data in approx 100 ms. We cannot understand why TypeORM is taking 2 orders of magnitude longer than the other two components (SQL server, GraphQL server) handling the same data set. The performance is unacceptably slow (slower than the 15 year old system we want to replace). I have removed columns and relations to produce a minimal example. The problem gets worse in the actual system with the additional columns and relations in these models, with TypeORM taking 10 - 20 SECONDS for the data set of 3000 records, even though we are not selecting/joining these extra columns/relations in this query.

Models:

investment

import {
  Column,
  Entity,
  JoinColumn,
  JoinTable,
  ManyToMany,
  ManyToOne,
  PrimaryGeneratedColumn,
  RelationId,
} from "typeorm";

import { Firm } from "./Firm.entity.solutions";

@Entity({ database: "Diligence", name: "Funds" })
export class Investment {
  @PrimaryGeneratedColumn({ name: "FundsID" })
  id: number;

  @Column({
    type: "varchar",
    length: 256,
    nullable: false,
    name: "FundName",
  })
  name: string;

  @Column({ name: "StrategyEnumID" })
  strategyEnumId: number;

  @Column({ name: "Strategy2EnumID" })
  subStrategyEnumId: number;

  @Column({ name: "PrimaryAssignment" })
  primaryAssignment: string;

  @Column({ name: "SecondaryAssignment" })
  secondaryAssignment: string;

  @ManyToOne(
    (type) => Firm,
    (firm) => firm.investments
  )
  @JoinColumn({ name: "FirmsID", referencedColumnName: "id" })
  firm: Firm;

  @Column({ name: "FirmsID" })
  @RelationId((investment: Investment) => investment.firm)
  firmId: number;
}

firm

import {
  Column,
  Entity,
  OneToMany,
  PrimaryGeneratedColumn,
} from "typeorm";

import { Investment } from "./Investment.entity.solutions";

@Entity({ database: "Diligence", name: "Firms" })
export class Firm {
  @PrimaryGeneratedColumn({
    name: "FirmsID",
  })
  id: number;

  @Column({
    type: "varchar",
    length: 256,
    nullable: false,
    name: "Name",
  })
  name: string;

  @OneToMany(
    (type) => Investment,
    (investment) => investment.firm
  )
  investments: Investment[];
}

the query

function getBaseQuery() {
  return getConnection("Diligence")
    .getRepository(Investment)
    .createQueryBuilder("investment")
    .leftJoinAndSelect("investment.firm", "firm");
}

async function time<T>(func: () => Promise<T>) {
  const startTime = Date.now();
  const value = await func();
  const endTime = Date.now();
  console.log("Duration (ms)", endTime - startTime);
  return value;
}

    const query = getBaseQuery();
    await time(() => query.getRawMany());
    await time(() => query.getMany());

The results, from console output:

query: SELECT "investment"."FundsID" AS "investment_FundsID", "investment"."FundName" AS "investment_FundName", "investment"."StrategyEnumID" AS "investment_StrategyEnumID", "investment"."Strategy2EnumID" AS "investment_Strategy2EnumID", "investment"."PrimaryAssignment" AS "investment_PrimaryAssignment", "investment"."SecondaryAssignment" AS "investment_SecondaryAssignment", "investment"."FirmsID" AS "investment_FirmsID", "firm"."FirmsID" AS "firm_FirmsID", "firm"."Name" AS "firm_Name" FROM "Diligence".."Funds" "investment" LEFT JOIN "Diligence".."Firms" "firm" ON "firm"."FirmsID"="investment"."FirmsID"
Duration (ms) 113   <---- getRawMany

query: SELECT "investment"."FundsID" AS "investment_FundsID", "investment"."FundName" AS "investment_FundName", "investment"."StrategyEnumID" AS "investment_StrategyEnumID", "investment"."Strategy2EnumID" AS "investment_Strategy2EnumID", "investment"."PrimaryAssignment" AS "investment_PrimaryAssignment", "investment"."SecondaryAssignment" AS "investment_SecondaryAssignment", "investment"."FirmsID" AS "investment_FirmsID", "firm"."FirmsID" AS "firm_FirmsID", "firm"."Name" AS "firm_Name" FROM "Diligence".."Funds" "investment" LEFT JOIN "Diligence".."Firms" "firm" ON "firm"."FirmsID"="investment"."FirmsID"
Duration (ms) 4399    <---- getMany

[Eraledm]

I'm facing a similar problem, even if I execute with .query() I got 4907.159ms while the query on database client gets it on 35ms, I tried getMany, getRawMany and query but none of those seems to work fast

[puttputt]

We are also experiencing this issue. I thought it was unique to us, and we have solved it but literally paginating everything. Will see if I can reproduce it given your steps here.

[imnotjames]

Duplicate of #2381