caching reports

(2 posts) (2 voices)


No tags yet.

  1. casteldji, Blocked


    if some of my reports are data intensive and i want to generate them only e.g. once every X hours rather than generating them each time the report page is loaded, what is the best solution for doing that?
    I thought about generating/storing the aggregated results from the heavy queries every X hours in separate tables and create the myDBR reports from those tables but it is painful and defeat somehow all the added value of mydbr.

    thanks a lot in advance for your insight about that.

  2. myDBR Team, Key Master

    Usually the first step is to see if queries can be optimized so that no cached data/reports would be needed. You can quite often get substantial improvement into query times and I/O by optmizing the SQL and making sure that queries use indexes well.

    If you need the cached data, the easiest way to avoid doing the heavy calculations is to use the aggregated results table and decide on runtime if you need to refresh the cached data. In that case the report logic would go like this:

    User runs the report:
    1. See if X hours has elapsed since last aggregated results table update. If not goto 3
    2. Run the heavy calculation and update the aggregated results table and record the time of the update
    3. Generate the report from aggregated results table

    myDBR Team


You must log in to post.