TypeORM - Multiple DB Calls vs Single DB Call

Introduction

The last article, TypeORM - Query Builder with Subquery demonstrates how you should build complex SQL queries. I hope this helps your work and study. Today's topic is still cases with TypeORM, but the angle is slightly different from the last one.

Which is Better?

Some senior developers mention that you should reduce database calls as much as possible. The potential reasons are:

• The network connection is always a bottleneck. It will help if you reduce the number of DB calls.
• The code-side should not construct, order and filter dataset. The DB is basically faster than the code's data manipulation if optimized by indexes or proper configuration.

In this case, the subqueries with TypeORM might be a good option. On the other hand, some experienced developers say that you should execute SQLs separately and build appropriate datasets in the code. The reasons are:

• The returned dataset should not be large in size. Transferring big data size between DBs and applications might be a bottleneck.
• Inside subqueries might execute without the index's or DB optimization's benefits. You should minimize the records themselves in the subqueries if you want.

Sample Code with TypeORM

Let's review the query on the last article.

Target SQL converted to TypeScript with TypeORM

SELECT 
  cs.course_id as course_id, 
  DATE_FORMAT(
    asses.created_datetime, '%Y-%m-%d'
  ) AS submitted_date, 
  IFNULL(count(cs.courseId), 0) as correct_submission_number, 
  IFNULL(total_exam.number, 0) as total_number 
FROM 
  assessment as asses 
  INNER JOIN submission as sub ON asses.submission_id = sub.id 
  INNER JOIN subject_exam as se ON se.exam_id = sub.exam_id 
  INNER JOIN course_subject as cs ON cs.subject_id = se.subject_id 
  LEFT OUTER JOIN (
    SELECT 
      cs.course_id as course_id, 
      IFNULL(COUNT(cs.course_id), 0) as number 
    FROM 
      course_subject as cs 
      LEFT OUTER JOIN subject_exam as se ON cs.subject_id = se.subject_id
    WHERE
      cs.dept_id = 'abcdefg' 
    GROUP BY 
      cs.course_id
  ) as total_exam ON total_exam.course_id = cs.course_id 
WHERE 
  asses.result = '2' -- = pass
  AND asses.status = '2' -- = submitted
  AND cs.dept_id = 'abcdefg'
GROUP BY 
  cs.course_id, 
  DATE_FORMAT(
    asses.created_datetime, '%Y-%m-%d'
  ) 
ORDER BY 
  DATE_FORMAT(
    asses.created_datetime, '%Y-%m-%d'
  ) asc, 
  cs.course_id asc;

TypeScript with TypeORM

import { getManager } from "typeorm";

// in class 'GetDailyStats'

//Build a subquery to get the total number of exams
const totalExamNumbers: any = getManager().createQueryBuilder()
    .select("cs.courseId", "course_id")
    .addSelect("IFNULL(COUNT(*), 0)", "number")
    .from(CourseSubject, "cs")
    .leftJoin(SubjectExam, "se", "cs.subject_id = se.subject_id")
    .andWhere("cs.dept_id = :deptId", {
        deptId: deptId
    })
    .groupBy("cs.course_id");

//Build a main query with the subquery for stats
const dailyStatsQuery: any = getManager().createQueryBuilder()
    .select("cs.courseId", "courseId")
    .addSelect("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')", "submitted_date")
    .addSelect("IFNULL(COUNT(cs.courseId), 0)", "correct_submission_number")
    .addSelect("IFNULL(total_exam.number, 0)", "total_number")
    .from(Assessment, "asses")
    .innerJoin(Submission, "sub", "asses.submission_id = sub.id")
    .innerJoin(SubjectExam, "se", "se.exam_id = sub.exam_id")
    .innerJoin(CourseSubject, "cs", "cs.subject_id = se.subject_id")
    .leftJoin("(" + totalExamNumbers.getQuery() + ")", "total_exam", "total_exam.course_id = cs.course_id")
    .where("asses.result = :result", {
        result: AssessmentResult.PASS
    })
    .andWhere("asses.status = :status", {
        status: AssessmentStatus.SUBMITTED
    })
    .andWhere("cs.dept_id = :deptId", {
        deptId: deptId
    })
    .groupBy("cs.course_id")
    .addGroupBy("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')")
    .orderBy("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')", "ASC")
    .addOrderBy("cs.course_id", "ASC")
    .setParameters(totalExamNumbers.getParameters())

