Limit on Proc Sql table joins?

Reply
Super Contributor
Posts: 418

Limit on Proc Sql table joins?

Hello all. Does anyone know if there is a limit on the number of table joins within Proc SQL when run in a portion of larger code?

I have some code that runs within 1 second in sql server,  however when I run it in SAS it takes > 1 minute. In addition, if I run it as part of a large section of CODE (have other datasets created before and after this section) it actually crashes SAS when it gets to this step.  I have never seen this happen before.

ALl I am trying to do is join the following tables, and the joins are correct (from a sql standpoint).

LIBNAME CRMFR odbc dsn='odbc_mydatabasename' schema=dbo bulkload=yes DBMAX_TEXT=32000;

select distinct VARIABLESMAN

FROM CRMFR.FR_TABLE_LQ lq 

                JOIN CRMFR.FR_Loan_L l

                    ON l.keyID = lq.keyID

                JOIN CRMFR.FR_TABLE_M m

                                ON l.Mkey1ID = m.Mkey1ID

                JOIN CRMFR.FR_TABLE_C c

                                ON l.ckeyidID = c.ckeyidID

                                AND m.ckeyidD = c.ckeyidID

                                AND c.clientName = myclientname

    JOIN CRMFR.FR_TABLE_Q q

       ON lq.qkey3ID = q.qkey3ID

      AND m.Mkey1ID = q.Mkey1ID

Super User
Posts: 3,252

Re: Limit on Proc Sql table joins?

Posted in reply to Anotherdream

There are many reasons why an SQL query submitted from SAS to SQL Server might perform differently to one within SQL Server itself.

As a first step try running your query using SQL Passthru. This will avoid SAS doing some unexpected translations on your code.

Also try setting the READBUFF= option to ensure SAS brings back data from the database in large chunks:

proc sql;

connect to odbc (dsn='odbc_mydatabasename' schema=dbo bulkload=yes DBMAX_TEXT=32000 readbuff = 32767);

  create table test as

  select * from connection to odbc

  ( select *

     From databasetable

     Where selection = ?????

  ) ;

  disconnect from odbc;

quit;

Super Contributor
Posts: 418

Re: Limit on Proc Sql table joins?

Hello Kiwi. I actually did re-write the code to run as a pass through, and it ran in under 1 second. So that is a "solution" to my problem, however my problem isn't actually getting the code to work, I am more interesting the fundamental programming reasons behind this causing SAS to crash. That seems extremely unstable and I would like to know what I did "incorrectly" to avoid it in the future.

Thanks so much!

Ask a Question
Discussion stats
  • 2 replies
  • 187 views
  • 0 likes
  • 2 in conversation