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
Comments: To: albert.venetitay@CTICEP.CAISSE-EPARGNE.FR
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
                INNER JOIN
               (select <distinct> NUCCOM,DTCON from STATG1.P_AGENDA
                where CNCON='AGE' and DTCON BETWEEN '30SEP2000'D and
'01OCT1999'D
               ) B
          on A.NUCCOM=B.NUCCOM
          ;

(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
construction. Sig

-----Original Message-----
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 ,
   Albert Venetitay


Following is the code and the proc contents of the file  .


 13        +

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.



                    CONTENTS PROCEDURE

 PDBOUT.P_AGENDA                          OBSERVATIONS:         2596476
 DATA
VARIABLES:            8
 V609

INDEXES:              1
 5:05 WEDNESDAY, OCTOBER 4, 2000          OBSERVATION LENGTH:   59
 14:30 FRIDAY, OCTOBER 20, 2000                  DELETED OBSERVATIONS: 0

                                          COMPRESSED:           YES
                                          REUSE SPACE:          NO
                                          SORTED:               NO



____________________________________________

CTICEP
40, avenue Jean Jaurès
93176 Bagnolet Cedex

Albert VENETITAY

E-Mail : albert.venetitay@cticep.caisse-epargne.fr
ou   : ave@cticep.caisse-epargne.fr