Content Platform Engine queries can be divided into continuable and non-continuable queries. (The query types are also known as paged and non-paged queries.) In a continuable query, rows that satisfy the search conditions are returned one page at a time.
A continuable query holds database locks for a short amount of time relative to a non-continuable query. The best concurrency is achieved with many active user searches. A non-continuable query returns all applicable rows and holds database row locks until query completion, which can cause query timeouts or database contention.
In a continuable query, a request for a new page is issued as a new search. In the database SQL for the new search, Content Platform Engine automatically appends an ORDER BY object_id. The object_id is the database column that corresponds to the Id property of the class for the object. This clause is required to continue the search at the next value. Subsequent page queries include an "object_id > 'last Id' " clause. The presence of this order by column affects the type of index that might be needed.
For a continuable query, the database SQL might find the first page of rows that satisfy the search conditions quickly. This quick return is possible if the query avoids scanning a table or index to find the rows. But the entire result set, which the client obtains by requesting every page of rows, might take a long time for the following reason: the rows must be retrieved and transferred from the database server over the network to the Content Platform Engine server.
The addition of the TOP N keyword to a continuable query has little effect on query performance. A continuable query already limits the number of rows that are returned, albeit a page at a time. Content Platform Engine adds to the database SQL some database vendor dependent "TOP N" keyword, such as the following ones: FETCH FIRST N for DB2 or ROWNUM < N for Oracle. These additions enhance the ability of the query to return quickly (if there are selective and indexed properties available). Without these additions, a table scan might be started.