Openfoodnetwork: Performance issue : system doesn't manage to display report

[myriamboure]

In France when I click on "order cycle customer total", depending on the fliters I set up, the system doesn't manage to load the page and I got the snale page. Especially when I set up for example start date 01/01/2017, until 31/03/2017, I got the snale, I think it's too much data to display and I guess I get a time out.
The problem is that a user wants to do that export every three months because he needs those data to establish invoices (he doesn't' give an invoice per order but a general invoice every three months), and he doesn't manage to get it... anyway, that's a performance issue and if it's possible to load data for a long period of time if should display.

[myriamboure]

@sstead is it something people are experiencing in Australia? For info in the case of this user he has 12 hubs and has lots of sales so there is a big amount of data... I guess that's why we are having timeout ? Ping @pacodelaluna as you said you would try to have a look at it.

[kirstenalarsen]

I’m not sure if it’s in the code or on the server, but we have dealt with things like this in the past by increasing timeouts - so the snail takes longer. @rohanm is that something that can be handled at instance level?

On 19 Apr 2017, at 7:15 PM, Myriam [e-mail] wrote:

@sstead https://github.com/sstead is it something people are experiencing in Australia? For info in the case of this user he has 12 hubs and has lots of sales so there is a big amount of data... I guess that's why we are having timeout ? Ping @pacodelaluna https://github.com/pacodelaluna as you said you would try to have a look at it.

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub https://github.com/openfoodfoundation/openfoodnetwork/issues/1539#issuecomment-295180029, or mute the thread https://github.com/notifications/unsubscribe-auth/ACxryHfCNEPZPbG8_kF-0E56h7uyV_DHks5rxdDGgaJpZM4NBZ-5.

[enricostano]

Maybe we should investigate pagination?

[jeronimo]

Pagination most likely will help but it can be the same for PDF/CSV export.

There is currently some work done on implementing UI-Grid (including "order cycle customer total") https://github.com/jeronimo/openfoodnetwork/tree/uk/1316-ui-grid-reports
It might fix the issue. But is it possible to get database dump to reproduce the issue and to try to fix it for old and new versions?

[myriamboure]

@pacodelaluna do you have an idea where it can come from ?

[myriamboure]

As an admin I an't even access this page anymore, I get the snale straight away, and I have a user who needs support :-o

[pacodelaluna]

From a quick view at the code, I think there are some issues with the data treatment, we should optimize the Postgres requests instead of working directly with data structures in Ruby which is costly.

[myriamboure]

Seems similar to https://github.com/openfoodfoundation/openfoodnetwork/issues/1139

[sauloperez]

From a quick view at the code, I think there are some issues with the data treatment, we should optimize the Postgres requests instead of working directly with data structures in Ruby which is costly.

Did you spot poorly optimized queries? Because that's what it looks like

Maybe we should investigate pagination?

Given #1139, looks like it's time to implement it

[kirstenalarsen]

caution about spending too much time on reports as likely a lot will be made obsolete by the new report UI-grid interface . .

On 21 Jun 2017, at 7:08 PM, Myriam [e-mail] wrote:

Seems similar to #1139 https://github.com/openfoodfoundation/openfoodnetwork/issues/1139
—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub https://github.com/openfoodfoundation/openfoodnetwork/issues/1539#issuecomment-310017182, or mute the thread https://github.com/notifications/unsubscribe-auth/ACxryNGKc5_n6NCwNcQI4ANE_Sg1n7coks5sGN2hgaJpZM4NBZ-5.

[lin-d-hop]

As an interim fix @myriamboure you may want to increase server timeouts from 60 secs so that you don't get the slug on page loads. I know it's not a fix, but since you are particularly experiencing the problem in super admin it is a quick work around.

As mentioned above by the time any fix is merged to master UI grids could well be at phase 1 release for this report.

[sauloperez]

From a quick view at the code, I think there are some issues with the data treatment, we should optimize the Postgres requests instead of working directly with data structures in Ruby which is costly.

I'm glad we share the same view! that's the biggest problem I see in all reports. There are tons of data mangling there when ideally (and I believe perfectly doable) all the data should come out of a query, ready to be rendered. Doing so would greatly reduce the code required for reports to work which is always good.

[myriamboure]

@kirstenalarsen @lin-d-hop @sauloperez any idea how this can move forward ? Our user don't manage to charge his data and he is a bit annoyed... @pacodelaluna can we try to increase the server timeout as proposed by Lynne meanwhile ? Am I the only one to have this issue ? This would be pretty surprising, I guess you have tons of data in Aus so you should have the same issue, no ? @sstead no worries on your side when loading early data for a big big hub for example ?

[kirstenalarsen]

I would recommend trying increasing the server timeout definitely to start
with . .

On 16 August 2017 at 03:47, Myriam [e-mail] wrote:

@kirstenalarsen https://github.com/kirstenalarsen @lin-d-hop
https://github.com/lin-d-hop @sauloperez https://github.com/sauloperez
any idea how this can move forward ? Our user don't manage to charge his
data and he is a bit annoyed... @pacodelaluna
https://github.com/pacodelaluna can we try to increase the server
timeout as proposed by Lynne meanwhile ? Am I the only one to have this
issue ? This would be pretty surprising, I guess you have tons of data in
Aus so you should have the same issue, no ? @sstead
https://github.com/sstead no worries on your side when loading early
data for a big big hub for example ?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/openfoodfoundation/openfoodnetwork/issues/1539#issuecomment-322538297,
or mute the thread
https://github.com/notifications/unsubscribe-auth/ACxryGESmlbEbsgiwI7bxSupWdNn0CGrks5sYdnOgaJpZM4NBZ-5
.

