Greetings!

I have a problem with reports load time. Query from Forena debug executes fast, but default view with ~20-30 rows displays after 20 seconds in drupal. If i choose report for longer period for example week or 2 weeks i get white blank report.

Ah and another thing i have found.

I have in Frx 4 parameters:
reg_code(text)
group_by_reg_code(checkbox)
id_code(text)
group_by_id_code(checkbox)

And in sql file i have If statement like this:

--IF=:reg_code:group_by_reg_code:id_code:group_by_id_code
--END

This if statement allways returns true even when both checkboxes are unchecked and textfields are empty.

Frx file and Sql file can be found:
Report Frx and sql here

Thank you!
Best regards,
Mairis

Comments

outrun123 created an issue. See original summary.

outrun123’s picture

outrun123 CreditAttribution: outrun123 commented
Assigned: outrun123 » Unassigned
Issue summary: View changes
outrun123’s picture

outrun123 CreditAttribution: outrun123 commented

I digged deeper and the problem is with fields link attribute. If i remove them then report generates in 3.6 seconds. But if i add fields link attribute then report generates in 13 seconds. If i generate report for whole month then report takes 5 seconds, but with link attribute it goes 30s+ ands its timeout.

metzlerd’s picture

metzlerd CreditAttribution: metzlerd commented

I hate to sound skeptical, but I can't really imagine a scenario that would cause this differential based on linked fields. We use linked fields in probably over 90% of the reports that we use and have never seen any performance problems related to this. Here are some followup questions:

What is the number of rows returned by the report? Is 20 correct as I think I'm reading above?

IF that is the case, can you verify the load time of the sample report provided at reports/sample.state_summary? This is a report that uses linked fields and can be used as a comparison for report performance, as can the active users report.

In your forena configuration is the configuration "Use text format filtering system" checked? if so what kind of an input format are you rusing?

Can you compare the query performance time when using the appropriate command line tool?

Finally, Do you have XHprof installed so that I could see call time graphs for your environment?

metzlerd’s picture

metzlerd CreditAttribution: metzlerd commented

The query is very complicated, and most likely the cause of your performance problem. A couple of notes on the query:

  1. Consider using UNION ALL instead of union.. Union deduplicates records which can be a very expensive performance option.
  2. Consider reversing the date comparisons... (somecolumn <= to_date(:parm_date, 'date_literal') rather than to_char(somecolumn) <= :date_literal. The optimizer will likely run the date conversion on every row, but if passed a literal, will only run the date conversion once.
  3. Make sure there are indexes on input date in your database.
  4. If none of the above solves your performance problem, try reworking the query to not use common table expressions (with clause). In oracle you may be forcing it to cache the full result of the query rather than caching the aggregate.
outrun123’s picture

outrun123 CreditAttribution: outrun123 commented

I installed xhprof and got these 2 reports.
Report with links
Report without links

metzlerd’s picture

metzlerd CreditAttribution: metzlerd commented

That is just the kind of data I need. Could you sort this by Incl. Wall Time and repost?

outrun123’s picture

outrun123 CreditAttribution: outrun123 commented

Ok here are the pics
Report with links
Report without links

outrun123’s picture

outrun123 CreditAttribution: outrun123 commented

From drupal logs i get nothing everything is fine.
And in apache logs only these:
[Wed Sep 21 09:53:55.864189 2016] [authz_core:debug] [pid 3796:tid 1248] mod_authz_core.c(806): [client 127.0.0.1:3250] AH01626: authorization result of Require all granted: granted, referer:
[Wed Sep 21 09:53:55.864189 2016] [authz_core:debug] [pid 3796:tid 1248] mod_authz_core.c(806): [client 127.0.0.1:3250] AH01626: authorization result of : granted, referer: