Forum

This content is now out of date.

Visit Our Community

YF executes too many reports on filter value change (performance problem)

We have a dashboard with 104 reports, distributed on 9 subtabs.
The dashboard displays a filter which is linked to most of these reports. In other words, most of the reports share this one filter.

Problem:
When changing the filter value, the screen update takes very long.
We would naively expect it to take the same time it takes to switch subtabs.
But it takes much longer than that.
So we wondered why.

We checked Yellowfin's ReportInstance table, because that records information about which reports got executed when.
We found that when just switching to another subtab, Yellowfin executes the reports associated with that subtab.
But when changing the filter value, Yellowfin executes all reports connected with the filter, no matter on whch subtab they were.
That would readily explain the extraordinary time it takes to update the screen.

Maybe Yellowfin tries to cache something. (But in that case, it doesn't use the cached information, because when we switch to another subtab, it executes that subtab's reports again. It wouldn't have to re-execute them if it used a cache.)
Whatever the cause, it's a big performance problem for us.
Please fix it.
If it's some caching logic gone wrong, we'd at least like to know how to switch it off! Because we did not knowingly switch it on.

Here are the steps to reproduce the problem:

1. Make a fresh installation of Yellowfin 7.1 with default settings (i.e., HSQLDB).
Start the Yellowfin server process.
2. Go to the administration menu and import the file "YellowfinExport Local 2014-10-30 Problem 05.xml", attached here in zipped form.
3. The import contains a data source pointing to our database. You'll have to make a manual fix to this data source as follows:
3.1 Administer the data source "Optikick".
3.2 Go to page 2 ("Filters"). Click on the source filter "Yellowfin-Benutzer-Rollen".
3.3 Check the checkbox "Refresh filter when new users are created".
3.4 Click the link "Refresh filter now". This should normally create a single data record.
4. Go to "Browse" and click on the dashboard "Problem 05".
5. Play a little with the dashboard "Problem 05": Switch through the dashboard's subtabs and use the filter "Spiel-ID", i.e. select a value and click "Go", or select no value and click "Go".
Note down your actions including the exact time. You should end up with a list looking somewhat like this:

2014-10-30 12:41:10 Click on Subtab1
2014-10-30 12:41:23 Select filter value 239 for filter "Spiel-ID" and click "Go"
2014-10-30 12:41:35 Click on Subtab2
2014-10-30 12:41:43 Click on Subtab3
2014-10-30 12:41:54 Select NO filter value for filter "Spiel-ID" and click "Go"
2014-10-30 12:42:05 Click on Subtab2
2014-10-30 12:42:15 Click on Subtab1

6. Stop the Yellowfin server process.
7. Using the SQL front-end tool of your preference, connect to your HSQLDB directory.
8. Execute the following SQL. Note the " + 3600"; that is meant to correct for the time zone. You may need a different value.

select
ReportHeader.ReportName,
ReportHeader.ReportId,
--
ReportView.ViewDescription,
--
ReportInstance.ReportInstanceId,
date_add(cast(ReportInstance.ReportDate as datetime), (ReportInstance.ReportTime + 3600) second) as ReportTimestamp,
ReportInstance.ReportDuration,
ReportInstance.ReportStatusCode,
ReportInstance.SQLText,
ReportInstance.RequestTypeCode
from
ReportInstance
left join
ReportHeader
on
ReportHeader.ReportId = ReportInstance.ReportId
left join
ReportView
on
ReportView.ViewId = ReportHeader.ViewId
where
ReportInstance.ReportId = ReportHeader.ReportId
-- and ReportInstance.ReportGMTDateTime >= 20141029000000 -- Today
order by
ReportInstance.ReportGMTDateTime
;

9. Paste the SQL results into a spreadsheet.
10. Paste your notes below the SQL results.
11. Sort by the reporttimestamp column. This gives you a nice sequence of events, interleaved with your own notes of what you actually did to trigger these events.
You should now be able to see the phenomenon I described above.
I've attached my own spreadsheet for your reference ("Problem 05 Match auf Lokal f�r YF.xlsx"). There, you'll see that after selecting filter value 239, Yellowfin executed all 11 reports on all subtabs, not just the 4 reports on Subtab1 (highlighted green).

As I said, it's a big performance problem for us.
It renders our dashboard near unusable.
The problem will have to be fixed before we can hope to sell our product.

System details:

Application Version: 7.1
Build: 20140923
Java Version: 1.7.0_65
Operating System: Linux 2.6.32-openvz-amd64 (amd64)
Hi Gert,

Thanks for the detailed information.

Unfortunately this is just how our subtabs load, and there is not going to be an easy way to change this without major changes to our code.
I have raised an enhancement request (TASK ID =178057) and marked it as HIGH, though I cannot comment on an ETA as it is currently marked as 'unplanned'.

However in saying this, I think there could be some ways to combat this;
-Reduce the number of reports that are linked across these tabs.
104 reports loading at once is quite a lot of load time
-Split up these sub-tabs into actual dashboards
This means you won't have all tabs loading at once.
E.g. Create 3 dashboards tabs, with 3 sub-tabs in each.

Hope these workarounds can help you with your performance issue and let me know if you have any comments or questions on this.

Thanks,
David


Thanks for your response, David!

>
>Unfortunately this is just how our subtabs load,
> and there is not going to be an easy way to change this
> without major changes to our code.
>

As I see it, Yellowfin doesn't even use the results from those superfluous SQL queries.
So skipping them entirely should have exactly zero consequences.

>
> [...] there could be some ways to combat this [...]
>

Yes, of course. But that would make the interface less useable. The user would have to click on the right Tab and re-select their desired filter values. And if they picked the wrong Tab by accident, bad luck, try again. May even be as slow as the current situation.
Hi Gert,

I do apologize for how this is affecting your YF instance, though unfortunately there is nothing that can be done for this at this very point in time.

The only hope is to have this implemented in a future release, which is not hopefully too far in the future.

Thanks for your patience and understanding with this issue.

Regards,
David
Hi David,

We are facing same issue in our environment and we have created 3 Dashboard with 3 sub tabs still we have performance issue. That would be really appreciated if will get this fix soon. We are using Yellowfin 7.1 (20160701)

Thanks,
-Harsh
Hi Harsh,

Unfortunately the task is still to be planned.
However.... I do know in the next coming major releases (over the next 3), the dashboard will be getting re-vamped. So that is the time when these items will be reviewed and planned, or closed with information as to why.
This is all happening over the next 12 months (give or take). Until then there is not much else we can do on this. I do apologise for this .

Regards,
David