Date: Fri, 20 Oct 2000 17:00:00 -0400
Reply-To: HERMANS1 <HERMANS1@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: HERMANS1 <HERMANS1@WESTAT.COM>
Subject: Re: sql temporary work file
Content-Type: text/plain; charset=US-ASCII
The SAS System creates temporary system files to hold intermediate results of
SQL queries. I have seen this error message when results of sorts or indexes
require more disk space than the system can provide. The results you get depend
on the dimensions of the FF table as well as the P_AGENDA table of 2.6M rows.
You'll need more storage space (memory and disk) or a more efficient join method
to complete the query.
I'd check first to make sure that the NUCCOM key doesn't repeat in FF or in
P_AGENDA | CNCON='AGE'. Repeats on both sides produce lossless joins that
multiply the size of result tables.
A number of query improvements might make it more efficient. Results may depend
on your platform, system configuration, and the version of SAS. This problem
reminds me of the BETWEEN bottleneck in earlier versions for which Howard
Schreier provided a solution. The BETWEEN operator in V8 should work OK.
Change the date range constraint to a BETWEEN if you are using V8. In fact, it
would do no harm and might improve the query if you rewrite it as [untested,
exclude angle brackets]
CREATE TABLE USAGE.TEMP AS
SELECT <distinct> A.NUPERS,A.CDNIVS1,B.DTCON FORMAT=DDMMYY10.
FROM (select <distinct> NUCCOM,NUPERS,CDNIVS1 from FF) A
(select <distinct> NUCCOM,DTCON from STATG1.P_AGENDA
where CNCON='AGE' and DTCON BETWEEN '30SEP2000'D and
(I have put the DISTINCT option in angle brackets to indicate that whether you
include it in any one or the other locations may change the meaning of the
query. Your data model dictates if and where you should eliminate duplicates in
each class. In any event, the DISTINCT option usually generates a sort or index
with consequences for memory and disk usage.)
Your posting does not tell us how you want to reduce the sources of
A.NUPERS,A.CDNIVS1,B.DTCON to distinct class values. If you can reduce the
tables being joined to distinct instances of the classes A.NUPERS,A.CDNIVS1 and
B.DTCON prior to the join, SQL can index on NUCCOM and complete the query more
efficiently. The explicit select lists (projections) in the subqueries that
yield A and B may help but cannot hurt the efficiency of the query no matter
where you place the DISTINCT options. SAS has improved its query "optimization"
methods, but for large scale problems it pays to apply a bit more effort to
making subsets and constraints explicit in database design and query
From: albert.venetitay@CTICEP.CAISSE-EPARGNE.FR at Internet-E-Mail
Sent: Friday, October 20, 2000 3:58 PM
To: SAS-L@LISTSERV.UGA.EDU at Internet-E-Mail
Subject: sql temporary work file
HI all ,
I'm getting the message ' proc sql temporary work file has grown'
inspite of increasing my work file ?
Does anybody knows what is going wrong with this program ?
Does the sql temporary file uses the same allocation of the work
file, or it uses some internal sort work files ?
Can anybody in this list , have seen this problem and could share
their experiences ?
Thanks a lot.
Any help and suggestions will be greatly appreciated ?
Thanks in advance ,
Kind regards ,
Following is the code and the proc contents of the file .
14 +CREATE TABLE USAGE.TEMP AS
15 +SELECT DISTINCT A.NUPERS,A.CDNIVS1,B.DTCON FORMAT=DDMMYY10.
16 +FROM FF A,STATG1.P_AGENDA B
17 +WHERE A.NUCCOM=B.NUCCOM
18 +AND B.CNCON='AGE'
19 +AND '30SEP2000'D >= B.DTCON >= '01OCT1999'D;
NOTE: PROC SQL TEMPORARY WORK FILE HAS GROWN TOO LARGE.
NOTE: TABLE USAGE.TEMP CREATED, WITH 0 ROWS AND 3 COLUMNS.
PDBOUT.P_AGENDA OBSERVATIONS: 2596476
5:05 WEDNESDAY, OCTOBER 4, 2000 OBSERVATION LENGTH: 59
14:30 FRIDAY, OCTOBER 20, 2000 DELETED OBSERVATIONS: 0
REUSE SPACE: NO
40, avenue Jean Jaurès
93176 Bagnolet Cedex
E-Mail : email@example.com
ou : firstname.lastname@example.org