Proc SQL Query Tuning - Running 6 hours

Yen — Mon, 17 May 2021 15:41:43 GMT

The below query takes 6 hours to run. Can any one help with tuning?

options nocenter compress=yes;
libname lib 'path';

%libkrb5(uwork,teradata,dbname);

proc sql;
%krb5(rchtera);
execute (collect statistics on teradata.table1 index (p1)) by teradata;
execute (collect statistics on teradata.table1 column (p1)) by teradata;
execute (collect statistics on teradata.table2 index (o1)) by teradata;
execute (collect statistics on teradata.table2 column (o1)) by teradata;
create table lib.t1 (bufsize=1073741824 bufno=max) as /*t1 has195395189 rows and 7 columns*/
select * from connection to teradata(
select distinct a.p1
,b.o1
,c.c1
,c.c2
,c.c3
,c.c4
,c.c5
from teradata.table1 a /* has 21371007 rows and 51 columns */
,vbmr.pty_ofr_dtl b
,teradata.table2 c /* has 198 rows and 9 column */
where a.p1 = b.p1
and b.o1 = c.o1
and b.o2 = '9999-12-31'
and b.o3 = 1
and b.o4 = 0
and b.o5 = 0
);
disconnect from teradata;
quit;

Re: Proc SQL Query Tuning - Running 6 hours

Kurt_Bremser — Mon, 17 May 2021 15:54:00 GMT

As all this happens in and from Teradata, there's two options:
- the Teradata actions and query take so long
- your network is the bottleneck in fetching the result

topic Proc SQL Query Tuning - Running 6 hours in SAS Programming

Proc SQL Query Tuning - Running 6 hours

Re: Proc SQL Query Tuning - Running 6 hours