[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    Re: Who can give me some advices SASHELP View and Dictionary
From:       Jack Hamilton <jfh () STANFORDALUMNI ! ORG>
Date:       2010-01-31 17:58:05
Message-ID: AA89D7D5-7952-4532-A411-A6487961DCAE () alumni ! stanford ! org
[Download RAW message or body]

Yes, it is clear that SAS Institute thinks that dictionary views execute more \
quickly, but I cannot read "X is faster than Y" as "You should not use Y".  There are \
various reasons why execution speed is not the only factor in choosing a particular \
coding solution.

And, dictionary tables are only a partial replacement for sashelp views.  The \
complete replacement is dictionary views + a PROC SQL step to create a view that can \
be used elsewhere.  That's more code to write and keep track of.

=====
47   data test1;
48      set sashelp.vlibnam;
49   run;

NOTE: There were 20 observations read from the data set SASHELP.VLIBNAM.
NOTE: The data set WORK.TEST1 has 20 observations and 11 variables.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.03 seconds


50
51   proc sql;
52      create table test2 as
53         select * from dictionary.libnames;
NOTE: Table WORK.TEST2 created, with 20 rows and 11 columns.

54   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds
=====

In this case, use of the dictionary table saves one one-hundredth of a second of \
execution time but requires four times as much code.  It is hard to see the why the \
dictionary table solution might be considered superior.


--
Jack Hamilton
jfh@alumni.stanford.org
Caelum non animum mutant qui trans mare currunt.

On Jan 31, 2010, at 6:30 am, Frank DiIorio wrote:

> On Jan 29, 9:19 pm, j...@STANFORDALUMNI.ORG (Jack Hamilton) wrote:
> > On Jan 29, 2010, at 8:48 am, Fehd, Ronald J. (CDC/CCHIS/NCPHI) wrote:
> > 
> > > sashelp.views are deprecated
> > 
> > Where does SAS Institute say that sashelp views are deprecated?
> > 
> 
> I never heard that term per se, but it's clear from the V9
> documentation that they encourage use of SQL (using Tables) over non-
> SQL (using Views).  Back in the V6 days, when the Tables were
> introduced, Nancy Michal (now Nancy Cole) and I wrote a paper
> describing the structure and uses of some of the Tables.  We also did
> some comparisons of efficiency running VMS, Unix, Windows, and OS/2,
> comparing timings of creating a report from CATALOGS using SQL, a DATA
> step, PROC PRINT, etc.  The SQL-based scenario (create a WORK dataset,
> then use PRINT)  ran, on average, in 1/4 the time of the next-fastest
> approach.
> 
> Here's an excerpt from the V9.1 documentation:
> 
> DICTIONARY Tables and Performance
> 
> When you query a DICTIONARY table, SAS gathers information that is
> pertinent to that table. Depending on the DICTIONARY table that is
> being queried, this process can include searching libraries, opening
> tables, and executing views. Unlike other SAS procedures and the DATA
> step, PROC SQL can improve this process by optimizing the query before
> the select process is launched. Therefore, although it is possible to
> access DICTIONARY table information with SAS procedures or the DATA
> step by using the SASHELP views, it is often more efficient to use
> PROC SQL instead.
> 
> For example, the following programs both produce the same result, but
> the PROC SQL step runs much faster because the WHERE clause is
> processed prior to opening the tables that are referenced by the
> SASHELP.VCOLUMN view:
> 
> data mytable;
> set sashelp.vcolumn;
> where libname='WORK' and memname='SALES';
> run;
> 
> proc sql;
> create table mytable as
> select * from sashelp.vcolumn
> where libname='WORK' and memname='SALES';
> quit;
> 
> Note:   SAS does not maintain DICTIONARY table information between
> queries. Each query of a DICTIONARY table launches a new discovery
> process.
> 
> If you are querying the same DICTIONARY table several times in a row,
> you can get even faster performance by creating a temporary SAS data
> set (with the DATA step SET statement or PROC SQL CREATE TABLE AS
> statement) with the information that you desire and run your query
> against that data set.


[prev in list] [next in list] [prev in thread] [next in thread]