Opening the Review tab is very slow since its redesign

[Ellpeck]

Since the redesigned Review tab (where more than two columns can be displayed), which I think was a part of v1.20, opening the tab is incredibly slow, at least using Postgres 15 on my machine. It also seems as though, the higher the percentage of reviewed (vs. unreviewed) strings there are in a language, the longer the Review tab takes to load if that language is selected.

This wasn't the case at all before the redesign, where the Review tab loaded pretty much as quickly as all other tabs in the project.

To check what was causing this issue, I turned on the logging option in Postgres that displays slow queries, and the query that (I assume) grabs all the information for the Review tab takes over 30 seconds on average in one of my projects. Here's a typical log of a slow query:

duration: 29598.588 ms  execute ecto_5156: SELECT DISTINCT ON (t0."key") t0."key", t0."document_id", array_agg(distinct(s2."id")) FROM "translations" AS t0 INNER JOIN "revisions" AS r3 ON t0."revision_id" = r3."id" INNER JOIN "projects" AS p1 ON r3."project_id" = p1."id" LEFT OUTER JOIN (SELECT st0."id" AS "id", st0."key" AS "key", st0."proposed_text" AS "proposed_text", st0."corrected_text" AS "corrected_text", st0."conflicted_text" AS "conflicted_text", st0."conflicted" AS "conflicted", st0."removed" AS "removed", st0."comments_count" AS "comments_count", st0."file_comment" AS "file_comment", st0."file_index" AS "file_index", st0."value_type" AS "value_type", st0."plural" AS "plural", st0."locked" AS "locked", st0."translated" AS "translated", st0."placeholders" AS "placeholders", st0."document_id" AS "document_id", st0."revision_id" AS "revision_id", st0."version_id" AS "version_id", st0."source_translation_id" AS "source_translation_id", st0."inserted_at" AS "inserted_at", st0."updated_at" AS "updated_at" FROM "translations" AS st0 INNER JOIN "revisions" AS sr2 ON st0."revision_id" = sr2."id" INNER JOIN "projects" AS sp1 ON sr2."project_id" = sp1."id" WHERE (st0."version_id" IS NULL) AND (sp1."id" = $1) AND (st0."revision_id" = ANY($2)) AND (st0."removed" = FALSE) AND (st0."locked" = FALSE)) AS s2 ON (s2."revision_id" = ANY($3) AND (s2."key" = t0."key")) AND (s2."document_id" = t0."document_id") WHERE (t0."removed" = FALSE) AND (t0."locked" = FALSE) AND (t0."conflicted" = TRUE) AND (t0."version_id" IS NULL) AND (p1."id" = $4) GROUP BY t0."key", t0."document_id" HAVING (array_agg(distinct(s2."conflicted")) != ARRAY[FALSE]) ORDER BY t0."key" LIMIT $5 OFFSET $6

Additionally, here are the stats for the project that the query is made from, if those are of any use for fixing this issue!

Master language
English – en

Translations (24)
Chinese – zh-Hant • 0.97% reviewed
Spanish – es • 99.69% reviewed
Afrikaans – af • 0.26% reviewed
Chinese Simplified – zh-Hans • 93.20% reviewed
Czech – cs • 78.18% reviewed
Dutch – nl • 39.69% reviewed
French – fr • 91.03% reviewed
German – de • 100% reviewed
Greek – el • 12.62% reviewed
Indonesian – id • 0.17% reviewed
Italian – it • 95.32% reviewed
Japanese – ja • 65.02% reviewed
Korean – ko • 77.76% reviewed
Lithuanian – lt • 0% reviewed
Norwegian – no • 10.85% reviewed
Polish – pl • 80.27% reviewed
Portuguese – pt-PT • 0% reviewed
Portuguese, Brazilian – pt-BR • 37.01% reviewed
Romanian – ro • 0% reviewed
Russian – ru • 23.04% reviewed
Swedish – sv-SE • 0.03% reviewed
Tagalog – tl • 8.25% reviewed
Thai – th • 46.77% reviewed
Turkish – tr • 79.30% reviewed

Documents (2)
Localization TinyLife/Content/Localization/en.json
StoreText Media/StoreText/en.json

Strings (87550)
✓ Reviewed: 39903
× In review: 47647
✎ Translated: 85370

I really love the redesigned Review tab, so I hope there's a simple fix for this issue. Either way, thanks so much for all your hard work on Accent!! ❤️

[simonprev]

Oh wow a project with ~85 000 strings 😮 I did not do any load test on the UI, I will try to tackle this soon to improve the performance 😄

[Ellpeck]

This is the community localization page for my project Tiny Life (don't mean to do some big advertising thing, but it seems like you're interested in the context of the project!!) :) I really love using Accent, it's by far the best open source localization software that isn't super bloated & high memory usage (imo) ❤️

[Ellpeck]

Hi! Are there any updates on this? This issue makes it very difficult to work with the Review tab, unfortunately ☹️

[simonprev]

I made some improvement in the SQL query in v1.23.5

What can of database do you use to run Accent?

[Ellpeck]

I use Postgres for Accent at the moment! I'll try out the new version and let you know how it goes :)

[Ellpeck]

Unfortunately, the Review tab is still very slow, even on v1.23.5.

Here's another log of a slow query after upgrading Accent:

duration: 28267.970 ms  execute ecto_931: SELECT DISTINCT ON (t0."key") t0."key", t0."document_id", array_agg(distinct(s2."id")) FROM "translations" AS t0 INNER JOIN "revisions" AS r3 ON t0."revision_id" = r3."id" INNER JOIN "projects" AS p1 ON r3."project_id" = p1."id" LEFT OUTER JOIN (SELECT st0."id" AS "id", st0."key" AS "key", st0."proposed_text" AS "proposed_text", st0."corrected_text" AS "corrected_text", st0."conflicted_text" AS "conflicted_text", st0."conflicted" AS "conflicted", st0."removed" AS "removed", st0."comments_count" AS "comments_count", st0."file_comment" AS "file_comment", st0."file_index" AS "file_index", st0."value_type" AS "value_type", st0."plural" AS "plural", st0."locked" AS "locked", st0."translated" AS "translated", st0."placeholders" AS "placeholders", st0."document_id" AS "document_id", st0."revision_id" AS "revision_id", st0."version_id" AS "version_id", st0."source_translation_id" AS "source_translation_id", st0."inserted_at" AS "inserted_at", st0."updated_at" AS "updated_at" FROM "translations" AS st0 INNER JOIN "revisions" AS sr2 ON st0."revision_id" = sr2."id" INNER JOIN "projects" AS sp1 ON sr2."project_id" = sp1."id" WHERE (st0."version_id" IS NULL) AND (sp1."id" = $1) AND (st0."revision_id" = ANY($2)) AND (st0."removed" = FALSE) AND (st0."locked" = FALSE)) AS s2 ON (s2."revision_id" = ANY($3) AND (s2."key" = t0."key")) AND (s2."document_id" = t0."document_id") WHERE (t0."removed" = FALSE) AND (t0."locked" = FALSE) AND (t0."conflicted" = TRUE) AND (t0."version_id" IS NULL) AND (p1."id" = $4) GROUP BY t0."key", t0."document_id" HAVING (array_agg(distinct(s2."conflicted")) != ARRAY[FALSE]) ORDER BY t0."key" LIMIT $5 OFFSET $6