Setting the page size for prefetching query results

When you first begin to iterate a query result, the query builder APIs submit the query to the database. Gosu does not typically submit the query each time you access the next result in the result object. Instead, Gosu automatically loads several results from the database result set as a batch into a cache in the application server for quick access. Common actions like iterating across the query have higher performance by using this cache.

In SQL, this caching is known as prefetching results. The number of items that the database prefetches and gives to an application is known as the prefetch page size.

Using only the first page of the results is equivalent to the SQL TOP or LIMIT functionality.

You can customize the number of results that the query builder APIs prefetch in order to tune overall query performance from the point-of-view the application server. To set the page size, call the setPageSize method on the query result object.

For example:

uses gw.api.database.Query

var query = Query.make(User)

// -- prefetch 10 entity instances at one time --
query.select().setPageSize(10)

Other notes:

  • If you plan to modify the entities, you must use a transaction.
  • In production code, you must not retrieve too many items and keep references to them. Memory errors and performance problems can occur. Design your code to limit the result set that your code returns.
Important: Always test database performance under realistic production conditions before and after changing any performance tuning settings.

See also