IBM 15 Manual Do Utilizador
233
Performance Considerations for Streams and Nodes
Note: The following databases support temporary tables for the purpose of caching: DB2,
Netezza, Oracle, SQL Server, and Teradata. Other databases will use a normal table for database
caching. The SQL code can be customized for specific databases - contact Support for assistance.
Netezza, Oracle, SQL Server, and Teradata. Other databases will use a normal table for database
caching. The SQL code can be customized for specific databases - contact Support for assistance.
Performance: Process Nodes
Sort.
The Sort node must read the entire input data set before it can be sorted. The data is stored in
memory up to some limit, and the excess is spilled to disk. The sorting algorithm is a combination
algorithm: data is read into memory up to the limit and sorted using a fast hybrid quick-sort
algorithm. If all the data fits in memory, then the sort is complete. Otherwise, a merge-sort
algorithm is applied. The sorted data is written to file and the next chunk of data is read into
memory, sorted, and written to disk. This is repeated until all the data has been read; then the sorted
chunks are merged. Merging may require repeated passes over the data stored on disk. At peak
usage, the Sort node will have two complete copies of the data set on disk: sorted and unsorted.
algorithm: data is read into memory up to the limit and sorted using a fast hybrid quick-sort
algorithm. If all the data fits in memory, then the sort is complete. Otherwise, a merge-sort
algorithm is applied. The sorted data is written to file and the next chunk of data is read into
memory, sorted, and written to disk. This is repeated until all the data has been read; then the sorted
chunks are merged. Merging may require repeated passes over the data stored on disk. At peak
usage, the Sort node will have two complete copies of the data set on disk: sorted and unsorted.
The overall running time of the algorithm is on the order of
N*log(N), where N is the number
of records. Sorting in memory is faster than merging from disk, so the actual running time can
be reduced by allocating more memory to the sort. The algorithm allocates to itself a fraction of
physical RAM controlled by the IBM® SPSS® Modeler Server configuration option Memory
usage multiplier. To increase the memory used for sorting, provide more physical RAM or increase
this value. Note that when the proportion of memory used exceeds the working set of the process
so that part of the memory is paged to disk, performance degrades because the memory-access
pattern of the in-memory sort algorithm is random and can cause excessive paging. The sort
algorithm is used by several nodes other than the Sort node, but the same performance rules apply.
be reduced by allocating more memory to the sort. The algorithm allocates to itself a fraction of
physical RAM controlled by the IBM® SPSS® Modeler Server configuration option Memory
usage multiplier. To increase the memory used for sorting, provide more physical RAM or increase
this value. Note that when the proportion of memory used exceeds the working set of the process
so that part of the memory is paged to disk, performance degrades because the memory-access
pattern of the in-memory sort algorithm is random and can cause excessive paging. The sort
algorithm is used by several nodes other than the Sort node, but the same performance rules apply.
Binning.
The Binning node reads the entire input data set to compute the bin boundaries, before
it allocates records to bins. The data set is cached while the boundaries are computed; then it is
rescanned for allocation. When the binning method is fixed-width or mean+standard deviation,
the data set is cached directly to disk. These methods have a linear running time and require
enough disk space to store the entire data set. When the binning method is ranks or tiles, the data
set is sorted using the sort algorithm described earlier, and the sorted data set is used as the cache.
Sorting gives these methods a running time of
rescanned for allocation. When the binning method is fixed-width or mean+standard deviation,
the data set is cached directly to disk. These methods have a linear running time and require
enough disk space to store the entire data set. When the binning method is ranks or tiles, the data
set is sorted using the sort algorithm described earlier, and the sorted data set is used as the cache.
Sorting gives these methods a running time of
M*N*log(N), where M is the number of binned
fields and N is the number of records; it requires disk space equal to twice the data set size.
Generating a Derive node based on generated bins will improve performance in subsequent
passes. Derive operations are much faster than binning.
Merge by Key (Join).
The Merge node, when the merge method is keys (equivalent to a database
join), sorts each of its input data sets by the key fields. This part of the procedure has a running
time of
time of
M*N*log(N), where M is the number of inputs and N is the number of records in the largest
input; it requires sufficient disk space to store all of its input data sets plus a second copy of the
largest data set. The running time of the merge itself is proportional to the size of the output
data set, which depends on the frequency of matching keys. In the worst case, where the output
is the Cartesian product of the inputs, the running time may approach
largest data set. The running time of the merge itself is proportional to the size of the output
data set, which depends on the frequency of matching keys. In the worst case, where the output
is the Cartesian product of the inputs, the running time may approach
NM. This is rare—most
joins have many fewer matching keys. If one data set is relatively larger than the other(s), or if
the incoming data is already sorted by a key field, then you can improve the performance of
this node using the Optimization tab.
the incoming data is already sorted by a key field, then you can improve the performance of
this node using the Optimization tab.