topic Joining Heterogeneous Data with proc FEDSQL is very slow in SAS Procedures

Joining Heterogeneous Data with proc FEDSQL is very slow

Sotan — Tue, 10 Jul 2018 20:42:49 GMT

I am able to run proc FEDSQL on a hadoop environment using SAS interface to Impala. Following a SAS example on page 42 of “SAS® 9.4 FedSQL Language Reference, Fifth Edition”, I joined a SAS table and a hadoop table. The query is very slow. Has anyone had experience with joining a SAS table and a database table using proc FEDSQL?

Re: Joining Heterogeneous Data with proc FEDSQL is very slow

LinusH — Tue, 10 Jul 2018 22:10:51 GMT

I don't know if there's a difference in behaviour between FedSQL and SQL but typically the join will take place in the SAS session.
This can be avoided/optimized by either uploading your SAS table to Hadoop, or try the DBKEY option.

Re: Joining Heterogeneous Data with proc FEDSQL is very slow

Sotan — Mon, 16 Jul 2018 15:24:19 GMT

Thank you Linus for the answer. I have used dbkey option or inserted SAS data to the database table with proc SQL.

Loading SAS data to a database table works very well but I need IT to create the DB table for me. I thought that the new proc FEDSQL gave me more flexibility in joining SAS table with database tables.