Threaded Reads

Performance Impact of Threaded Reads

Threaded reads only increase performance when the DBMS result set is large. Performance is optimal when the partitions are similar in size. Using threaded reads should reduce the elapsed time of your SAS step, but unusual cases can slow the SAS step. They generally increase the workload on your DBMS.

For example, threaded reads for DB2 under z/OS involve a tradeoff, generally reducing job elapsed time but increasing DB2 workload and CPU usage. See the auto partitioning documentation for DB2 under z/OS for details.

SAS automatically tries to autopartition table references for SAS in threaded applications. To determine whether autopartitioning is occurring and to assess its performance, complete these tasks:

Turn on SAS tracing to see whether SAS is autopartitioning and to view the SQL associated with each thread.
Know your DBMS algorithm for autopartitioning.
Turn threaded reads on and off, and compare the elapsed times.

Follow these guidelines to ensure optimal tuning of threaded reads.

Use it only when pulling large result sets into SAS from the DBMS.
Use DBSLICE= to partition if SAS autopartitioning does not occur.
Override autopartitioning with DBSLICE= if you can manually provide substantially better partitioning. The best partitioning equally distributes the result set across the threads.
See the DBMS-specific reference section in this document for information and tips for your DBMS.

Threaded reads are most effective on new, faster computer hardware running SAS, and with a powerful parallel edition of the DBMS. For example, if SAS runs on a fast uniprocessor or on a multiprocessor machine and your DBMS runs on a high-end SMP server, you can experience substantial performance gains. However, you can experience minimal gains or even performance degradation when running SAS on an old desktop model with a nonparallel DBMS edition running on old hardware.

Top of Page