sas - 使用 SPDE 的 proc transpose 运行时间比 v9 库长约 60 倍

Question

我一直在将所有数据集移动到 SPDE 库中，因为我在所有方面都体验到了出色的性能提升。一切，直到运行 proc transpose。与存储在普通 v9 库中的相同数据集相比，在 SPDE 数据集上执行所需的时间约为 60 倍。数据集按 item_id 排序。它正在被读/写到同一个库。

有谁知道为什么会这样？我是否错过了一些关于 SPDE 和 Proc Transpose 不能很好地协同工作的重要信息？

SPDE 库

MPRINT(XMLIMPORT_VANTAGE):   proc transpose data = smplus.links_response_mechanism out = smplus.response_mechanism (drop = _NAME_) 
prefix = rm_;
MPRINT(XMLIMPORT_VANTAGE):   by item_id;
MPRINT(XMLIMPORT_VANTAGE):   id lookup_code;
MPRINT(XMLIMPORT_VANTAGE):   var x;
MPRINT(XMLIMPORT_VANTAGE):   run;

NOTE: There were 5866747 observations read from the data set SMPLUS.LINKS_RESPONSE_MECHANISM.
NOTE: The data set SMPLUS.RESPONSE_MECHANISM has 3209353 observations and 14 variables.
NOTE: Compressing data set SMPLUS.RESPONSE_MECHANISM decreased size by 37.98 percent.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           28:27.63
      cpu time            28:34.64

V9 库

MPRINT(XMLIMPORT_VANTAGE):   proc transpose data = mplus.links_response_mechanism out = mplus.response_mechanism (drop = _NAME_) 
prefix = rm_;
MPRINT(XMLIMPORT_VANTAGE):   by item_id;
68                                                         The SAS System                             02:00 Thursday, August 8, 2013

MPRINT(XMLIMPORT_VANTAGE):   id lookup_code;
MPRINT(XMLIMPORT_VANTAGE):   var x;
MPRINT(XMLIMPORT_VANTAGE):   run;

NOTE: There were 5866747 observations read from the data set MPLUS.LINKS_RESPONSE_MECHANISM.
NOTE: The data set MPLUS.RESPONSE_MECHANISM has 3209353 observations and 14 variables.
NOTE: Compressing data set MPLUS.RESPONSE_MECHANISM decreased size by 27.60 percent. 
      Compressed is 32271 pages; un-compressed would require 44572 pages.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           28.76 seconds
      cpu time            28.79 seconds

Answer 1

在我看来，PROC TRANSPOSE 和 SPDE 存在一些问题。这是一个简单的SSCCE，它有很大的不同；没有你的那么重要，但在某种程度上，这可能是桌面上的一个因素，首先没有特别实质性的性能调整。听起来像是调用 SAS 技术支持。

libname spdelib spde 'c:\temp\SPDE Main' 
    datapath=('c:\temp\SPDE Data' 'd:\temp\SPDE Data')
    indexpath=('d:\temp\SPDE Index')
    partsize=512;

libname mainlib 'c:\temp\';


data mainlib.bigdata;
do ID = 1 to 1500000;
  do _varn=1 to 10;
    varname=cats("Var_",_varn);
    vardata=ranuni(7);
    output;
  end;
end;
run;
data  spdelib.bigdata;
do ID = 1 to 1500000;
  do _varn=1 to 10;
    varname=cats("Var_",_varn);
    vardata=ranuni(7);
    output;
  end;
end;
run;
*These data steps take roughly the same amount of time, around 30 seconds each;

proc transpose data=spdelib.bigdata out=spdelib.transdata;
by id;
id varname;
var vardata;
run;
*Run a few times, this takes around 3 to 4 minutes, with 1.5 minutes CPU time;

proc transpose data=mainlib.bigdata out=mainlib.transdata;
by id;
id varname;
var vardata;
run;
*Run a few times, this takes around 30 to 45 seconds, with 20 seconds CPU time;