Determining the Sort Cost Factor

The SET SORT COST FACTOR statement can be used to specify a value which the SQL query optimizer will use to estimate the additional performance cost associated with an ORDER BY clause. The specified value must be a fractional decimal that is greater than 0.0 but less than 1.0. It specifies the cost per row incurred by the sort/merge process required to sort a SELECT statement result set based on the specified ORDER BY clause. Note that the cost of doing a sort only matters when the optimizer has to choose between an execution plan that will naturally return the result rows in the specified order (due to the availability of a key) and another which requires that a sort be performed.

You can compute a sort cost factor using the example nsfawards database and the rdm-sql utility as follows. Of course, this example assumes that you have created and imported the nsfawards database.

Consider the following rdm-sql script contained in a file named sortcost.sql:

open database nsfawards;
.m Turn on advance command mode
.x
.e
.m Total number of awards:
select count(*) from award;
.m
.m Time unsorted query thru award table
.m But output results to award.out file
.J award.out
.T
select sponsor_nm, start_date, title from award;
.T
.J
.m
.m Time a sorted query thru award table
.J award.out
.T
select sponsor_nm, start_date, title from award order by 1, 2;
.T
.J
.q

The command-line execution of this script is shown below.

>rdm-sql sortcost.sql
Raima Database Manager SQL command processor Utility
Raima Database Manager 14.0.0 Build 1000 [8-1-2016] http://www.raima.com
Copyright (c) 2016 Raima Inc., All rights reserved.

Turn on advance command mode
Total number of awards:

            count(*)
--------------------
              132364
*** 1 row(s) returned

Time unsorted query thru award table
But output results to award.out file
*** saving query output in file: award.out
*** 1292 row(s) returned
rdm-sql sortcost.sql(12): elapsed time:  12.222 secs
*** save query output mode is off

Time a sorted query thru award table
*** saving query output in file: award.out
*** 1292 row(s) returned
rdm-sql sortcost.sql(19): elapsed time:  21.992 secs
*** save query output mode is off

To compute the sort cost factor, subtract the unsorted execution time from the sorted time and divide the result by the number of rows:(21.992 – 12.222)/132365 = 0.00007381106.

The following SET statement can be used to assign a value for the sort cost factor.

set sort cost factor to 0.000074;

This chapter has presented a detailed description of the query optimization process used in RDM SQL. If you have been able to carefully read through this chapter with understanding, you are now well equipped to create queries that perform well and to analyze poorly performing ones.