DATA Step, Macro, Functions and more

Need to filter a 1.5 billion rows teradata table with 1 mil account numbers from a sas table

Reply
Contributor
Posts: 40

Need to filter a 1.5 billion rows teradata table with 1 mil account numbers from a sas table

I have a teradata table with 1.5 billion rows of data. I need to filter this table on 1+ million account numbers, so I can only get those rows from the teradata table. The second table containing only the account numbers is in SAS locally obtained from a totally different DW.

 

I thought of using "intersect", but the issue is that the first table has several columns, and the second table got only one column containing account numbers, so it won't work. Then I thought of adding all account numbers into a macro and pass that in the explicit passthrough. But then the macro values will go beyond 32,767 character limit.

 

It seems I have to use implicit passthrough, but I want this to be done quick not taking hours.

Is there any other ways this can be accomplished within a matter of minutes vs. hours?

Super User
Posts: 3,260

Re: Need to filter a 1.5 billion rows teradata table with 1 mil account numbers from a sas table

Load your SAS table into Teradata as a temporary table and do the join there.

Contributor
Posts: 40

Re: Need to filter a 1.5 billion rows teradata table with 1 mil account numbers from a sas table

Thanks, I forgot to mention the teradata got only read access, no write access to this production db.  

Super User
Posts: 3,260

Re: Need to filter a 1.5 billion rows teradata table with 1 mil account numbers from a sas table

Most databases have special temporary space just for this purpose:

 

http://support.sas.com/documentation/cdl/en/acreldb/69039/HTML/default/viewer.htm#n0irpkyp22l7vzn1il...

 

Check with your Teradata DBA to see if you can have access to this. This is the most efficient method - any other cross-database technique will be extremely slow.

Super User
Posts: 10,046

Re: Need to filter a 1.5 billion rows teradata table with 1 mil account numbers from a sas table

You can use IN operator too.

select *
 from tera 
  where id in (select id form sas);


OR Hash Table.
Super User
Posts: 5,440

Re: Need to filter a 1.5 billion rows teradata table with 1 mil account numbers from a sas table

Sorry @Ksharp, those are not very good ways to so it. The result is that all 1.5 billion rows are transferred to the local SAS session.
TD temporary table should be the way to evaluate first.
Data never sleeps
Contributor
Posts: 40

Re: Need to filter a 1.5 billion rows teradata table with 1 mil account numbers from a sas table

Thanks to both. I already tried that in clause using implicit passthrough like you stated, but it didn't finish and I fed up of it, then stopped. We do have write access to a different database within teradata, so I think I will load the 1+mil account numbers into a teradata db that we have access to write, then use that in the in clause filter via explicit passthrough.

 

I didn't know about the temporary table space - but that is a good info. Thanks again.

Super User
Posts: 5,440

Re: Need to filter a 1.5 billion rows teradata table with 1 mil account numbers from a sas table

I think that if upload to a temp table you should be able to use implicit pass through.
Data never sleeps
Frequent Contributor
Posts: 141

Re: Need to filter a 1.5 billion rows teradata table with 1 mil account numbers from a sas table

To speed up the database access use EXISTS statement in SQL rather than the IN statement

 

Select *from table_a a

where exists(select*from orders o

where a.prod_id=o.prod_id);

 

It is best to write a SQL statement with subquery.

Thanks,
Suryakiran
Ask a Question
Discussion stats
  • 8 replies
  • 449 views
  • 3 likes
  • 5 in conversation