Performance considerations when sorting data

While there is not always an index available that allows for sorting on the desired field, developers of Data Service queries should examine all the available indexes for a file before using the SORTDASC and SORTDESC parameters. When the using the SORTASC or SORTDESC parameter, only the returned records are sorted.

This table shows relevant information about the file containing the desired data and the query that is constructed.

Parameter	Setting
File name	EMPLOYEE
Primary index	EMPSET1 Contains these fields: Company Employee
Additional index	EMPSET2 Contains these fields: Company Last Name First Name Middle Init Employee
Field type and size	Company	Numeric, 4
	Employee	Numeric, 9
	Last Name	AlphaLower, 30
	First Name	AlphaLower, 15
	Middle Init	Alpha, 1
Desired sort order	Ascending, on the last name of the employee.
Total records in the file	1000
MAX parameter value	25 (the default)

Based on the primary index (EMPSET1), the records in the file are stored sorted by the numerical fields Company and Employee. This query returns the first 25 employees in the file. After these records are returned, they are then sorted by the value of the Last Name field.

PROD=apps810&FILE=employee&FIELD=employee;first-name;last-name
&SORTASC=last-name

For example, supposing the last name of the employee in the 26th record is Ames. Even if the last name of the employee in the 25th record is Doe, the 26th record is not returned.

If the user uses the correct index they can achieve global sorting, which is (sorting over all the records in a table. In this example, index EMPSET6 sorts the employees in each company according to their last name, rather than by their employee number.

PROD=apps810&FILE=employee&INDEX=empset6&FIELD=employee;
first-name;last-name

Using this index improves performance, since the sorting is completed by the database before the Data Service query, and allows for global sorting of the file before the data is returned.