Subsetting in a Teradata query w/ SAS table

LinusH — Thu, 19 Mar 2015 13:35:33 GMT

Hi,

i got a similar issue as http://communities.sas.com/message/112605.

But I can't resolve by using information from that thread.

I have two tables in TD, lets say Customer and Sales, about the the same size (couple of 10'' recs).

I'm able to push down an implicit join with a subset on Sale date.

Result is a around 10' for a date, and the query is around 2 mins.

I wish to further optimize via pushing down a list om of Sales Agents (around 300) for sub-setting.

I tried the MULTISOURCE_OPTS=IN_CLAUSE, which resulted in in five time increase of query time.

Now I'm trying to get the Sales Agent list in a TD temporary table instead, but can't figure out how to use it in the join.

For an implicit join to occur, the following constraint apply (from SAS/ACCESS doc):

"You must specify the SCHEMA= LIBNAME option to fully qualify each table name in a join for each LIBNAME that you reference"

And temporary tables doesn't use schema.

So, in case of explicit SQL pass-thru, how do I refer between between a global connection (temporary) and an ordinary connection? Haven't found any examples so far.I

Re: Subsetting in a Teradata query w/ SAS table

LinusH — Thu, 19 Mar 2015 13:59:20 GMT

Ok, thank god, or someone else, for DI Studio! 🙂

It seemed that you just didn't specify a schema in the table specification for the temporary table.

Another good new was that the Teradata optimizer liked this better than a long IN-clause, cut the execution time to a third.

The downside is that I have to use TD-specific SQL in the join/where-clause (timestamp and cast()) instead of SAS dt-constants and functions.

So if anyone have an idea to do this using implicit SQL pass-thru, or to make the IN_CLAUSE have better performance, please share.

topic Re: Subsetting in a Teradata query w/ SAS table in SAS Data Management

Subsetting in a Teradata query w/ SAS table

Re: Subsetting in a Teradata query w/ SAS table