05-14-2013 06:12 PM
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
05-14-2013 09:52 PM
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:
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 *
Where selection = ?????
disconnect from odbc;
05-15-2013 04:19 PM
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!