03-19-2015 09:35 AM
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
03-19-2015 09:59 AM
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.