BookmarkSubscribeRSS Feed
LinusH
Tourmaline | Level 20

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
1 REPLY 1
LinusH
Tourmaline | Level 20

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 833 views
  • 4 likes
  • 1 in conversation