Optimizing suggestions needed for a SQL UPDATE statment. Two ~5 million record tables being used
optimization, query-optimization, sas, sql
Solution
All of the answers so far are firmly oriented in the SQL part of your question, but neglect the SAS part to some extent. I would strongly recommend trying a data step update/modify/merge instead of proc sql for this kind of update. It should be possible to sort both tables and apply similar logic from your SQL to ensure that the correct rows/columns are updated.
I've seen similar kinds of updates run in a matter of minutes on 20 million or more rows.
Also, check out http://runsubmit.com , a SAS specific stackoverflow style site, for more SAS specific answers.
Disclosure: I'm a SAS employee. I have nothing to do with runsubmit, which is independently run.
Problem
I'm looking for any suggestions to optimize the following PROC SQL statement from a SAS program. The two tables involved contain around 5 million records each and the runtime is about 46 hours. The statement is looking to update a "new" version of the "old" table. Noting a column if the "old" table, for a "PK_ID", was listed without a value for "3RD_ID" and "CODE", but in the "new" table, for a "PK_ID", it is now listed WITH a value for "3RD_ID" and "CODE". Thanks for any suggestions... (The code is really formatted below! For some reasons my spaces aren't showing for indents...) ``` PROC SQL _METHOD; UPDATE NEW_TABLE AS N SET NEW_2ND_ID=(SELECT 2ND_ID FROM OLD_TABLE AS O WHERE N.PK_ID=0.PK_ID AND N.2ND_ID<>O.2ND_ID AND O.3RD_ID IS NULL AND O.CODE IS NULL AND N.3RD_ID IS NOT NULL AND N.CODE IS NOT NULL AND N.2ND_ID IS NOT NULL) WHERE N.3RD_ID IS NOT NULL AND N.PK_ID IS NOT NULL AND N.CODE IS NOT NULL AND N.2ND_ID IS NOT NULL; QUIT; ```