When the solidDB® SQL Optimizer chooses an execution plan, it considers the performance impact of sorting data. Sorting occurs if the result set is not returned automatically in the correct order. If sorting is needed, the Optimizer chooses whether to use the internal sorter or the external sorter. The internal sorter is used with small result sets (hundreds of rows) while the external sorter is used with large result sets (thousands of rows).
Sorting occurs when no index satisfies the requested ordering of fetched rows. If the table data is accessed using the primary key or index, the result set is automatically in the order specified by the index in use. Hence, you can improve server performance by designing primary keys and indexes to support the ordering requirements of frequently used, performance-critical queries.
Note Some queries require sorting implicitly. For example, if the optimizer chooses a JOIN operation to use the MERGE JOIN algorithm, the result sets to be joined require sorting before the join can occur.
Internal sorter
The internal sorter performs all sorting in the main memory. The amount of memory used for sorting is defined with the SQL.SortArraySize parameter. The SQL.SortArraySize parameter defines the size of the array (in rows) that is used for ordering the result set of a query. For example, if you specify a value of 1000, the server creates an array large enough to sort 1000 rows of data. If the amount of data to be sorted does not fit into the allocated memory, increase the value of the parameter SQL.SortArraySize.
External sorter
If the sorting task does not fit in the main memory (typically with large result sets), the Optimizer uses the external sorter, which stores intermediate information to disk. The external sorter is enabled by default (Sorter.SorterEnabled=yes).
The temporary files used by the external sort are created in a directory or directories specified with the Sorter.TmpDir_nparameter. The files are deleted automatically after sorting has finished.
To achieve better performance, the external sort files can be stored to a local drive using local disk names. Using multiple local disks avoids network I/O and balances the I/O load to multiple disks.
An external sort requires space both on disk and in memory, not just space on the disk. You can configure the maximum amount of memory used for sorting with the Sorter.MaxMemPerSort and Sorter.MaxCacheUsePercent parameters.
Querying and controlling Optimizers sorter decisions
You can query the Optimizer decisions for sorting using the EXPLAIN PLAN FOR statement.
If the Optimizer is not choosing the optimal query execution plan, you can override the Optimizer decision by using optimizer hints. For more information, see “Hints” in the solidDB® Programmer Guide.
Additionally, the performance counters with the prefix Sorter provide information about the external sorter tasks. To view the Sorter performance counters, issue the following command:
ADMIN COMMAND 'pmon sorter'
For example, high values of the Sorter start sort counter indicate excessive use of the external sorter. If you have enough memory available, you can increase the value of the SQL.SortArraySize parameter to avoid the use of the external sorter.