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
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?
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!
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: