SPDE update benchmarks

[EyalGonen]

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.

[LinusH]

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.

[EyalGonen]

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.

[LinusH]

My best advice is to set up your own benchmark test, using production like data and environment, and some different relveant update scenarios.
And again, consider compare with corresponding Base/DB options as well.