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

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

Venkat4 — Wed, 02 Nov 2016 03:43:47 GMT

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?

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

SASKiwi — Wed, 02 Nov 2016 03:58:40 GMT

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

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

Venkat4 — Wed, 02 Nov 2016 04:04:29 GMT

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

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

SASKiwi — Wed, 02 Nov 2016 04:14:40 GMT

Most databases have special temporary space just for this purpose:

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

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.

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

Ksharp — Wed, 02 Nov 2016 11:20:49 GMT

You can use IN operator too.

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


OR Hash Table.

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

LinusH — Wed, 02 Nov 2016 14:28:09 GMT

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.

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

Venkat4 — Wed, 02 Nov 2016 14:32:44 GMT

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.

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

LinusH — Wed, 02 Nov 2016 17:04:14 GMT

I think that if upload to a temp table you should be able to use implicit pass through.

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

SuryaKiran — Wed, 02 Nov 2016 19:58:12 GMT

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.