Unavailable materialized view

[bjanker77]

We're currently developing a web application for statistics, based on SQL Anywhere 11. The main source for data is a materialized view. Due to the use of count() and sum() functions in the view, it's impossible to set it to immidiate refresh. Therefore, we refresh the view in an event running every 10 minutes, due to a lot of changes to the underlying data. During the refresh, the materialized view is unavailable, thus also the web application. I've got a couple of ideas, but unsure what to do:

-Two identical views, alternating the refresh between them and keeping track of which view is fresh -Moving the data to a table after each refresh to keep the data until next refresh is finished -Removing all count() and sum() functions to set the view to immidiate refresh.

What is the best practice in this case?

Is it possible to find out if a materialized view is currently being refreshed? In that case we can exit the application gracefully.

Regards,

Bjarne Anker AKVA group Software Norway

[Former Member]

In your web application, are you referencing the materialized view directly as a base table?

[bjanker77]

Yes, we're using it directly in all the queries. But since this question was posted, we've managed to make it refresh immediate, so it looks like the problem is solved. But we're not sure, so any ideas are very welcome.

Bjarne

[Former Member]

Whether or not the best approach is to use immediate refresh, rather than manual refresh, depends on

1. update frequency to the underlying base tables (greater overhead to the system by constantly re-computing the deltas to be made to the view)
2. lock contention (and, possibly, deadlock) between update transactions trying to update the same MV simultaneously

I would argue that MVs provide little benefit if your application is going to refer to the MV by name and the view is manually refreshed; in that case, the SQL Anywhere MV infrastructure is simply providing mechanisms (DDL statements) with which you can manage the view (ie the REFRESH MATERIALIZED VIEW statement). If you're referring to the MV directly, the server can't help with staleness measurements either, as you've bypassed them by referring to the MV directly rather than the underlying base tables.

With a manual refresh/explicit MV reference combination, you might be better off managing the "materialized view" yourself by creating non-transactional global temporary tables when the database is started, and periodically recomputing its contents as a "refresh" (or you might have several to permit your application to continue to use a recent materialized copy while the latest one is being created). You could create a "ring" of temporary tables, managed via events, to maintain various levels of freshness so that the reporting part of your application would never have to wait (or block on a lock). You would, however, be forced to construct your queries dynamically at run time to get the data from the most recent copy; you would need a meta-data table to save that information. You could then issue (the equivalent of) EXECUTE IMMEDIATE in your application to query the most recent, available materialized result.

[Former Member]

Glenn's answer has cleared up a mis-understanding in my mind: I have thought of materialized views as a "structure" I had to create and access explicitly, and it was just a nice by-product that the MV might help when I refer to base tables elsewhere. It just now clicks where the real value of the MV is! Thanks Glenn.

[bjanker77]

Hi.

We ran into som locking-issues, som the immediate refresh is off the table for now. Instead, we're looking into 2 identical views, and some smart software to keep the refreshed. I looks promising. But is there any way to copy a materialized view to a new one in a statement? We usually drop and recreate the view in case of new versions, and it would be nice to have just one definition-file to manage.

Regards,

Bjarne Anker

[Former Member]

No, there isn't a way of copying one materialized view to another. What you're essentially doing with all of this infrastructure is building your own mechanisms to support snapshots - you're really not using the features of MVs at all.