Vengala Vinay

Having 9+ Years of Experience in Software Development

Home » Fixing Slow Largest Contentful Paint (LCP) caused by unoptimized database queries in Legacy Shopify Codebases Without Breaking API Contracts

Fixing Slow Largest Contentful Paint (LCP) caused by unoptimized database queries in Legacy Shopify Codebases Without Breaking API Contracts

Diagnosing Database-Bound LCP Bottlenecks in Shopify

Slow Largest Contentful Paint (LCP) is a critical performance metric, directly impacting user experience and SEO rankings. In legacy Shopify codebases, particularly those with extensive custom themes or app integrations, database query performance is a frequent culprit. These issues often manifest as lengthy waits for product data, collection details, or customer information required to render the LCP element. The challenge lies in identifying and optimizing these queries without disrupting existing API contracts or introducing regressions.

The first step is precise identification. Shopify’s Liquid templating language, while powerful, can obscure the underlying data fetching mechanisms. We need to leverage Shopify’s built-in tools and external profiling to pinpoint the exact queries causing the delay.

Leveraging Shopify Admin and Browser Developer Tools

Shopify’s Admin interface provides some basic performance insights, but for granular detail, browser developer tools are indispensable. Specifically, the Network tab in Chrome DevTools (or Firefox Developer Tools) is your primary weapon.

Workflow:

  • Navigate to the product or collection page exhibiting slow LCP.
  • Open your browser’s Developer Tools (F12).
  • Select the “Network” tab.
  • Refresh the page (Ctrl+R or Cmd+R).
  • Filter requests by “XHR” or “Fetch” to isolate API calls.
  • Observe the “Time” column for requests that take an unusually long time. Pay close attention to requests that occur just before or during the rendering of the LCP element (e.g., the main product image, title, or price).
  • Examine the “Initiator” column to trace back which JavaScript or Liquid template initiated the slow request.

While this identifies slow *network* requests, it doesn’t always reveal the *database query* responsible. For that, we need to look at the server-side execution. Shopify’s Liquid environment doesn’t offer direct SQL query logging in the same way a traditional backend framework might. However, we can infer database activity by analyzing the Liquid objects and their retrieval times.

Analyzing Liquid Object Fetch Times

Many Liquid objects (like product, collection, all_products, articles) trigger database reads. If a Liquid template iterates over a large collection of products or performs multiple complex lookups within a single render cycle, performance can degrade significantly. We can use Liquid’s `now` filter to time specific sections of our templates.

Consider a scenario where a custom product page template fetches related products or accessories. A naive implementation might look like this:

Example: Inefficient Related Products Fetch

{% assign start_time = 'now' | date: '%s.%3N' %}

<h2>Related Products</h2>
<div class="related-products">
  {% for related_product in product.metafields.custom.related_products %}
    {% assign related_prod = all_products[related_product.value] %}
    {% if related_prod %}
      <div class="related-product-item">
        <a href="{{ related_prod.url }}">
          <img src="{{ related_prod.featured_image | img_url: 'small' }}" alt="{{ related_prod.featured_image.alt }}">
          <h3>{{ related_prod.title }}</h3>
        </a>
      </div>
    {% endif %}
  {% endfor %}
</div>

{% assign end_time = 'now' | date: '%s.%3N' %}
{% assign elapsed_time = end_time | minus: start_time %}

<p>Related products fetch took: {{ elapsed_time }} seconds</p>

The `all_products[handle]` lookup within the loop is particularly problematic. If `product.metafields.custom.related_products` contains many handles, this will result in numerous individual lookups, each potentially hitting the database. The `date` filter with `’%s.%3N’` provides microsecond precision for timing.

Optimizing Database Access Patterns

The key to optimization is to reduce the number of database hits and ensure those hits are as efficient as possible. This often involves batching requests or using more efficient Liquid objects.

1. Batching Product Lookups

Instead of looking up products one by one in a loop, we can leverage Shopify’s `collections` object or, if dealing with a fixed set of handles, construct a query that fetches them more efficiently. For metafields containing product handles, a common pattern is to use the `for` loop with `all_products` which is inefficient. A better approach is to fetch all necessary products at once if possible, or to use a pre-defined collection.

Optimized Approach (using a predefined collection):

If your “related products” are managed via a dedicated collection (e.g., “Related to Product X”), this is far more efficient.

{% assign start_time = 'now' | date: '%s.%3N' %}

