opencollective icon indicating copy to clipboard operation
opencollective copied to clipboard
verified publisher icon opencollective

Expense Security Checks: Optimize queries and improve performance

Open kewitz opened this issue 3 years ago • 4 comments

  • [ ] Improve Order stats query performance: https://github.com/opencollective/opencollective-api/blob/820a22f0eb029cec5b5fc81c2aa6851bb879f9da/server/lib/security/order.ts#L19-L26
  • [ ] N + 1 issue on Expense.securityChecks for the host dashboard. The field is not using loaders despite having multiple heavy SQL queries.
  • [x] As a quick win, indexing users IPs as we're filtering on nested JSON in findRelatedUsersByIp seems like a necessity.

kewitz avatar Nov 04 '22 12:11 kewitz

https://opencollective.com/nmhep/contribute/heppy-10th-birthday-new-mexico-health-equity-partnership-45814/checkout/profile?amount=100&platformTip=20&contributeAs=raju-sheikh&amount=100&platformTip=20

Rajusheik avatar Nov 23 '22 03:11 Rajusheik

Related: https://github.com/opencollective/opencollective/issues/6739

Betree avatar May 17 '23 10:05 Betree

Also, there's an optimization to make on the call to PayoutMethod.findSimilar; it is not batched (N + 1 issue) and the query filters on unindexed JSONB data, making it extremely slow in some cases (I get some timeouts locally).

Betree avatar May 17 '23 11:05 Betree

Some fields have since been migrated to add loaders, but we're still missing 2 important calls:

  • findRelatedUsersByConnectedAccounts
  • PayoutMethod.findSimilar

Betree avatar Feb 15 '24 08:02 Betree