Improve performance of stock dashboard

IMA-WorldHealth / bhima

A hospital information management application for rural Congolese hospitals

GNU General Public License v2.0

215 stars 103 forks source link

Improve performance of stock dashboard #6659

Open jniles opened 2 years ago

jniles commented 2 years ago

The stock dashboard uses queries that take far too long to load and crashes the server on most low-end devices. We should optimize these queries so that someone doesn't inadvertently kill the server by trying to load the dashboard.

jmcameron commented 2 years ago

Should we temporarily disable this page for now until this issue is resolved?

jniles commented 2 years ago

I'm not sure how to do that in a way that isn't as much effort as just fixing it. Got any ideas?

jmcameron commented 2 years ago

Can't you disable that page via the permissions?

On Wed, Jun 1, 2022 at 9:28 AM Jonathan Niles @.***> wrote:

I'm not sure how to do that in a way that isn't as much effort as just fixing it. Got any ideas?

— Reply to this email directly, view it on GitHub https://github.com/IMA-WorldHealth/bhima/issues/6659#issuecomment-1143281286, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAAPFQLSUI4GK7UXGYF5C73VM4NKRANCNFSM5XQGUWQA . You are receiving this because you commented.Message ID: @.***>

jniles commented 2 years ago

That would be a script we run in each production site?

Given how long it takes to coordinate connecting to each site, I would suggest that we do a fix, then do an upgrade that contains that fix. But I could be swayed.

jmcameron commented 2 years ago

I'm not sure how to do that in a way that isn't as much effort as just fixing it. Got any ideas?

I'm not sure how involved it would be to optimize the queries to make this faster. We could stick SQL queries to disable the permissions for that page as part of the migration file and restore it later. We would need to send an email to the admins with small servers not to re-enable that permission for now.

jmcameron commented 7 months ago

After taking a quick look at this issue, it seems like there are at least two simple things to do to improve the performance of the Stock Dashboard:

Add a search filter and default the time period to the last year. Currently all the panels search for 'allTime' period. It is not clear that lots from years in the past are relevant if they have not been used recently. In any case, the filter would allow searching for 'allTime' if desired.
Each panel does its own "stockLots()" or "stockInventories" query. This could be reduced to one more general SQL query (eg 'stockLots()' and then do the filtering for each dashboard with the result of the SQL query. Note that this should not be much slower than 'Articles in Stock'.