// Execute the generated query
const dailyStatsRaws = await dailyStatsQuery.getRawMany();

//Convert raws to our appropriate objects 
const dailyStats = dailyStatsRaws.map((s: any) => {
    const item: DailyStatsItem = {
        courseId: s.courseId,
        submittedDate: s.submittedDate,
        correctSubmissions: s.correctSubmissions,
        totalSubmissions: s.totalSubmissions
    };
    return item;
});

return dailyStats;

IMPORTANT: What is setParameters()?
When you set some parameters on a subquery, setting values is not ready to execute with the main query. You explicitly need to set them on your main query before calling getRawMany().

Do we need to call setParameters() for parameters on the main query? No, you do not need it. It is just for separated queries.

HINT: What is IFNULL in MySQL?
The IFNULL() function returns a specified value if the expression is NULL.

HINT: Grouping by DATETIME?
If you want to change the grouping rules by DATETIME columns, please change the DATE_FORMAT parts.

Hourly base: DATE_FORMAT(asses.created_datetime, '%Y-%m-%d %H:00:00')
Daily base: DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')
Monthly base: DATE_FORMAT(asses.created_datetime, '%Y-%m')
Yearly base: DATE_FORMAT(asses.created_datetime, '%Y')

Link Two Objects

Let's think about the way of constructing the dataset in the code. In the above case, dailyStatsQuery needs to link to totalExamNumbers in the code. When you link them, linking two arrays will consume time even though the records are already filtered by deptId. We might think to create a key and value object (Hashmap in Java and Dictionary in C#) because the field, courseId is a unique ID.

import { getManager } from "typeorm";

// in class 'GetDailyStats'

//Calculate the total number of exams
const totalExamNumbers: any = getManager().createQueryBuilder()
    .select("cs.courseId", "course_id")
    .addSelect("IFNULL(COUNT(*), 0)", "number")
    .from(CourseSubject, "cs")
    .leftJoin(SubjectExam, "se", "cs.subject_id = se.subject_id")
    .andWhere("cs.dept_id = :deptId", {
        deptId: deptId
    })
    .groupBy("cs.course_id")
    .getRawMany();

// [1st way] Convert from an array to an associative object array
const totalExamNumberHashMap1 = totalExamNumbers.reduce(
  (c, e) => ({ ...c, [e.course_id]: e }),
  {}
);

// [2nd way] Convert from an array to an associative object array
const totalExamNumberHashMap2 = Object.assign(
  {},
  ...totalExamNumbers.map((e) => ({
    [e.course_id]: e,
  }))
);

//Build a main query with the subquery for stats
const dailyStatsQuery: any = getManager().createQueryBuilder()
    .select("cs.courseId", "courseId")
    .addSelect("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')", "submitted_date")
    .addSelect("IFNULL(COUNT(cs.courseId), 0)", "correct_submission_number")
    .from(Assessment, "asses")
    .innerJoin(Submission, "sub", "asses.submission_id = sub.id")
    .innerJoin(SubjectExam, "se", "se.exam_id = sub.exam_id")
    .innerJoin(CourseSubject, "cs", "cs.subject_id = se.subject_id")
    .where("asses.result = :result", {
        result: AssessmentResult.PASS
    })
    .andWhere("asses.status = :status", {
        status: AssessmentStatus.SUBMITTED
    })
    .andWhere("cs.dept_id = :deptId", {
        deptId: deptId
    })
    .groupBy("cs.course_id")
    .addGroupBy("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')")
    .orderBy("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')", "ASC")
    .addOrderBy("cs.course_id", "ASC")

