bufsize and proc sql

Reply
Occasional Contributor
Posts: 11

bufsize and proc sql

Hi,


we are trying to increase the bufsize for newly created tables.

Please see the following example. The system value is set to it's maximum of almost 2G:


proc sql;

create table test1 (bufsize = max)

(

DATE num format=YYMMDD10. informat=YYMMDD10.,

TEST_KEY char(20) format=$20.,

AMOUNT num format=8.2 length=6

  );

describe table test1;

quit;

This creates the following output:

NOTE: SQL table WORK.TEST1 was created like:

create table WORK.TEST1( bufsize=2630656 )

  (

   DATE num format=YYMMDD10. informat=YYMMDD10.,

   TEST_KEY char(20) format=$20.,

   AMOUNT num format=8.2

  );

Where does this value of ~ 2MB come from? When we increase the amount of test data, this value is going up to ~8MB, but not more.

This is causing us I/O problems.


My questions are:


1. How does SAS create the bufsize value of ~2MB?

2. How can i force it to use a bigger value of e.g. 1GB?


Thanks in advance!

Super Contributor
Posts: 253

Re: bufsize and proc sql

I get the same effective maximum (~2.5MB).  I do get a warning, saying that my maximum (2g) is higher than the allowable limit (around 1g).  Do you get the same?  That's with a memsize of 6g, which doesn't surprise me - from other evidence I can see that SQL uses around 5x as much space (1g * 5) as it needs.

Ask a Question
Discussion stats
  • 1 reply
  • 504 views
  • 0 likes
  • 2 in conversation