topic Re: Proc SQL Performance in SAS Programming

Proc SQL Performance

shl007 — Tue, 26 Mar 2019 19:32:06 GMT

Hello - I have a dataset with over 126 million rows. I'm running a proc sql on that table to join to another table with far fewer rows. The proc sql never finishes, even with a composite index. Any ideas for trying to improve the performance? I also looked at the site below, and nothing else seemed to help. Thanks for any tips.

https://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001360977.htm

Re: Proc SQL Performance

ballardw — Tue, 26 Mar 2019 19:42:38 GMT

It would likely help to at least include the code for the SQL and indicate which is the larger data set. Also if any of the sets involved are from another DBMS such as Oracle or DB2 you should indicate that and possibly include the options used for the connection with the database (no password or user name but other options may be important).

And how long have you waited? Depending on the complexity of the query and the type of join a couple of hours may not be excessive.

Re: Proc SQL Performance

Rajesh3 — Tue, 26 Mar 2019 19:46:04 GMT

Hello,

If you are sure that you are not generating cartesian product, maybe you can try an alternative like using arrays or hash objects.

http://support.sas.com/resources/papers/proceedings09/071-2009.pdf

Thanks,

Rajesh.

Re: Proc SQL Performance

shl007 — Tue, 26 Mar 2019 19:48:46 GMT

Here is the SQL - "master_bkp" has the 126 million rows. Using proc datasets in a prior step, I had created a composite index on field1 and field2.

proc sql;

create table test as

select *

from master_bkp a, lookup b

where b.field1 = a.field2 and a.field1 < b.field2

;

quit;

Re: Proc SQL Performance

Reeza — Tue, 26 Mar 2019 20:02:06 GMT

@shl007 wrote:

Here is the SQL - "master_bkp" has the 126 million rows. Using proc datasets in a prior step, I had created a composite index on field1 and field2.

proc sql;

create table test as

select *

from master_bkp a, lookup b

where b.field1 = a.field2 and a.field1 < b.field2

;

quit;

Looks like a cross join instead of a right join.

None of those tables appear to be on a server either, so is this work happening on a server or your computer? If it's on a desktop with 126 million rows, I'm not surprised it won't complete.

proc sql;

create table test as

select * from master_bkp as a

inner join lookup as b

on a.field2=b.field1 and a.field1 < b.field2;

quit;

Re: Proc SQL Performance

shl007 — Tue, 26 Mar 2019 20:04:25 GMT

On the server, not on my desktop. Note: field1 & field2 are key fields in the "lookup" table. Wondering if a hash approach would help.

Re: Proc SQL Performance

Reeza — Tue, 26 Mar 2019 20:23:46 GMT

Did you try the modified query?

Try it by limiting obs for 1,000,000 records with each set of code to see which is performing better.

Hash would likely work, but not my strong suit, so you'll have to wait for someone else to help with that.

@shl007 wrote:

On the server, not on my desktop. Note: field1 & field2 are key fields in the "lookup" table. Wondering if a hash approach would help.

Re: Proc SQL Performance

shl007 — Tue, 26 Mar 2019 20:35:19 GMT

Tried hash and got this error - still searching for other options, but this suggests hash is not a viable solution in this case.

ERROR: Hash object added 2097136 items when memory failure occurred.

FATAL: Insufficient memory to execute DATA step program. Aborted during the EXECUTION phase.

ERROR: The SAS System stopped processing this step because of insufficient memory.

Re: Proc SQL Performance

PGStats — Tue, 26 Mar 2019 20:47:58 GMT

I'm not sure about this, but it might help to define your index on Field2, Field1 , in that order, on master_bkp and on Field1, Field2, in that order, on lookup, so that your equality join condition is the first index field in both tables.

Re: Proc SQL Performance

SASKiwi — Tue, 26 Mar 2019 21:14:35 GMT

Are you using your lookup table as the hash object, not the master table? How many rows are in the lookup table?

Re: Proc SQL Performance

ChrisNZ — Tue, 26 Mar 2019 21:24:23 GMT

The first thing to do when this happens is add the _method option on proc sql, to see what joins are used.

You can then decide on the next step, depending on your expectations vs the reality.

Indexes are probably not used, but you don't know at the moment (and maybe they shouldn't be used, depending on the size of the secondary table). You need to know what's happening -and monitor the effects of any change made- before proceeding, Otherwise you are just stabbing in the dark.

Re: Proc SQL Performance

shl007 — Tue, 26 Mar 2019 21:26:03 GMT

I'm using the master table as the one with the 126 million rows. Below is the hash code that generated that memory error. Again, running on server. Checking with server admin to see if increasing memsize would be a viable solution without adversely affecting anything. Other than that, at a loss ...

data mastertest;

if _n_=1 then do;

declare hash e(dataset: 'master');

