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.