topic Re: SPDE update benchmarks in SAS Programming

SPDE update benchmarks

EyalGonen — Wed, 08 Dec 2021 13:25:28 GMT

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.

Re: SPDE update benchmarks

LinusH — Wed, 08 Dec 2021 14:36:36 GMT

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.

Re: SPDE update benchmarks

EyalGonen — Wed, 08 Dec 2021 15:50:53 GMT

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.

Re: SPDE update benchmarks

LinusH — Wed, 08 Dec 2021 16:35:06 GMT

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.