topic Re: SAS performance in SAS Programming

SAS performance

RupaJ — Wed, 23 Oct 2019 19:57:51 GMT

Hello,

I am trying to run a query (with PROC SQL accessing Teradata) on SAS and the same query I am executing on Teradata database directly. On Teradata database, the query runs in seconds, however on SAS it is running for more than 20 minutes. I am wondering how SAS is processing the request. If everything is happening on the Teradata side and if SAS is just bringing the results, why would it take this long.

Now both Teradata and SAS has around 1 TB work space for query processing.

Thanks

Re: SAS performance

ChrisNZ — Wed, 23 Oct 2019 20:29:17 GMT

It's impossible to answer without knowing the query.

Implicit or explicit pass-through? Any functions used? etc.

Do you know about the SASTRACE option, which can show you the query sent to Teradata?

Re: SAS performance

RupaJ — Fri, 01 Nov 2019 18:33:15 GMT

hello @ChrisNZ

Sorry, my bad. But I could figure it out myself. So below is the sample query. So when I run this on Teradata, it runs in 40 sec whereas on SAS it took 8 minutes. From TD logs, I see that the only SELECT from proc sql runs on TD , SAS then brings back the data set and creates the table on it's work space which is why it takes 8 minutes.

Query : 1

proc sql;

drop table work.table1;

create table work.table1 as

select * from trlib.Employee t1

inner join

trlib.Salary t2

on t1.empid = t2.empid

quit

I then figured there is a way to directly run the whole thing on Teradata (in it's spool space )and create the table on TD itself and then bring back the results on SAS for further processing /analysis. The same query actually ran in 30 sec from SAS as opposed to 8 mins when run on SAS work space.

Query 2:

proc sql;

connect to teradata (user="john" server="apple" pw="test123" mode="teradata" );

execute (create table as select * from …. ) by teradata;

quit;

Thanks!

Re: SAS performance

ChrisNZ — Fri, 01 Nov 2019 22:01:00 GMT

1. There is no reason whatsoever that the query you describe should run in SAS when it can run in Teradata.

proc sql;
create table work.table1 as
select * from teralib.Employee t1
inner join
teralib.Salary t2
on t1.empid = t2.empid;

should run exactly the same as

proc sql;
connect using TERALIB;
create table work.table1 as
select * from connection to TERALIB 
 (select * from Employee t1
  inner join
  Salary t2
  on t1.empid = t2.empid);

If it doesn't you should contact tech support.

2. This:

execute (create table as select * from …. ) by teradata;

creates a teradata table, not a SAS table.