Query Performance is getting worse on average

Queries and Resources
1

Been seeing more query performance issues where even simple SQL queries might take 10 seconds to perform and load. Also seeing more timeouts where the query fails to execute entirely.

Even had a JSON SQL query take several seconds where it would normally take milliseconds.

Seeing this on my end as well as with a client in a different state.

Reload page or rerun the query and it's fine again so it is very intermittent.

1 Like
2

hi @bradlymathews thank you for reporting this!

Would it be possible to get a chrome performance profile and/or a screenshot of the in app query stats (stats from hovering over query after run)?

in-app-query-stats

3

I'll do that as I encounter them. Is there any way for my client to do that in a published app? I assume no, but worth asking.

4

yeah there's no way to view the in-app query stats in a published app but your client can get a chrome performance profile by following the steps here: Analyze runtime performance - Chrome Developers

5

Hey Brad! Are you still experiencing this performance issue?

6

I get the occasional failed query and extra latency. Frankly you just get used to it.

But nothing egregious of late.

7

Got it. Passed along to the engineer looking into this!

8

Glad nothing egregious. We've made fixes recently, and please continue to reach out if things get worse!

9

Hey @bradlymathews -

did you ever get to the bottom of this? We've been battling performance issues for months and the magic number is 10 seconds.

10

Oof, that is a lot!

I never really did directly, just learned a bit more about optimizing within Retool's abilities and peccadilloes.

  1. Optimize your own queries best you can:
    Make sure your performance issue is not due to your back end. Optimize your indexes and all the normal stuff.
  2. Combine multiple queries so you are doing fewer trips to the server:
    For example I fetch all of my lookup tables in a single query: [how-to] Return multiple unrelated tables in one database call . You can also use upserts, use stored procedures or simply run more than one sql query at a time as your needs see fit.
  3. Have the app do a little less - do not cram your entire system into one app (that's an exaggeration of course!)
    I have tended to make my apps bigger so there was less page loading and thus fewer queries to the server. But as your app gets more components and even handlers, etc, it does get a little sluggish over all, so find your sweet spot. I am dialing back a bit and aiming for simpler apps.
  4. Avoid doggy components.
    For instance ListView and the old table will kill you if you are not really careful! it will seems like your queries are slow when it is the app. Hopefully the new ListView solves this like the new Table did for its ancestor.
2 Likes
11

Thanks so much for sharing @bradlymathews! This is all great advice - I do expect the new listView to help with performance :crossed_fingers: :grin:

I think this post has some helpful guidance as well

This docs page has a helpful breakdown of each step in the query performance

12

@bradlymathews thanks for this post (and your many other useful posts!) Solid advice all around that we will use.

Regarding suggestion #1 - we assumed that the issue was due to our back end, but we couldn't figure it out despite lots of tuning. The tuning was worth it, and resulted in better apps, but we still get sluggishness and hangs. Very often the query runs for 10s.

We've learned restarting the docker containers (we're self hosted) clears the issue up for 24-48 hours before it returns.

I'm working with Retool support (they've been great, but it's a tough problem.) When we get it fixed I'll report back.

1 Like