<h2>Related Products</h2>
<div class="related-products">
  {% assign related_collection_handle = product.metafields.custom.related_collection_handle %}
  {% if related_collection_handle != blank %}
    {% assign related_collection = collections[related_collection_handle] %}
    {% if related_collection.products.size > 0 %}
      {% for related_prod in related_collection.products limit: 4 %}
        <div class="related-product-item">
          <a href="{{ related_prod.url }}">
            <img src="{{ related_prod.featured_image | img_url: 'small' }}" alt="{{ related_prod.featured_image.alt }}">
            <h3>{{ related_prod.title }}</h3>
          </a>
        </div>
      {% endfor %}
    {% endif %}
  {% endif %}
</div>

{% assign end_time = 'now' | date: '%s.%3N' %}
{% assign elapsed_time = end_time | minus: start_time %}

<p>Related products fetch took: {{ elapsed_time }} seconds</p>

This approach leverages the `collections` object, which is optimized for fetching multiple products within a collection in a single database operation. The `limit: 4` is crucial to prevent fetching an excessive number of related items, which would also impact performance.

2. Caching Expensive Data

For data that doesn’t change frequently, caching can be a lifesaver. Shopify’s Liquid doesn’t have a built-in, robust caching mechanism for arbitrary data. However, you can simulate caching using metafields or by leveraging external services if you have a more advanced setup (e.g., headless Shopify with a custom backend).

A simple, albeit limited, form of caching can be achieved by storing computed results in a metafield. This is best suited for data that is expensive to compute but rarely changes.

Example: Caching Computed Pricing Data

{% assign start_time = 'now' | date: '%s.%3N' %}

{% comment %}
  Assume 'calculate_custom_pricing' is a complex snippet that performs
  multiple lookups and calculations.
{% endcomment %}

{% assign cached_pricing = product.metafields.custom.computed_pricing %}
{% assign pricing_data = null %}

{% if cached_pricing != blank %}
  {% assign pricing_data = cached_pricing | split: '|' %}
{% else %}
  {% comment %}
    If not cached, compute it. This part is the expensive operation.
    In a real scenario, this might involve calling a complex snippet.
    For demonstration, we'll simulate it.
  {% endcomment %}
  {% assign computed_price = product.price | times: 1.15 %}
  {% assign computed_compare_at_price = product.compare_at_price | times: 1.15 %}

  {% comment %}
    Format and store in a simple string for metafield.
    This is a basic example; JSON might be better for complex data.
  {% endcomment %}
  {% assign pricing_data_string = computed_price | append: '|' | append: computed_compare_at_price %}

  {% comment %}
    Save to metafield. This write operation itself has a cost,
    but it's amortized over subsequent reads.
    Note: Metafield writes can be slow and have limits.
    This is best for data that changes infrequently.
  {% endcomment %}
  {% comment %}
    {%- assign metafield_update = 'custom.computed_pricing' -%}
    {%- assign metafield_value = pricing_data_string -%}
    {%- comment -%}
      In a real theme, you'd use Shopify's metafield API or
      a theme editor setting to manage this. Direct Liquid writes
      to metafields are not standard for performance optimization
      in this manner without specific app support or backend logic.
      This example illustrates the *concept* of caching.
    {%- endcomment -%}
  {% endcomment %}

  {% comment %}
    For demonstration, we'll just use the computed values directly
    if not cached.
  {% endcomment %}
  {% assign pricing_data = pricing_data_string | split: '|' %}
{% endif %}

{% assign final_price = pricing_data[0] | divided_by: 1 %}
{% assign final_compare_at_price = pricing_data[1] | divided_by: 1 %}

<div class="product-pricing">
  <span class="price">{{ final_price | money }}</span>
  {% if final_compare_at_price > final_price %}
    <span class="compare-at-price">{{ final_compare_at_price | money }}</span>
  {% endif %}
</div>

{% assign end_time = 'now' | date: '%s.%3N' %}
{% assign elapsed_time = end_time | minus: start_time %}

<p>Pricing data fetch/compute took: {{ elapsed_time }} seconds</p>

Important Caveat: Directly writing to metafields within Liquid for caching purposes is generally discouraged due to performance implications of the write operation itself and potential API limits. A more robust solution would involve a custom backend service (e.g., a Shopify App) that computes and stores these values in a dedicated cache (like Redis) or updates metafields asynchronously. However, the principle of checking for a cached value before recomputing remains valid.

3. Minimizing `all_products` and `all_collections` Usage

These global objects are convenient but can be extremely inefficient on large stores. `all_products` can iterate over thousands of products, and each lookup within a loop (`all_products[handle]`) can be a separate database query. Similarly, `all_collections` can be slow if not used judiciously.

Instead of:

{% for product in all_products limit: 10 %}
  {% comment %} ... process product ... {% endcomment %}
{% endfor %}

Prefer:

{% comment %}
  If you need a specific subset, use a collection.
  If you need a specific product, use the product handle directly.
{% endcomment %}

