topic how make update faster? in SAS Procedures

how make update faster?

deleted_user — Thu, 15 Oct 2009 19:13:21 GMT

I have a SPDS table and I want to set all values in a particular column to NULL or ' ' (empty string).

It is taking very long to process, the table is ~30million records.
Here is my code

proc sql;
update data.account set ssn=' ';
quit;

Re: how make update faster?

LinusH — Mon, 19 Oct 2009 08:12:35 GMT

I don't know for sure, but my gut feeling says that recreating the table using data set would be faster.
The downside is that you have to recreate any existing indexes.
Even a bit faster would be to do a SQL pass-thru to do the job (create table...).

On the other hand, your example looks like a one-time fix, so it doesn't really needs to be optimized...?

/Linus

Re: how make update faster?

Flip — Mon, 19 Oct 2009 11:49:49 GMT

I have found in the past that updates like this are quite often faster if you drop the index first, then reindex it after the update.