Materialized Views

SPD Server allows users to create an SQL view as a materialized view. What makes a materialized view different from an SQL view? For a materialized view, the results of the view statement are computed and saved in a temporary SPD Server table at the time the view is created. For a standard SQL view the results are computed each time the view is referenced in a subsequent SQL statement. As long as there are no changes to any of the input tables that the view consists of, 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 comprise the view are modified, the materialized view will recompute the results the next time the view is referenced and refreshes the temporary table with the new results. The materialized view temporary results table exists for as long as the view is in existence. When a view is dropped or deleted, then the temporary results table is also deleted.

Materialized Views Operating Details

User Interface for Materialized Views

Benefits of Materialized Views

Materialized View Example

Materialized Views Operating Details

A materialized view can be created only at the time the SQL view is created. This feature is available only through the SPD Server 4.5 SQL pass-through facility. A new keyword Materialized is added to the Create View syntax that identifies the view to be created as a materialized view. When a materialized view is created, the Create View operation does not complete until the temporary results table is populated. This can add substantial time to the execution of Create View.

Each time a created materialized view is referenced in an SQL statement, there is a check to determine whether any of the input tables used to produce the temporary results table have been modified. If not, the temporary table is substituted in place of the vie w file within the SQL statement. If any of the input tables have been modified, the SQL statement executes without this substitution so it acts as if it is a standard SQL view reference. There is also a background thread launched at this time that is independent of the SQL statement execution, which refreshes the temporary results table. Until this refresh is completed, any incoming references to the view is treated as standard view references.

Creating a standard SQL view results in a view file being created in the specified domain with the name <viewname>.view.0.0.0.spds9. Creating a materialized view results in an additional SPD Server table being created in the same domain as the view file with the name format <.viewname>.mdfspds9 and corresponding .dpf files <.viewname>.dpfspds9. The materialized view table is not visible or accessible to the user 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.

User Interface for Materialized Views

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

EXECUTE (Create Materialized View <viewname> as Select ) BY [sasspds | alias];

All other references to the view follow the existing SQL syntax, whether it is a standard SQL view or a materialized view. The Materialized keyword is used only in the Create statement. For example, to drop a materialized view, you would use the following syntax.

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

If any of the input tables to a materialized view are modified, the next time the view is referenced, a refresh is performed on the materialized view table. You can use an spdsserv.parm file option setting to specify the time delay before the materialized view table is refreshed.

MVREFRESHTIME=<number-of-seconds> ;

Where <number-of-seconds> specifies the number of seconds before the refresh starts. You can set the MVREFRESHTIME= option to any integer value between 0 and 86400. The default MVREFRESHTIME= specification is 30 seconds.

The reason that a time delay might be necessary before refreshing a materialized view table is to prevent processor thrashing. Processor thrashing might occur if you refresh the materialized view table when other processes are concurrently processing updates to the tables that are used in the view. If your computing environment does not perform multiple concurrent table updates, then you can set MVREFRESHTIME=0 and eliminate any time delay associated with materialized view refreshes.

Benefits of Materialized Views

Creating a materialized view instead of a Standard SQL 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 using 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 less, a query referencing the materialized view now takes seconds. Previously using the standard view operation s, every time the view was referenced would result in 20 minutes of execution time. The performance benefits should be measured on a case by case basis.

The decision of whether to use a standard view or a materialized view can be primarily driven by 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 be fore any updates can occur, then the materialized view should provide superior performance. In cases where the defined view can be created very quickly, there is probably not a need for using a materialized view. If the input tables are frequently updated in comparison to how often the view is referenced, a standard view would probably be more efficient.

Materialized View Example

The following code shows the creation and use of a materialized view. The input tables X and Z are created with X having three columns a,b,c and Z having four columns a,b,c,d respectively.

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);
quit;