Materialized Views

For a standard SQL view, the results are computed each time the view is referenced in a subsequent SQL statement. For a materialized view, the results of the view statement are computed and saved in a temporary server table when the view is created. As long as the input tables that the view consists of are not changed, the materialized view returns the results from the temporary table when the view is referenced in an SQL statement. If any of the input tables that make up the view are modified, the materialized view recomputes the results the next time the view is referenced and refreshes the temporary table with the new results. The temporary results table for a materialized view exists for as long as the view exists. When a user deletes or drops a materialized view, the temporary results table is deleted as well.

You must use the explicit SQL pass-through facility to create a materialized view. Specify the keyword MATERIALIZED in the CREATE VIEW syntax to identify the view as a materialized view. When you create a materialized view, the CREATE VIEW operation does not complete until the temporary results table is populated. This process can add substantial time to the execution of a CREATE VIEW statement.

Each time you reference a materialized view in an SQL statement, a check determines whether any of the input tables that are used to produce the temporary results table have been modified. If none of the tables have been modified, the temporary table is substituted in place of the view file in the SQL statement. If any of the input tables have been modified, the SQL statement executes and uses the changed tables. The statement functions like a standard SQL view reference. A background thread is also launched. The background thread is independent of the SQL statement execution. This thread refreshes the temporary results table. Until the refresh is completed, any incoming references to the view are treated as standard view references.

When you create a materialized view, an additional server table is created in the same domain as a standard SQL view file.

You cannot view or access the materialized view table by using PROC DATASETS or other SAS procedures. If one or more simple indexes are defined on any of the input tables that are used to create the results table, the indexes are also created on the materialized view table, as long as the column that was indexed in the input table also exists in the materialized view table.

To create a materialized view, use the following SQL explicit pass-through syntax.

execute (
  create materialized view <viewname> 
  as <SELECT-statements> ) 
by [sasspds | alias];

The MATERIALIZED keyword is necessary only in the CREATE VIEW statement. For all other references, use only the view name to reference the materialized view. The same is true in the DROP VIEW statement. For example, to drop a materialized view, use the following syntax.

EXECUTE (Drop View <viewname> ) BY [sasspds | alias];

A materialized view can provide enormous performance benefits when the view is referenced in an SQL statement. For views that contain costly operations such as multiple table joins or operations on very large tables, the execution time for queries containing a materialized view can be orders of magnitude less than a standard view. If the results table produced by the view is relatively small in comparison with the input tables, the execution time for queries that use a materialized view might be a few seconds versus several minutes for a standard view.

For example, if it takes on average 20 minutes to produce the result set from a view, and the result is in the order of thousands of rows or fewer, a query that references a materialized view takes seconds to execute. If you create a standard view, every time the view is referenced results in 20 minutes of execution time. You should measure the performance benefits on a case-by-case basis.

You can base your decision of whether to use a standard view or a materialized view on how often the input tables to the view are updated, versus how often the view is referenced in an SQL statement. If a view is being referenced at least twice before any updates occur, then the materialized view should provide superior performance. In cases when you can create the defined view quickly, you probably do not need a materialized view. If the input tables are frequently updated in comparison to how often the view is referenced, a standard view is probably more efficient.

You must query or access a server materialized view though an SQL explicit pass-through connection. Attempts to access the server materialized views via native SAS will result in an error.

The example statements below illustrate how to access a server materialized view:

select * 
  from connection 
  to sasspds 
   (select .... from <viewname> ...);

or

execute(create table <tablename> 
  as select ... 
  from <viewname> ... 
  by [sasspds or <àlias>] );

The following code creates and uses a materialized view. The code creates the input tables X and Z. Table X has three columns (a,b,c), and table Z has four columns (a,b,c,d).

data mydomain.X;
  do a = 1 to 1000;
     b = sin(a);
     c = cos(a);
  output;
end;
run;

data mydomain.Z;
  do a = 500 to 1500;
     b = sin(a);
     c = cos(a);
     d = mod(a,99);
  output;
end;
run;

PROC SQL;
connect to sasspds (dbq='mydomain'
  host='myhost'
  serv='myport'
  user='me'
  passwd='mypasswd');

execute (create materialized view XZVIEW as
         select *
           from Z
           where a in
             (select a from X))
           by sasspds;

         select *
           from connection
           to sasspds
            (select *
              from XZVIEW
              where d >90);

execute (drop view XZVIEW);
disconnect from sasspds;
quit;