[myriamboure]

When investigating the big performance issue we have on French server (that force our users to recreate new entreprises when they reach a threshold of data) we need to check if this is solved.

[sstead]

This issue has come up in Aus from a SuperAdmin perspective. Gradually the reports have been getting slower and slower to the point that I can now only download 2 days of OC Customer Totals data at a time (any longer and the page times out).

[myriamboure]

Shall we treat some "report overhaul" as a feature? it is not really a bug as it is badly designed, we can't really fix it we need to rebuild it differently if I understand well. In doing that we have various technical solutions: the one proposed above, but also going for some "custom report" feature using zapier directly with possibly defaults requests that we can setup for users on the basics like sales reports. So I think we should treat that as an icebox thing to be specified, scoped, prioritized. Agree @kirstenalarsen @daniellemoorhead @RachL ?

[myriamboure]

@lin-d-hop could be a great product owner on that one :-)

[kirstenalarsen]

yep I think things are hitting mission critical on reports .. i think we need to look at whether it has to come into 'top 10' in some form

[sauloperez]

I'd say refactoring them to fetch data from DB in a performant way it's much quicker than rebuilding them. https://github.com/openfoodfoundation/openfoodnetwork/issues/2616 is the first step towards that. Looking forward to what @kristinalim comes up with! Improving their UX can come as a second iteration.

[mkllnk]

@sstead can't even download a report for one day now. That renders it completely broken for super admins on Aus production. She needs:

I would like the OC customer totals report for the whole instance for the whole month of Sept and Oct (each month separate please). Alternatively I just need a list of which shops had sales in each month, and the total value of those sales

I will do some basic database queries to see how they perform and share my experiments here.

[mkllnk]

SQL to get all sales per distributor in one month:

select distributor_id, enterprises.name, count(spree_orders.id) orders, sum(spree_orders.total) distributor_total from spree_orders join enterprises on distributor_id=enterprises.id where (spree_orders.completed_at >= '2018-10-01' and spree_orders.completed_at < '2018-11-01') group by distributor_id, enterprises.name having sum(spree_orders.total) > 0 order by enterprises.name;

Disclaimer: the date here is UTC and therefore a little bit off for Australia, but if we do that consistently, we get good statistics.

[myriamboure]

This issue is pretty high in the bug backlog column @sstead I do share your pain, I'm having it since months but found some ways by creating a user and adding it as a manager of the company I need to support, to see only few data, then removing the manager... I know it's not long term, but there are some bugs before this one IMO, check the bug backlog and see if you agree, else propose some rearrangement ;-) The problem is even bigger, like a hub want to export sales data for a year, which is legitimate... and they can't. So I think we need to open a bigger project on the future of reporting, studying the option to connect with zapier with some "predefined requests" but give the possibility to users to setup new requests and build on-demand reports as they want, or use some dashboard tool we can integrate with, etc. Maybe just taking reports out of OFN and integrating with powerful tools able to do it, and integrating the logic in OFN "as if it was in OFN" but that redirects to a google sheet, or... anyway, I think we need a new feature to solve that bug so we need to open some wishlist and do a proper inception of 1- The need and 2- All the potential solutions. @kirstenalarsen @sstead @lin-d-hop do you agree? If yes let's move that conversation on Discourse, if one of you wants to open or check existing wishlist so we take it and move it forward to inception (could be a first experiment on priorizing inception work!) let's do it.

[kirstenalarsen]

Yes I agree @myriamboure - this is a prime case for getting the ball rolling on investigation and inception. I think following the conversation last week re. solid and blox etc this is a really good opportunity to look at all the options and consider what the actual best path is going to be for 'next generation' reporting solution :) I am not going to put my hand up to start the ball rolling as I already have a list of things to get rolling that I haven't got to yet and don't want to be a bottleneck in this conversation. I will say though that we have another food hub system (Ceres) also needing to relook at it's reporting, so the time seems right to look at 'arms length and shared' solutions to this problem

[luisramos0]

I used to work on a reporting tool with a request timeout of 15 minutes :-)
Performance is not really important in reports. As long as they work, this is the part of the app users will wait a little bit with no problem.

Updating the request timeout is a valid solution to this problem. And a good alternative to spending a lot of precious roadmap space on this subject.
One approach I've seen used is to segregate the app from reports: we keep the source code as is but we deploy a separate instance (on the same server or separate server) that connects to the same DB but only shows reports to the users: this separate instance will have a much bigger request timeout. This solves the problem of having a long timeout on the transactional part of the app (which will create other problems).

[sauloperez]

Interesting @luisramos0. What a surprise. This is exactly what we did in https://github.com/coopdevs/katuma_reports, but that was a hack :trollface:

It seems like a good trade-off to me.

[luisramos0]

I dont think the solution I am describing is a hack, it's a very common solution to a very common problem in all webapps with reporting around the web.

eveveryone, fyi, we are talking about this because there was a new occurrence of this issue in #3148 and it was fixed by increasing timeout on FR server here: https://github.com/openfoodfoundation/ofn-install/pull/290

[sauloperez]

I understand. I meant the reason why did it was a hack; trying to slip in a report our users neede :see_no_evil: . Anyway, glad to share the pros and cons we found when the time comes.

[mkllnk]

Maybe the reports are the first module to extract and move into a separate app. That sounds very reasonable to me. It would be a good project for Google's Summer of Code.

[lin-d-hop]

Closing as we will be tackling with within the epic [Performance] reports/orders&fulfilments #4131