e.definekey('key');

e.definedata('datetime', 'detailid', 'datasetid');

e.definedone();

call missing(datetime, detailid, datasetid);

end;

set lookup (obs=15);

drop rc;

rc=e.find();

if rc=0 then key=catx(' ',datetime, detailid, datasetid);

else key='** Not Found';

run;

Re: Proc SQL Performance

ChrisNZ — Tue, 26 Mar 2019 21:28:04 GMT

> Tried hash and got this error - still searching for other options, but this suggests hash is not a viable solution in this case.

Maybe not. Several hash merges may well be faster than a single SQL join involving a large sort or overuse of indexes.

Re: Proc SQL Performance

ChrisNZ — Tue, 26 Mar 2019 21:30:00 GMT

You need to inverse the position of LOOKUP and MASTER in your code.

Read the MASTER table sequentially with statement SET, and load the LOOKUP table in the hash object.

Re: Proc SQL Performance

shl007 — Tue, 26 Mar 2019 21:49:46 GMT

Hello - I assume the _method option on proc sql would require that the SQL actually finishes running? That is part of my issue ... Maybe I run it on a subset of the large dataset, but then is that modeling reality? Thanks for all the tips.

Also, on the response mentioning multiple hashes, what do you mean by multiple? Meaning multiple key fields?

Re: Proc SQL Performance

ChrisNZ — Tue, 26 Mar 2019 22:30:31 GMT

I assume the _method option on proc sql would require that the SQL actually finishes running?

The _method option does not require the query to complete. Look it up.

Also, on the response mentioning multiple hashes, what do you mean by multiple? Meaning multiple key fields?

I mean several successive data steps where you only fetch part of the lookup data.

This is in case your lookup data is too large to fit in memory: you look up one half (for example) and then the other half.

Re: Proc SQL Performance

Patrick — Tue, 26 Mar 2019 22:45:44 GMT

@shl007

If the lookup table fits into memory then the hash approach sounds promising to me.

If you can't get things work then eventually provide some representative sample data (created via SAS data step code) for both your master and lookup table so we can start answering "in code".

The closer the sample data reflects your real data (i.e. variable names and type) the closer the code posted will be to what you need in the end.

Re: Proc SQL Performance

mkeintz — Wed, 27 Mar 2019 06:17:27 GMT

If your LOOKUP dataset never has duplicates in its key (field1 renamed as _field1 below), then this may be quite simple. Even if there are duplicates, there wouldn't be much change in the code:

data test (drop=_:);
  if _n_=1 then do;
    if 0 then set update (rename=(field1=_field1 field2=_field2));
    declare hash upd (dataset:'update (rename=(field1=_field1 field2=_field2))',hashexp:12);
      upd.definekey('_field1'); 
      upd.definedata(all:'Y');
      upd.definedone();
  end;
  set large;
  if upd.find(key:field2)=0 and field1<_field2;
run;

I use "hashexp:12" to tell sas to use up to 4096 (=2**12) "buckets" for hash object UPD. The default is hashexp:8. The maximum is hashexp:20.

I rename the variables field1 and field2 in data set update, because otherwise a successful find method would overwrite the values for field1 and field2 in the master dataset, which your sql code would not do.

Re: Proc SQL Performance

s_lassen — Wed, 27 Mar 2019 10:55:26 GMT

Here is the SQL - "master_bkp" has the 126 million rows. Using proc datasets in a prior step, I had created a composite index on field1 and field2.

proc sql;

create table test as

select *

from master_bkp a, lookup b

where b.field1 = a.field2 and a.field1 < b.field2

;

quit;

Rather than using a composite index and SQL, I would go with a simple index on FIELD2 in master_bkp, and use a datastep, something like:

proc sql;
  create index field2 on master_bkp(field2);
quit;


data want;
  set lookup;
  lookup_field2=field2; /* save the value */
  do field2=.,field1; /* in case there are duplicates on lookup, refresh with a non-existing key */
    do until(0); /* do forever, or until LEAVE */
      set master_bkp(rename=(field1=master_field1)) key=field2;
      if _iorc_ then do; /* last row read, no more found */
        _error_=0; /* when _iorc_ is set, _error_ is also set, but we do not want an error message */      
        leave;  
        end;
      if field1<lookup_field2 then
        output;
      end;
    end;
run;

I am not sure SAS is that good at using composite indexes for non-identical queries (like your comparison using "<"), but this is probably going to work (not tested, though, no test data). You can try it out with a single obs from lookup first, to check the syntax, and see how it performs.

Re: Proc SQL Performance

LinusH — Wed, 27 Mar 2019 17:26:32 GMT

Adding
Options msglevel=i;
will let th know if the indexed is being used.
Also adding
PROC SQL _method;
will let you know what query strategy is being used.