topic Re: performance question? in SAS Procedures

performance question?

sassharp — Wed, 25 Apr 2012 03:49:00 GMT

need to improve performance.

table1 = 700million obs

proc sort data=table1;

by id

run;

table2=100k obs

proc sort data=table2;

by id;

run;

data table1;

update table1 table2;

by id;

run;

q. how to improve performance?

Re: performance question?

Patrick — Wed, 25 Apr 2012 04:47:02 GMT

Assuming these are SAS tables and your 100k table fits into memory below code would be an option:

data m700;
retain var1 var2 'm700';
do keyvar=1 to 1000;
output;
end;
run;

data k100;
retain var1 var2 'k100';
do keyvar=2,5,10,100,999;
output;
end;
run;

data m700;
if _n_=1 then
do;
    dcl hash h1(dataset:'k100');
    h1.defineKey('keyvar');
    h1.defineData(all:'y');
    h1.defineDone();
end;
modify m700;
_rc=h1.find();
run;

Re: performance question?

Ksharp — Wed, 25 Apr 2012 05:37:30 GMT

Hash Table or proc format

Re: performance question?

LinusH — Wed, 25 Apr 2012 07:34:24 GMT

A more old school approach could be using MODIFY with the KEY= option (if feasible).

This eliminates the need to sort the master table, and the master table is not rewritten during update.

Re: performance question?

Ksharp — Wed, 25 Apr 2012 07:52:49 GMT

I don't think it is a fast solution. Modify is only suited for the small table .

And the most important thing is that it is dangerous when your sas session stop accidently, the whole dataset will be collapsed totally.

Ksharp

Re: performance question?

LinusH — Wed, 25 Apr 2012 08:01:33 GMT

It's usually the fastest way to update a table, as long the master table is large, and the transaction table is relative small.

The risk for abnormal interruption has to weighted with work to restore a back-up (file system, or SAS PROC COPY style).

Re: performance question?

Patrick — Wed, 25 Apr 2012 08:21:01 GMT

Linus' second point that when using MODIFY with KEY= avoids rewriting the whole master table is a very strong argument for this approach.

Considering the relation of observations in the master and the transaction data set (700m to 100k) I would assume that it's even worth to create an index over the key variables (if not already existing).

data m700;
retain var1 var2 'm700';
do keyvar=1 to 1000;
output;
end;
run;

data k100;
retain var1 var2 'k100';
do keyvar=2,5,10,100,999;
output;
end;
run;

proc sql;
create index keyvar on m700 (keyvar);
quit;

data m700;
set k100(rename=(var1=kvar1 var2=kvar2));
modify m700 key=keyvar;
if _iorc_ = 0 then
do;
    var1=kvar1;
    var2=kvar2;
    replace;
end;
run;

Re: performance question?

LinusH — Wed, 25 Apr 2012 08:27:18 GMT

Yes, an index is essential to speed this process up!

Over time, it might be necessary as a maintenance action, to recreate (and sort) the master table, so that the index processing can remain efficient.

Re: performance question?

Ksharp — Wed, 25 Apr 2012 08:27:30 GMT

Hi, LinusH

The way you proposed need to make a index firstly, that means you need to spend lots of time to make such an index, it is almost like re-order a large table , which will waste lots of time.

And this way will be very fast if the result of query is less than twenty percent of a table . but we can't sure it too.

Ksharp

Re: performance question?

Patrick — Wed, 25 Apr 2012 08:31:17 GMT

I believe that even if your SAS session aborts the data set is not necessarily corrupted. Actually it would have to be quite an extreme event like a sudden power failure to corrupt the table.

As much as I understand Modify transactional integrity ("unit of work") is maintained so (in my understanding) worst what may happen if a SAS session aborts is that only have of the updates are applied.

P.S: And if worst comes to worst then there is even a REPAIR statement as part of Proc Datasets which might fix damages. In all the time I've used Modify I had only once problems - and Repair fixed it.

Re: performance question?

Patrick — Wed, 25 Apr 2012 08:35:16 GMT

The OP asked for better performing code to update a 700M master table with 100k "transactions". My assumption would be that this is not a one-off task. Creating an index is a one-off task so it might be well worth it to create this index and avoid writting 700M records every single time you run an update.

Re: performance question?

LinusH — Wed, 25 Apr 2012 08:36:52 GMT

Nothing is certain...

But i this example the ratio of the master and transaction table is 0,014%.

And the index creation is a one time job, not a daily as sort/recreation of the master table would be.

And if they are lucky, the index might be helpful for query as well.

Re: performance question?

Ksharp — Wed, 25 Apr 2012 08:50:35 GMT

Hi. LinusH

20% I mean is not the ratio of master and tran .

I mean the number of updated observations in master table.

From the documentaion , if the result of query is less than 20% , using index is less efficient than consequent access method ( like SET ) .

Anyway , Anyhow , We can talk to each other right now, which lead me to wonder where are you (Patrick and LinusH) come from ?

I guess Australia ? Right ?

Nice to meet you and Patrick.

Ksharp

Re: performance question?

LinusH — Wed, 25 Apr 2012 09:04:46 GMT

I'm a swede

Re: performance question?

Patrick — Wed, 25 Apr 2012 09:38:47 GMT

Yes, I live in Australia since a couple of years.

Re: performance question?

sassharp — Wed, 25 Apr 2012 12:30:27 GMT

https://communities.sas.com/thread/34628

This above link is the scenario. Out of 100k records 10 k ids are common in 700M and 100K. These 10K records from 100 (Table2) should be updated in table1(700M) and we need to append the remaining 90K records from table2 should be appended to table1.

The result should contain 700M and 90K.

Thanks

Re: performance question?

Ksharp — Thu, 26 Apr 2012 02:14:36 GMT

I would like to use Hash Table + remove() .

Ksharp

Re: performance question?

Patrick — Thu, 26 Apr 2012 04:42:41 GMT

Again a strong reason IMHO to use Modify as Linus suggested as it allows you to combine insert/update in one go without re-writting the whole master table.

data m700;
retain var1 var2 'm700';
do keyvar=2 to 4,6 to 10;
output;
end;
run;

data k100;
retain var1 var2 'k100';
do keyvar=1,5,9;
output;
end;
run;

proc sql;
create index keyvar on m700 (keyvar);
quit;

data m700;
set k100(rename=(var1=kvar1 var2=kvar2));
modify m700 key=keyvar;
if _iorc_ = 0 then
do;
    /* updates */
    var1=kvar1;
    var2=kvar2;
    replace;
end;
else
if _iorc_>0 then
do;
    /* inserts */
    var1=kvar1;
    var2=kvar2;
    output;
end;
run;

Re: Stack database columns

Re: performance question?

Ksharp — Thu, 26 Apr 2012 04:54:35 GMT

Hello,Patrick.

I have a different opinion with you. As I said before, Using index would be faster than Direct Access Method (i.e. SET statement) only if the number of updated or query observations is less than 20% of total number of a table .

At sometime, you can see SAS will not use index at LOG when declaring OPTIONS MSLEVEL=I .

Why ? that is because using index is not better than SET statement. That means that is not always a good way to use index .

I prefer to Hash Table , My favorite .

Anyway, It is personal favor.

Regards.

Ksharp

Re: performance question?

Patrick — Thu, 26 Apr 2012 05:02:44 GMT

Considering that the bottleneck is normally I/O I assume that reading an index file is faster than reading the whole 700M records and even more so writing 100k records and updating the index is faster than writing 700M records.

I believe this 20% figure only applies for select (read) and not for update/insert.