Roll Your Own Materialized Views

This procedure is called usp_mview, it will create and maintain a set of tables based on a view. In reporting application (Cognos, BusinessObjects) views are often created to simplify SQL or perform some business logic. Often the views are complex, resulting in poor performance. An indexed view can be created, but the schema binding and update frequency often presents problems. This procedure will create and update a set of materialized views similar to Oracle's dbms_mview package.

The procedure depends on the mview_config table, DDL in comments section of procedure. Create this table and add the views you wish to materialize. A primary key (single column only) should be specified. Note that the primary key specified need not be unique, only high enough cardinality to ensure that multiple rows with the same key return different CHECKSUM values. Procedure can be scheduled from any scheduling tool that can execute an SQL command.

PROCEDURE DETAILS

• The procedure uses a configuration table called mview_config. This table lists all the tables/views that you wish to materialize.
• The mview_config table has a column called primary_key. This can only be a single column name. It does not need to be unique, but should have high cardinality (i.e. material_number).
• The procedure takes a parameter @pAction. This can be 'update' or 'create'. It defaults to update.
• If create is specified, the procedure cycles through the config table and creates an identical snapshot of the base table/view with the prefix "mv_". So the view Plan_Sales is materialized as mv_ Plan_Sales.
• For the create routine, a single nonclustered index is created on the primary_key column from config table. You will probably want to add more.
• For the update routine, a delete/insert strategy is used. First a hash table is contructed with primary key and checksum values from both tables, using a full outer join. Most rows in the two tables are probably the same, so this hash table will only have a few thousand rows.
• The update routine looks at the combination of PK+checksum for uniqueness. Based on this, there will be some rows in the mv table that are stale, these are not updated but deleted. Rows that are deleted from base table also fall into this group.
• The Insert routine adds all rows that have actually been updated or inserted in base table.
• The mview_config table is updated at end of procedure with last run data. You may want a running log table at some point.

Hope you find this useful-

Mark Stockwell