topic Re: Fetching data from teradata into sas table taking more time in SAS Programming

Fetching data from teradata into sas table taking more time

Banu — Sun, 11 Feb 2018 02:49:20 GMT

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.

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

Reeza — Sun, 11 Feb 2018 05:05:19 GMT

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.

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

kiranv_ — Sun, 11 Feb 2018 16:34:03 GMT

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;

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

Ksharp — Sun, 11 Feb 2018 12:57:45 GMT

Add some options to fast it.

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

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

Banu — Sun, 11 Feb 2018 13:12:51 GMT

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.

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

LinusH — Sun, 11 Feb 2018 13:24:09 GMT

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.