Looking for benchmarks/recommendation/best practice on how to update a large SPDE table with some update dataset by some common key.
Approaches can be SQL Update, Data Step Modify, Data Step Update, or perhaps other methods.
I can't offer any benchmark data, but my take is that SPDE is designed for "bulk loading", appends/insert is usually relatively fast. SQL DELETE FROM was a performance killer (but that might have changed, this was a few years ago).
What is the purpose of the benchmark?
You want to try different update methods, and compare with what?
I would set up in the same/similar environment corresponding test case using Base SAS, and potential an external DB if that is appropriate.
External bench mark figures is problematic, because there are a lot of things that affects performance, like data model, size, kind of operations, concurrent operations, host, disks, network etc etc.
I am specifically interested to know which method is the fastest to update (update existing records) a large SPDE table with some smaller update table by some common key. I am aware of several options like PROC SQL, Data Step modify, Data Step Update, etc. but not sure which will give the best performance.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.