Fetching data from teradata into sas table taking more time

[Banu]

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.

[Reeza]

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.

---

[kiranv_]

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;

[Ksharp]

Add some options to fast it.

libname x ..... readbuff=10000 bulkload=yes dbcommit=10000;

[Banu]

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.

[LinusH]

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.