SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Subsetting in a Teradata query w/ SAS table

Reply
Super User
Posts: 5,256

Subsetting in a Teradata query w/ SAS table

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

Data never sleeps
Super User
Posts: 5,256

Re: Subsetting in a Teradata query w/ SAS table

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.

/L

Data never sleeps
Ask a Question
Discussion stats
  • 1 reply
  • 259 views
  • 4 likes
  • 1 in conversation