Large "in list" filter caches causing performance issues
Question Asked
In some of our reports we would like to utilize the drop down list of cached values that is generated using a custom query. In one specific filter, we map a list of names to access filters (which the filter is dependent on). The issue is that the complete cache is almost 500,000 rows big and this causes the report to take a long time upon initial loading. Is there any work around or way to prevent long loading times using large filter caches? Or is this simply the nature of having very large caches? Thank you for your help!

guest

Fri Feb 3, 2012 4:51 AM

Comment
Hi,

Generally caching a filter that large is not recommended.

You could make that filter dependent on another field to try to narrow down the results returned.

Regards,
Steven

Steven

Fri Feb 3, 2012 11:03 AM

Comment
Thanks for your reply.

Right now the cached filter is dependent upon some of our other filters (cascading) but we've noticed that regardless of the way the large cached values are narrowed, it still takes a long time for the report to load due to the fact that the cache is set up. So that I can be clear:

Hypothetically, say we have 500,000 student names and each group of 100 student names are associated with a professor.

We could set up cascading filters so that the user would first need to choose a professor, and then the student "in list" filter would become available with the 100 students under that particular professor.

We've noticed that even if the student filter is narrowed down in such a way, that fact that such a large filter cache even exists is what slows down the report upon initial load. I'm sure there is probably not much you could do from a YellowFin perspective for such a large cache, but I wasn't sure if this is a common problem seen by others.

guest

Sat Feb 4, 2012 6:28 AM

Comment
Hi,

This is a fairly large list to load into Yellowfin, though using cahced filters should speed this up.

However it may still need to load all the results from the YF database.

When a filter is cached, the values are loaded from the source database, converted to an xml document and stored in the Yellowfin database.
The results are also cached in memory for up to 24 hours, after 24 hours they are removed.
[i]This is to stop it from using up too much memory. /[i]

When a report uses a cached filter, it gets the results from the memory cache first if possible, and if not will load them from the xml document stored in the YF database (and add it to the memory cache).

When the cached filter is loaded from the YF database, it loads the XML document that includes the entire result set for all combinations of dependant filters (500000 values in your case).
Loading this document and converting it back to a native java object could take a while for a large data set like this, though it shouldn't do it every time the report is run, only once per day at the most.

So in saying this does your report take a while to open each time?
By 'open' we mean simply launching the report, and not running the report.

Thanks,
David

David

Tue Feb 7, 2012 12:45 PM

Comment
Please note:

Cached Filters are no longer stored as XML in the Yellowfin database. Changes to this process occured prior to Yellowfin 6.2.

We now use dedicated tables which have improved performance & storage.

Thanks,
Yellowfin Support




David

Fri Oct 4, 2013 2:44 PM