{% assign featured_collection = collections['featured-products'] %}
{% for product in featured_collection.products limit: 10 %}
  {% comment %} ... process product ... {% endcomment %}
{% endfor %}

If you absolutely need to iterate over a large number of products and filter them, consider doing this filtering server-side (via a Shopify App or custom backend) and passing the results to Liquid, or using JavaScript on the client-side for less critical LCP elements.

Refactoring API-Bound Logic

Many legacy themes or apps might have custom JavaScript that makes AJAX calls to fetch data. If these calls are slow, they are likely due to inefficient backend logic or database queries on the Shopify side (e.g., within a Shopify App’s API endpoint). The principles of optimizing database access remain the same.

Example: Slow AJAX Endpoint (Conceptual Node.js/Express for a Shopify App)

const express = require('express');
const router = express.Router();
const shopifyApi = require('../shopify-api'); // Your Shopify API client

// Endpoint to get related products
router.get('/products/:productId/related', async (req, res) => {
  const { productId } = req.params;
  const session = await shopifyApi.session.fromURL(req.originalUrl, req.headers.cookie);

  try {
    // Inefficient: Fetching product details and then iterating to find related ones
    const product = await shopifyApi.product.get(productId);
    const relatedProductHandles = product.metafields.find(mf => mf.key === 'related_products')?.value || [];

    // VERY INEFFICIENT: Multiple individual product lookups
    const relatedProducts = [];
    for (const handle of relatedProductHandles) {
      const relatedProd = await shopifyApi.product.getByHandle(handle);
      if (relatedProd) {
        relatedProducts.push({
          id: relatedProd.id,
          title: relatedProd.title,
          url: `/products/${relatedProd.handle}`,
          imageUrl: relatedProd.images[0]?.src
        });
      }
    }

    res.json(relatedProducts);
  } catch (error) {
    console.error("Error fetching related products:", error);
    res.status(500).send("Error fetching related products");
  }
});

module.exports = router;

Optimized Endpoint (using GraphQL for batching):

const express = require('express');
const router = express.Router();
const shopifyApi = require('../shopify-api');

// Endpoint to get related products (Optimized with GraphQL)
router.get('/products/:productId/related', async (req, res) => {
  const { productId } = req.params;
  const session = await shopifyApi.session.fromURL(req.originalUrl, req.headers.cookie);

  try {
    // Fetch product and its related product handles in one go
    const productData = await shopifyApi.graphql(`
      query GetProductAndRelatedHandles($id: ID!) {
        product(id: $id) {
          id
          handle
          metafield(namespace: "custom", key: "related_products") {
            value
          }
        }
      }
    `, { id: productId });

    const relatedProductHandles = JSON.parse(productData.product.metafield?.value || '[]');

    if (!relatedProductHandles || relatedProductHandles.length === 0) {
      return res.json([]);
    }

    // Use GraphQL to fetch all related products in a single request
    const relatedProductsQuery = `
      query GetRelatedProducts($handles: [String!]!) {
        products(first: ${relatedProductHandles.length}, query: "handle:(${relatedProductHandles.join(' OR handle:')})") {
          edges {
            node {
              id
              title
              handle
              images(first: 1) {
                edges {
                  node {
                    src
                  }
                }
              }
            }
          }
        }
      }
    `;

    const relatedProductsData = await shopifyApi.graphql(relatedProductsQuery, { handles: relatedProductHandles });

    const relatedProducts = relatedProductsData.products.edges.map(({ node }) => ({
      id: node.id,
      title: node.title,
      url: `/products/${node.handle}`,
      imageUrl: node.images.edges[0]?.node?.src
    }));

    res.json(relatedProducts);
  } catch (error) {
    console.error("Error fetching related products:", error);
    res.status(500).send("Error fetching related products");
  }
});

module.exports = router;

The optimized version uses Shopify’s GraphQL Admin API. First, it fetches the product and its related product handles. Then, it constructs a single GraphQL query to retrieve all necessary related products using a `handle:` filter. This drastically reduces the number of API calls and database interactions compared to iterating and fetching products individually.

Testing and Monitoring

After implementing optimizations, rigorous testing is essential. Use tools like:

  • Google PageSpeed Insights: To measure LCP and other Core Web Vitals.
  • WebPageTest: For detailed waterfall charts and performance analysis from various locations.
  • Browser Developer Tools (Network Tab): To re-verify request timings and identify any new bottlenecks.
  • Shopify’s Theme Check: To ensure code quality and identify potential issues.

Implement ongoing monitoring. Set up alerts for LCP degradation or increased server response times. Regularly review performance reports to catch regressions early. For critical LCP elements, consider implementing client-side rendering fallbacks or progressive enhancement strategies if server-side rendering remains a bottleneck.