DATA Step, Macro, Functions and more

Fetching data from teradata into sas table taking more time

Reply
Contributor
Posts: 43

Fetching data from teradata into sas table taking more time

[ Edited ]

Hi,

 

I need your help to optimize the below query. In my code, query is fetching around 250 millions of data.

Can you suggest me what is the best way to fetch 250 millions data in short time.

 

LIBNAME X "C:\MY_SASDATA";

LIBNAME TD teradata  TDPID=TERADT USER="%SYSGET(USER)"    PASSWORD="%SYSGET(PASSWD)" ;

 

PROC SQL;

CREATE TABLE X.ALL_ACCTS AS    SELECT VAR1, VAR2, VAR3, VAR4, VAR5, VAR5  FROM TD.TABLE1 AS TD 

 WHERE TD.ACCT_NBR =SAS.ACCT_NBR ;

QUIT;

 

Here where clause having 6 conditions. Please help me how can I optimize above query.

 

 

 

Super User
Posts: 22,850

Re: Fetching data from teradata into sas table taking more time

Was your code cut off? There's no 6 conditions or even a join but a WHERE which doesn't make sense because only one table is listed.

 


Banu wrote:

Hi,

 

I need your help to optimize the below query. In my code, query is fetching around 250 millions of data.

Can you suggest me what is the best way to fetch 250 millions data in short time.

 

LIBNAME X "C:\MY_SASDATA";

LIBNAME TD teradata  TDPID=TERADT USER="%SYSGET(USER)"    PASSWORD="%SYSGET(PASSWD)" ;

 

PROC SQL;

CREATE TABLE X.ALL_ACCTS AS   

SELECT VAR1, VAR2, VAR3, VAR4, VAR5, VAR5 

FROM TD.TABLE1 AS TD 

 WHERE TD.ACCT_NBR =SAS.ACCT_NBR ;

QUIT;

 

Here where clause having 6 conditions. Please help me how can I optimize above query.

 

 

 


 

PROC Star
Posts: 499

Re: Fetching data from teradata into sas table taking more time

[ Edited ]

please  use explicit pass through to optimize query and example is shown below and also add fastexport = yes in your connect statement 

 

proc sql;

connect to teradata(server=server user=userid pwd=password fastexport =yes);

create table work.job204 as select * from connection to teradata

(select * from employees

where jobcode=204

and name = 'smith');

quit;

Super User
Posts: 10,618

Re: Fetching data from teradata into sas table taking more time

Add some options to fast it.

libname x ..... readbuff=10000 bulkload=yes dbcommit=10000;
Contributor
Posts: 43

Re: Fetching data from teradata into sas table taking more time

If we create a view by adding this select statement in teradata, and then
if I use create SAS table as select * from Teradata table;.We should not
create teradata table or temp table from SAS(No DDL use from SAS).

Is it reduce time fetching time or any suggestions.
Super User
Posts: 5,827

Re: Fetching data from teradata into sas table taking more time

1. It seems that you have an inner join with a SAS table. If that's filtering the result, a temporary table is needed in TD.
2. Why do want to fetch 250'' rows to SAS? What is the next step? Try to keep as much data and processing in TD as possible.
Data never sleeps
Ask a Question
Discussion stats
  • 5 replies
  • 192 views
  • 1 like
  • 5 in conversation