3 Replies Latest reply on Mar 31, 2014 12:19 PM by shawkins

    Setting materialized view to be loaded at start time and not at first request and refreshed periodically

    gioppoluca

      It seems to me that materialized view are generated at the first call to a VDB (at least the in-memory ones).

      It would be nice to have them loaded at VDB activation and refreshed after time-to live expiration.

      The option is to have the view refresh itself periodically so that there will be no wait time for the first user.

      This can be helpful especially for large data that does not vary much and can require complex transformation, but is not so much or frequently used.

      Having the option to:

      1) load at start time

      2) refresh at end of time to live without waiting for next call

      could be usefull

      Is it already possible?

      Is it reasonable?

      Thanks

      Luca

        • 1. Re: Setting materialized view to be loaded at start time and not at first request and refreshed periodically
          markaddleman

          I don't think such a thing is possible without code but we have similar requirements and address them this way:

          At application startup, we issue the following anonymous procedure block as a continuous query:

          BEGIN

             select * from materialized_view without return;

             call delay(1, 'HOUR');

             select 1;

          END

           

          As a continuous query, Teiid will execute the block repeatedly as long as the invoking JDBC connection is still alive.  Obviously, the first statement materialized/refreshes the view.  The second statement invokes a stored procedure from a custom translator that simply throws a DataNotAvailable exception for the specified time period.  The last statement is required under the semantics of an anonymous procedure blocks:  these blocks are required to return something.

          • 2. Re: Setting materialized view to be loaded at start time and not at first request and refreshed periodically
            rareddy

            We started similar features with external materialize management with [TEIID-2584] Add management features to materialization  However, these still need to be expanded to internal materialization situation too. This started out with need for job scheduler, now we need more integration code for internal mv and management UI etc to round up the feature.

             

            Ramesh..

            • 3. Re: Setting materialized view to be loaded at start time and not at first request and refreshed periodically
              shawkins

              > Is it already possible?

               

              Additional management support was made available for external materialized views, including proactive loading/refresh.  This has not yet been tied into internal materialization.

               

              > Is it reasonable?

               

              Yes it is reasonable.  The intent behind internal materialization was to have something built-in with good performance characteristics, without a lot of bells and whistles.  However it seems that people do want it to be more fully featured, so additional management options would be reasonable.  Can you log something?

               

              > I don't think such a thing is possible without code but we have similar requirements and address them this way

               

              Yes, that would work.  You can also use the REFRESHMATVIEW system admin procedure.