Queries timing out when pulling data from one table

[ngoudreau]

We have a table that has over 100k rows. Any time we pull data from that table using a query, it takes significantly longer than any other query and will eventually time out around 50% of the time.

Some days are slow, but functional, and other days are nearly inoperable.

[MiguelOrtiz]

Wow, I've struggled with queries returning a couple of thousands, I can't imagine with 100k....

Any chance you're able to ask users to input filters of what they want to see? I'd imagine they dont want the 100k rows. This could make the query more efficient

[ngoudreau]

The table has 100k rows in it -- we're not returning 100k rows in the query!

The query isn't anything complicated tbh.

[ZeroCodez]

Hey @ngoudreau ,
I understand your problem , You have to use Server-side pagination in table and update query for server side pagination and increase timeout timing to 120000.

[polly]

Hi @ngoudreau

That does sound like a big table! I would love to direct you to our documentation for some best practices: Query performance best practices | Retool Docs

A couple call-outs:

• Like @ZeroCodez suggested, consider using server-side pagination, as described in the Retrieve only required data from large data sets section.
• We also have this documentation on Query performance, which might give you some additional tips and insight. This doc recommends using the Query Timeline in the debug toolbar to visualize and identify long query run times.
• Are you on Retool Cloud or Retool Self-hosted? Cloud has a max query time of 2 minutes but self hosted customers can change the Retool configured timeout setting - Advanced query options | Retool Docs

[Tess]

@ngoudreau let us know if you're still working through this! Happy to take a closer look