Loading Related Database Items

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.

• Development Workflow
• Get Started
• Database Fields and Tables
• Measuring Execution Time of Liquid Code

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.

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:

Companies:

Result collection:

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:

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:

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.