BookmarkSubscribeRSS Feed
Banu
Obsidian | Level 7

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.

 

 

 

5 REPLIES 5
Reeza
Super User

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_
Rhodochrosite | Level 12

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
Super User

Add some options to fast it.

libname x ..... readbuff=10000 bulkload=yes dbcommit=10000;
Banu
Obsidian | Level 7
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
Tourmaline | Level 20
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2543 views
  • 1 like
  • 5 in conversation