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 by using the primary key or index, the result set is automatically in the order specified by the index. 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 100000, the server creates an array large enough to sort 100000 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. External sorter first tries to use the database buffer pool (IndexFile.CacheSize) and if the sorting set is too large to fit into the configured limits, it is moved to temporary files on disk.
The temporary files (sxs...) that are used by the external sorter are created in a directory or directories that are specified with the Sorter.TmpDir_nparameter. The files are deleted automatically after sorting has finished.
For example, the following set of parameter settings might be appropriate for sorting frequent sorts of up to 2GB:
Parameter
Comment
SQL.SortArraySize=100000
Sorts under 100000 rows are sorted by the internal sorter.
Sorter.SorterEnabled=Yes
External sorter is used.
Sorter.TmpDir_1 = c:\tmp Sorter.TmpDir_2 = d:\tmp
You can use multiple disks for sorting to spread the load.
Sorter.MaxCacheUsePercent=50
Use a maximum of 50% of the value set by the IndexFile.CacheSize parameter.
MaxMemPerSort=2048M
Sorts that exceed this size will use disk.
IndexFile.CacheSize=32G
When using Sorter.MaxCacheUsePercent=50, a maximum of 16GB of the buffer pool can be used by the sorter, which reduces the amount of cache for other usage.
Querying and controlling optimizers sorter decisions
You can query the optimizer decisions for sorting by 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.
Additionally, the performance counters with the prefix Sorter provide information about the external sorter tasks. To view the Sorter performance counters, run the following command:
ADMIN COMMAND 'pmon sorter'
For example, high values of the ‘Sorter start sort’counter indicate the frequent use of the external sorter. It might be possible to improve performance by increasing the SQL.SortArraySize parameter with the cost of an increased memory footprint, see SQL section.