Report generation doesn't scale

[connorjclark]

Hello, first of all thanks so much for this great gem, it's been a huge time saves in a lot of aspects so far for my current project.

The only issue I have come across so far deals with generating reports. We have done a small launch for our application, and got 500 users to take surveys.

The issue: the surveys with 400-500 responses take too long to create a report. Greater than 30s, the default timeout time. Our hotfix solution is to cache the reports generated, and kill the cache every 20 responses or so.

I believe the generate_report method is the culprit, with all of those joins.

I wanted to open up this issue to see if anyone else has come across this. I will be attempting a solution in the coming weeks :) Any feedback or help would be wonderful!

[kjayma]

Connor, thanks for the feedback - I really appreciate it and I'm glad the
gem is useful to you.

I'm not surprised to hear of the problem. I never got around to
performance tuning on the reports.

If you've started work on a branch to fix the problem, why don't you push
it up to github and I can work with you on the solution. If not, I can get
one started and let you know. I expect to have some time here and there
over the next week to work on the problem.

On Sat, Jan 31, 2015 at 11:17 PM, Connor Clark notifications@github.com
wrote:

Hello, first of all thanks so much for this great gem, it's been a huge
time saves in a lot of aspects so far for my current project.

The only issue I have come across so far deals with generating reports. We
have done a small launch for our application, and got 500 users to take
surveys.

The issue: the surveys with 400-500 responses take too long to create a
report. Greater than 30s, the default timeout time. Our hotfix solution is
to cache the reports generated, and kill the cache every 20 responses or so.

I believe the generate_report method
https://github.com/kjayma/surveyor_gui/blob/master/app/controllers/surveyor_gui/reports_controller.rb#L43
is the culprit, with all of those joins.

I wanted to open up this issue to see if anyone else has come across this.
I will be attempting a solution in the coming weeks :) Any feedback or help
would be wonderful!

—
Reply to this email directly or view it on GitHub
#42.

[kjayma]

Looks like the problem is not so much in the generate_report method, but in the views.

[connorjclark]

Hey kjayma,

I am free starting Thursday to take a deeper look. Will get back to you then :)

[kjayma]

Hey Connor,

I started some work on this over the weekend. When I get home, I'll push
branch. I started by adding some eager loading that was missing in the
controller. That helped quite a bit, but there are some bigger problems
that I haven't solved yet. The issue is n+1 queries on response_sets and
responses, and I haven't yet nailed down what's triggering it.

On Sun, Mar 8, 2015 at 5:42 PM, Connor Clark notifications@github.com
wrote:

Hey kjayma,

I am free starting Thursday to take a deeper look. Will get back to you
then :)

—
Reply to this email directly or view it on GitHub
#42 (comment).

[kjayma]

Peformance tuning aside, I've been thinking of making the reports
asynchronous. Could introduce a delayed job and a call back to the web
page, so it doesn't time out.

On Sun, Mar 8, 2015 at 5:42 PM, Connor Clark notifications@github.com
wrote:

Hey kjayma,

I am free starting Thursday to take a deeper look. Will get back to you
then :)

—
Reply to this email directly or view it on GitHub
#42 (comment).

[connorjclark]

An asynchronous solution would be a good fallback. I suggest we try and get the performance as quick as possible, and then test it on a very large amount of response sets. If it's still slow, we should add an asynchronous callback URL.

Sent from my iPhone

On Mar 9, 2015, at 9:09 AM, Kevin Jay notifications@github.com wrote:

Peformance tuning aside, I've been thinking of making the reports
asynchronous. Could introduce a delayed job and a call back to the web
page, so it doesn't time out.

On Sun, Mar 8, 2015 at 5:42 PM, Connor Clark notifications@github.com
wrote:

Hey kjayma,

I am free starting Thursday to take a deeper look. Will get back to you
then :)

—
Reply to this email directly or view it on GitHub
#42 (comment).

—
Reply to this email directly or view it on GitHub.

[kjayma]

Pushed a new branch to work on this problem at report_performance

[connorjclark]

I will be spending a lot of time today working on this. Hopefully we'll come to a solution :)

Are there any tests for the reporting? If not, I will spend some time getting that set up.

I just learned about performance tests in Rails. We can use this to keep track of our progress. This would be the best thing to do first, so we have some good before and after stats.

[connorjclark]

I am getting this error when I run 'rake spec'

"Could not load the testbed app. Have you generated it?"

Looking at /test, I am really not sure what I need to do to generate it.

[kjayma]

There are not yet tests for reports. I banged it in quickly before starting a new job, and elected to get something up there fast in the short time I had at the expense of performance and tests.

For setting up the tests, you need to run "bundle exec rake gui_testbed" before running the specs. You may also need to run "bundle exec rspec"

[connorjclark]

https://gist.github.com/Hoten/545d346d2398c0c083d3#file-gistfile1-txt-L497

Attempting to install the testbed app. "bundle exec rake gui_testbed" produces a few routing errors. Any idea what is causing this?

[connorjclark]

I came across a performance issue on another application last week. I fixed it by lazily loading associations for potentially large queries. Will attempt this.

[connorjclark]

The report view are doing a lot of data base queries via 'where'. My solution is starting to look like this:

changes to reports#show (

surveyor_gui/app/controllers/surveyor_gui/reports_controller.rb

Line 30 in 33ed11a

def show

) :
Instead of giving the view a ActiveModel query builder, I give it the responses as an array. I also eagerly load the proper association from the ResponseSet query.

@response_sets = ResponseSet.includes(responses: [:question]).where(survey_id: @survey.id, test_data: false)
@responses = @response_sets.flat_map(&:responses)

I then had to travel to the report views and modify the wheres to be simple selects.

Still many queries coming from somewhere. Almost there, I think.

[connorjclark]

Last part of the puzzle!

_report_data.html.haml (

surveyor_gui/app/views/surveyor_gui/shared/_report_data.html.haml

Line 11 in 33ed11a

- @responses.where(question_id: q.id, response_set_id: response_set).each do |r|

) had a huge double loop. Changed it to

response_set.responses.select { |r| r.question_id == q.id }.each do |r|

Also, some report partials were calling a method called 'is_comment' (https://github.com/kjayma/surveyor_gui/blob/master/lib/surveyor_gui/models/question_methods.rb#L37) on questions. See:(

surveyor_gui/app/views/surveyor_gui/shared/_report_data.html.haml

Line 22 in 33ed11a

- if q.answers.is_comment.count > 0

)

On my machine, reporting is now instantaneous for ~400 response sets. (sidenote: I am only testing with :pick_one question types). I upped it to 2400, and it took 30s... the bottleneck seems to be that scary loop in _report_data (

surveyor_gui/app/views/surveyor_gui/shared/_report_data.html.haml

Line 11 in 33ed11a

- @responses.where(question_id: q.id, response_set_id: response_set).each do |r|

)

I'm doing all this optimization on another project, since I can't get the testbed app to work to test things. I'll attempt a merge of all the changes when I can get that working - failing that, I'll just make changes blind and let you patch up the remaining issues.