Query timeout when fetching more than 3 records at a time

The query does have a lot of joins in it - is this just too much for one query on gadget?

{
  "select": {
    "operation": true,
    "productId": true,
    "product": {
      "id": true,
      "title": true,
      "handle": true,
      "body": true,
      "vendor": true,
      "status": true,
      "tags": true,
      "shop": {
        "domain": true
      },
      "options": {
        "edges": {
          "node": {
            "id": true,
            "name": true,
            "position": true,
            "values": true
          }
        }
      },
      "catalogs": {
        "edges": {
          "node": {
            "id": true,
            "title": true
          }
        }
      },
      "metafields": {
        "edges": {
          "node": {
            "namespace": true,
            "key": true,
            "value": true
          }
        }
      },
      "media": {
        "edges": {
          "node": {
            "id": true,
            "image": true
          }
        }
      },
      "variants": {
        "edges": {
          "node": {
            "id": true,
            "title": true,
            "price": true,
            "compareAtPrice": true,
            "selectedOptions": true,
            "inventoryQuantity": true,
            "position": true,
            "sku": true,
            "barcode": true,
            "priceListPrice": {
              "edges": {
                "node": {
                  "price": true,
                  "priceList": {
                    "catalog": {
                      "id": true,
                      "title": true
                    }
                  }
                }
              }
            },
            "metafields": {
              "edges": {
                "node": {
                  "namespace": true,
                  "key": true,
                  "value": true
                }
              }
            }
          }
        }
      }
    }
  },
  "first": 5
}

{
  "select": {
    "operation": true,
    "productId": true,
    "product": {
      "id": true,
      "title": true,
      "handle": true,
      "body": true,
      "vendor": true,
      "status": true,
      "tags": true,
      "shop": {
        "domain": true
      },
      "options": {
        "edges": {
          "node": {
            "id": true,
            "name": true,
            "position": true,
            "values": true
          }
        }
      },
      "catalogs": {
        "edges": {
          "node": {
            "id": true,
            "title": true
          }
        }
      },
      "metafields": {
        "edges": {
          "node": {
            "namespace": true,
            "key": true,
            "value": true
          }
        }
      },
      "media": {
        "edges": {
          "node": {
            "id": true,
            "image": true
          }
        }
      },
      "variants": {
        "edges": {
          "node": {
            "id": true,
            "title": true,
            "price": true,
            "compareAtPrice": true,
            "selectedOptions": true,
            "inventoryQuantity": true,
            "position": true,
            "sku": true,
            "barcode": true,
            "priceListPrice": {
              "edges": {
                "node": {
                  "price": true,
                  "priceList": {
                    "catalog": {
                      "id": true,
                      "title": true
                    }
                  }
                }
              }
            },
            "metafields": {
              "edges": {
                "node": {
                  "namespace": true,
                  "key": true,
                  "value": true
                }
              }
            }
          }
        }
      }
    }
  },
  "first": 5
}

18 Replies

Chocci_Milk•3mo ago

Hello, I think that the number of related records that you're fetching could definitely be an issue. However, I would like to investigate this some more. Could you please share the URL of the application?

kalenjordanOP•3mo ago

https://tapestry-stitch.gadget.app/edit/dev-boden/settings

Chocci_Milk•3mo ago

What model are you running the find many on?

kalenjordanOP•3mo ago

queuedProductJob - https://tapestry-stitch.gadget.app/edit/dev-kalen/files/_actions/bloomPatchCatalog.mjs?startLineNumber=140&startColumn=0&endLineNumber=141&endColumn=0

Gadget Auth - Loading

Gadget Auth

Chocci_Milk•3mo ago

Hey, talked to the team after doing some research and it looks like there might be an issue here (our end). Going to do a bit more investigation and report it to the team for them to action. This should work. Do you know how to get yourself unstuck for now or whould you like some guidance? Maybe another way to approach your issue?

Unknown User•3mo ago

Message Not Public

[Gadget] Kyle•3mo ago

hey folks gunna take a look into this

kalenjordanOP•3mo ago

The queue table is a lot smaller now and it's running much faster

Unknown User•3mo ago

Message Not Public

kalenjordanOP•3mo ago

oh interesting

Unknown User•3mo ago

Message Not Public

kalenjordanOP•3mo ago

while I have you - one thing that concerned me about the shopify sync to gadget is that it missed like a fairly small number of records out of 23k products and 230k variants - I manually did a full sync against products/variants and it resolved. but there weren't any permanently failed queued jobs for those missing products - is that expected?

Unknown User•3mo ago

Message Not Public

Chocci_Milk•3mo ago

Hey Kalen, sorry for the late reply. We're still investigating the sync issue and we're trying to gather as much information as possible before filling you in I'm going to continue in the other thread

Unknown User•3mo ago

Message Not Public

kalenjordanOP•3mo ago

The query is timing out again: GGT_DATABASE_OPERATION_TIMEOUT: One operation in the backend database took too long to execute and was cancelled. I have made a number of changes since June 6 but I don't think there should be anything that has caused the query to be an order of magnitude heavier. It's mostly a couple of json fields that have been added in. On the tapestry-stitch-staging store which has 1k products it can still fetch batches of 250. On the point-carre-development shop (13k products), it's timing out even with a batch size of 50. It seems to work with a batch size of 20. This is the where the query is being made: https://tapestry-stitch.gadget.app/edit/staging/files/api/actions/bloomPutCatalog.mjs?startLineNumber=224&startColumn=0&endLineNumber=225&endColumn=0 Here are the query options: https://gist.github.com/kalenjordan/5ad36d936a2f6a5e229725d805800b4a Okay now it's running faster again - sometimes times out with 250, sometimes works. With a batch size of 100 its fetching them consistenly in under 4 seconds. Are we still on the prod infrastructure option for staging?

Unknown User•2mo ago

Message Not Public

kalenjordanOP•2mo ago

thanks no we're in decent shape in prod now with this

Gaming

Programming

Query timeout when fetching more than 3 records at a time

Did you find this page helpful?