Loading Related Database Items

Last updated on June 26, 2024.

This guide discusses loading related records while avoiding n+1 queries. These types of queries slow down the loading process because displaying each database item produces a request for another.

Prerequisites

This is an advanced tutorial. To follow it, you must understand basic Insites concepts, the topics in the Tables and Database Fields. For testing, you must be acquainted with measuring the execution time of Liquid code fragments using the filter.

Steps

The sample scenario in this guide walks you through two approaches to demonstrate the difference between the naive and the recommended approach to loading related records.

Important

Use the recommended approach when implementing related records. That is, request data from a related record within a GraphQL query.

Loading related records in this demonstrative scenario is a five-step process:

  • Step 1: Creating the database items
  • Step 2: Loading related record items for each record (not recommended)
  • Step 3: Testing the naive approach
  • Step 4: Requesting data from a related record within the GraphQL query (recommended)
  • Step 5: Comparing the results of the naive approach vs. the recommended approach

Step 1: Creating the database items

The data schema in this scenario consists of two records: and . The programmer record has a property that references a corresponding company record (foreign key).

app/schema/company.yml

app/schema/programmer.yml

The goal is to load data from the table, along with related data in the table, by joining the two tables:

Programmers:

email title company_id
programmer-1@example.com junior 238415
programmer-2@example.com middle 238416
programmer-3@example.com senior 238417
programmer-7@example.com senior 238421

Companies:

id name url
238415 Company #2 company-2.com
238416 Company #3 company-3.com
238417 Company #4 company-4.com
238421 Company #8 company-8.com

Result collection:

programmer.email programmer.title company.name company.url
programmer-1@example.com junior Company #2 company-2.com
programmer-2@example.com middle Company #3 company-3.com
programmer-3@example.com senior Company #4 company-4.com
programmer-7@example.com senior Company #8 company-8.com

Step 2: Loading related record items for each record (not recommended)

Use two GraphQL queries for this approach:

app/graphql/programmers.grapqhl

app/graphql/company.graphql

While displaying the programmer collection — for each programmer record — fetch the related company record using the GraphQL query:

Step 3: Testing the naive approach

Use the time_diff Liquid filter to test the naive approach. The quantitative results are displayed in the table below:

overall
benchmark-n+1 1,236.169
1,223.485
1,418.733
1,216.547
1,314.053
Note

Please visit the documentation about Measuring the Execution Time of Liquid Code to learn how to set up tests for measuring code runtime.

Step 4: Requesting data from a related record within the GraphQL query (recommended)

To avoid n+1 queries, request company data within the GraphQL query. Load related data simultaneously with the programmer collection using a new GraphQL field called .

The argument is required and is used as a foreign key of the company collection. In SQL language, this could look similar to:

Your GraphQL query would look like this after updating:

Update the page. The page looks much simpler now:

app/views/pages/programmers/index.liquid

Step 5: Comparing the results of the naive approach vs. the recommended approach

Test the second approach using the same measurement method. The results are as follows:

overall
benchmark-record 122.504
160.885
134.064
160.051
131.056

Observe that requesting data from the related record within the GraphQL query delivers results around ten times faster than the previous method outlined in Step 3. The n+1 queries in the naive approach slow down the process because displaying each database item produces a request for another database item.

Have a suggestion for this page?

Didn't quite find what you are looking for or have feedback on how we can make the content better then we would love to hear from you. Please provide us feedback and we will get back to you shortly.