SAS/ACCESS JDBC : double outer join sends multiple queries to source.

[sdut]

Hi

We have recently added SAS/ACCESS for JDBC to our environment (9.4m7) and we are facing a behaviour for which we would like to have a workaround.

Consider this code:

libname mylib jdbc classpath="D:\LIB"
class="xxxx"
URL="xxxx"
user=xxxx
password=xxxx; /*LIBNAME BASIC STATEMENT IS CORRECT : WE CAN ACCESS ITS CONTENTS*/

proc sql;
create table ttt as 
	select * 
	from mylib.a 
	LEFT join mylib.b on a.age =b.age 
	LEFT JOIN mylib.c on c.key = a.key
	where a.field < 1000;

quit;

When running it, SAS split the two join and sends three queries to the source system, as opposed to just pushing that query down to the database system.

It causes a lot of performance issues, our database tables can get quite huge to retrieving the entire thing takes a very long time.

Is there a way to "hint" at SAS that the database is able to handle that double outer join?

If we do inner joins instead of outer joins, then the sas query is pushed in a single sql query to our database.

Thank you in advance !

[SASKiwi]

Try SQL Passthru where you write your database's SQL syntax inside the brackets:

proc sql;
 connect using mylib;
  create table ttt  as 
  select * from connection to mylib
  (SELECT *
   FROM a as A
   LEFT JOIN b as B
   ON A.age = B.age
   LEFT JOIN c as C
   ON A.key = C.key
   where a.field < 1000
   )
  ;
quit;

[LinusH]

According to the documentation, SAS says that some DBMS doesn't support where statements in combination with outer joins.

This is not specifically stated under the JDBC section, but I could be worth to try without it to see if that is the issue.

Also, set these option, it might give som information why SAS/ACCESS to JDBC/DB doesn't accept your query:

options sastrace=',,,d' sastraceloc=saslog nostsuffix msglevel=i;

[sdut]

Hi both,

Thank you for your answers.

@LinusH The DBMS supports that exact SAL statement without issue, we have tried directly with a query software against that database and that works perfectly as intended. The log trace didn't show anything valuable. Is there no way to tell the SAS execution engine that this DBMS is able to handle that type of statement? I'm fairly new to SAS so any help on that end would be greatly appreciated.

@SASKiwi SQL Passthrough does the trick, however that would not work for joins done with the SAS EG GUI.

[SASKiwi]

I suggest you open a track with SAS Tech Support on this and then post any updates back here. Do you by any chance have SAS/ACCESS to ODBC where you can try exactly the same thing? SAS's JDBC engine is relatively new whereas ODBC has been around a long time. 

[sdut · Accepted solution]

Hi

FYI, an update on this.

I have opened a support track.

It highlighted a problem with the driver provided by our database vendor. The vendor provided us with a hotfix and that fixed the original problem.

However, it uncovered a limitation of the SAS/ACCESS JDBC and ODBC interfaces. They do not support mixing outer with inner joins. In that case the queries are broken down in multiple sub-queries sent to the database, which causes a lot of problems for us.

I have opened a SAS ballot to request an enhancement to support that outer/inner join mixing - or at least to give a flag to indicate that the underlying data source supports inner and outer joins together. You're obviously all welcome to upvote it 😊

https://communities.sas.com/t5/SASware-Ballot-Ideas/JDBC-SAS-ACCESS-More-options-for-query-pushdown-...

Thanks for your help !