SASHELP 视图与 SQL 字典表的性能 [英] Performance of SASHELP views versus SQL dictionary tables

问题描述

为什么 SAS 使用例如 sashelp.vcolumn 与等效的 SQL 表 dictionary.columns 从数据步骤视图创建数据集需要更长的时间?

Why does it take longer for SAS to create a dataset from a data step view using, for example, sashelp.vcolumn versus the equivalent SQL table dictionary.columns?

我使用 fullstimer 做了一个测试，它似乎证实了我对性能差异的怀疑.

I did a test using fullstimer and it seems to confirm my suspicion of performance differences.

option fullstimer;

data test1;
    set sashelp.vcolumn;
    where libname = 'SASHELP' and
        memname = 'CLASS' and
        memtype = 'DATA';
run;

proc sql;
    create table test2 as
    select *
    from dictionary.columns
    where libname = 'SASHELP' and
        memname = 'CLASS' and
        memtype = 'DATA';
quit;

日志摘录:

NOTE: There were 5 observations read from the data set SASHELP.VCOLUMN.
      WHERE (libname='SASHELP') and (memname='CLASS') and (memtype='DATA');
NOTE: The data set WORK.TEST1 has 5 observations and 18 variables.
NOTE: DATA statement used (Total process time):
      real time           0.67 seconds
      user cpu time       0.23 seconds
      system cpu time     0.23 seconds
      memory              3820.75k
      OS Memory           24300.00k
      Timestamp           04/13/2015 09:42:21 AM
      Step Count                        5  Switch Count  0


NOTE: Table WORK.TEST2 created, with 5 rows and 18 columns.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      memory              3267.46k
      OS Memory           24300.00k
      Timestamp           04/13/2015 09:42:21 AM
      Step Count                        6  Switch Count  0

SASHELP 使用的内存稍高，但差别不大.请注意时间——使用 SASHELP 比使用 SQL 字典长 22 倍.肯定不能仅仅因为内存使用的差异相对较小.

The memory used is a little higher for SASHELP but the difference isn't huge. Note the time--it's 22 times longer using SASHELP than with the SQL dictionary. Surely it can't just be due to the relatively small difference in memory usage.

在@Salva 的建议下，我在新的 SAS 会话中重新提交了代码，这次在数据步骤之前运行 SQL 步骤.记忆和时间的差异更加明显:

At @Salva's suggestion, I resubmitted the code in a new SAS session, this time running the SQL step before the data step. The memory and time differences are even more pronounced:

                | sql       | sashelp
----------------+-----------+-----------
real time       | 0.28 sec  | 1.84 sec
user cpu time   | 0.00 sec  | 0.25 sec
system cpu time | 0.00 sec  | 0.24 sec
memory          | 3164.78k  | 4139.53k
OS Memory       | 10456.00k | 13292.00k
Step Count      | 1         | 2
Switch Count    | 0         | 0

推荐答案

其中一些(如果不是全部)是 SQL 和 Data Step 之间的开销差异.例如:

Some (if not all) of this is the difference in overhead between SQL and Data Step. For example:

proc sql;
    create table test2 as
    select *
    from sashelp.vcolumn
    where libname = 'SASHELP' and
        memname = 'CLASS' and
        memtype = 'DATA';
quit;

也非常快.

SAS 页面字典表提供了一些可能是主要解释的信息.

The SAS page about Dictionary Tables gives some information that is likely the main explanation.

查询 DICTIONARY 表时，SAS 启动发现过程收集与该表相关的信息.取决于正在查询的 DICTIONARY 表，这个发现过程可以搜索库、打开表和执行视图.不同于其他 SAS过程和数据步骤，PROC SQL 可以减轻这个过程在启动发现过程之前优化查询.因此，虽然可以访问 DICTIONARY 表使用 SASHELP 获取 SAS 过程或 DATA 步的信息视图，通常使用 PROC SQL 更有效.

When querying a DICTIONARY table, SAS launches a discovery process that gathers information that is pertinent to that table. Depending on the DICTIONARY table that is being queried, this discovery process can search libraries, open tables, and execute views. Unlike other SAS procedures and the DATA step, PROC SQL can mitigate this process by optimizing the query before the discovery 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.

这篇关于SASHELP 视图与 SQL 字典表的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！