// Execute the generated query
const dailyStatsRaws = await dailyStatsQuery.getRawMany();

//Convert raws to our appropriate objects 
const dailyStats = dailyStatsRaws.map((s: any) => {
    const item: DailyStatsItem = {
        courseId: s.courseId,
        submittedDate: s.submittedDate,
        correctSubmissions: s.correctSubmissions,
        totalSubmissions: totalExamNumberHashMap1[s.courseId] 
        ? totalExamNumberHashMap1[s.courseId].number : 0
    };
    return item;
});

return dailyStats;

IMPORTANT:
I showed you the two ways of key & value object conversion by reduce and Object.assign. However, I have not considered the performance. If you give me better solutions, I appreciate :)

Potential Cases

Which cases should we select either a single DB call or multiple DB calls?

Select Multiple DB Calls

1. Cloud Database - Many enterprises utilize cloud-based DBs, which provide scalability, availability, etc. instead of DBs managed by ourselves. However, for instance, if your application accesses them via a public IP OR it is physically far from your apps, you might consider the data size. Multiple DB calls with small data might be faster than a single DB call.
2. Not Perform with DB Benefits - As I mentioned, some cases such as subqueries might not receive the index's or DB optimization's benefits.

Select Single DB Call

1. Simply Avoid Multiple Calls - This is not just for the communication between your applications and databases. Of course, your applications should not communicate any services as much as possible because this via the network is a bottleneck.
2. Complex Dataset - In the above case, linking totalExamNumbers to others should not be big trouble because it has a unique ID. However, if it needs to link to objects without specific unique IDs, completing the data operations by DBs would be better. You might need proper indexes, stored procedures, or views, which support complex data manipulations.
3. Send Raw Data or Flat Tables - Your backends (~= APIs) are utilized for multiple services such as web applications or data analysis tools etc. For some BI or data analysis tools, flat tables as star schema are utilized. For example with the above case, BI tools (= receivers who request the dataset) expect the dataset is a static data source's table but it actually comes from the SQL select results, which are dynamically generated and are stored somewhere such as memory data stores.

Quick Performance Check

If you want to check the performance of your queries without the additional work, console.time("Function 1") and console.timeEnd("Function 2") might help you.

// [1st way] Convert from an array to an associative object array
console.time("totalExamNumberHashMap1");
const totalExamNumberHashMap1 = totalExamNumbers.reduce(
  (c, e) => ({ ...c, [e.course_id]: e }),
  {}
);
console.timeEnd("totalExamNumberHashMap1")

// [2nd way] Convert from an array to an associative object array
console.time("totalExamNumberHashMap2");
const totalExamNumberHashMap2 = Object.assign(
  {},
  ...totalExamNumbers.map((e) => ({
    [e.course_id]: e,
  }))
);
console.timeEnd("totalExamNumberHashMap2")

Conclusion

TypeORM is a powerful tool to implement the Code-First approach. On the other hand, we have to deeply think about how it should be utilized effectively depends on your environment and situation.

Bio

When I was 30 years old, I went to Australia for changing my career on a Working Holiday visa. I graduated from University of Sydney with a Bachelor of Computer Science and Technology. During the period, I also worked as a Fraud Prevention Analyst at Amex in Sydney, Australia (yes, it was super busy. I went to my uni during the day and worked at night...)

After graduation, I worked as a C#/.Net developer for an Australian small business for 5 years. Now, I came back to Japan and work as a TypeScript/Go/React developer for a Japanese security start-up company.

I love learning new fields which is a big challenge. I am happy if you support me to accelerate the improvement of my skill/knowledge. Please feel free to contact me if you are interested in my unique career.

Thanks,