topic Re: proc SQL on Dictionary VS. data step on sashelp: why is data step so slow in SAS Programming

proc SQL on Dictionary VS. data step on sashelp: why is data step so slow

dirkvk — Tue, 04 Dec 2018 12:28:47 GMT

Hi all,

I want to return the info in the sashelp.vtable/dictionary.tables for a specific dataset.

Works fine but why does one take 11 minutes and the other not even a second?

I was expecting some difference but not this much of a difference.

Note though that at this location they defined an enormous amount of libs and datasets at start-up.

Just curious for your thoughts on this...

%macro DSInfo(DSInfo,LibName,DSName);
data &DSInfo;
  set sashelp.vTable (where = (libname = %upcase("&LibName") and memname=upcase("&DSName") ) );
run;
%mend;

%macro DSInfo2(DSInfo,LibName,DSName);
proc sql noprint;
  create table &DSInfo as select * from dictionary.tables where libname = upcase("&LibName") and memname = upcase("&DSName");
quit;
%mend;

%let StartTime = %sysfunc(time());
%DSInfo(DSInfo,WORK,_prodsavail);
%let EndTime = %sysfunc(time());
* Anders komt de LOG-booschap niet als 1 geheel: source even uitschakelen;
options nosource;
%put ***********************************************************************;
%put Programma duur: %sysfunc(putn(%sysevalf(&EndTime-&StartTime),time10.2));
%put ***********************************************************************;
options source;

%let StartTime = %sysfunc(time());
%DSInfo2(DSInfoSQL,WORK,_prodsavail);
%let EndTime = %sysfunc(time());
* Anders komt de LOG-booschap niet als 1 geheel: source even uitschakelen;
options nosource;
%put ***********************************************************************;
%put Programma duur: %sysfunc(putn(%sysevalf(&EndTime-&StartTime),time10.2));
%put ***********************************************************************;
options source;

Re: proc SQL on Dictionary VS. data step on sashelp: why is data step so slow

Kurt_Bremser — Tue, 04 Dec 2018 12:46:09 GMT

Which SAS version is implemented?

I have the impression that SAS has improved the way a where condition in the data step is handed over to the SQL view, because with earlier SAS versions I found that using the view led to a read through all datasets in all libraries before the where condition was applied.

Re: proc SQL on Dictionary VS. data step on sashelp: why is data step so slow

dirkvk — Tue, 04 Dec 2018 13:19:02 GMT

Hi Kurt,

Thanks 4 your reply. I am on SAS 9.3 at this client. Running in EG 5.1 if that matters.

Re: proc SQL on Dictionary VS. data step on sashelp: why is data step so slow

Kurt_Bremser — Wed, 05 Dec 2018 08:09:31 GMT

@dirkvk wrote:

Hi Kurt,

Thanks 4 your reply. I am on SAS 9.3 at this client. Running in EG 5.1 if that matters.

They should seriously consider upgrading to 9.4M5. I have not experienced a performance penalty with the view in a similar situation:

data class;
set sashelp.class;
run;

data info1;
set sashelp.vtable (where=(libname = upcase("work") and memname= upcase('class')));
run;

That took just a fraction of a second, even though I have lots of libraries with several hundred thousand datasets active.

Re: proc SQL on Dictionary VS. data step on sashelp: why is data step so slow

ballardw — Tue, 04 Dec 2018 16:13:43 GMT

You didn't indicate which one took longer.

I suspect that the SQL version ran quicker as I have seen that occur and I believe that it has to do with the nature of a SAS VIEW, which the sashelp.vtable is and optimizations used with the DICTIONARY equivalent.

I note that on my system that the data step approach can create notes such as

NOTE: The map data sets in library MAPSGFK are based on the digital maps from GfK GeoMarketing
      and are covered by their Copyright. For additional information, see
      http://support.sas.com/mapsonline/gfklicense.

that the SQL dictionary table doesn't.

For giggles you should also try Proc SQl addressing the sashelp.vtable instead of the Dictionary table as another comparison. On my system the time for the data step and Proc Sql using sashelp.vtable are nearly identical though CPU time is slightly less for Proc SQL.

Re: proc SQL on Dictionary VS. data step on sashelp: why is data step so slow

dirkvk — Sat, 01 Feb 2020 11:20:22 GMT

Hey, just now I realized that I did not answer here - It was the data step on the view that took 11 minutes, :-), as you suspected.

Thanks all 4 your input!