Product Documentation

c-treeACE V10.0 Release Notes

Previous Topic

Next Topic

c-treeACE SQL TOP Sort Optimization

A c-treeACE SQL query of the following form was found to run slow:

SELECT TOP N * FROM tbl WHERE tbl.a BETWEEN x AND y ORDER BY tbl.a, tbl.b;

When an index only exists on tbl.a, all of the records in the range are read and sorted, however, the TOP N value should be used to reduce this number, which can be a dramatic improvement when there are large numbers of records to be read. To optimize this query, once N records have been read, reading continues from tbl.a (already in order by index) only until tbl.a changes.

TOCIndex