Limit the Number of Rows in a Database Query

You can control runaway queries for an application role assigned to a physical database by limiting queries to a specific number of rows.

Any query limits you set should exceed the Presentation Server settings for Maximum Number of Rows Processed when Rendering a Table View and Maximum Number of Rows to Download by at least 500 to avoid error messages. When you specify a max row query limit, then those users assigned to the application role may receive Max Row Limit Exceeded messages.

You can override the row limit that you set for an application role. See Override an Application Role's Query Limits.

The options for row limit are:

  • Enable - Limits the number of rows to the value specified. If the number of rows exceeds the Max Rows value, the query is terminated.

  • Disable - Disables any limits set in the Max Rows field.

  • Warn - Logs queries that exceed the set limit in the Query log. This option doesn't enforce limits.

  • Inherit - Inherits limits from the parent application role. If there is no row limit to inherit, no limit is enforced.

  1. On your home page, click Navigator Navigator icon and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Click Physical Layer Physical layer tab contains objects representing physical data.
  4. In the Physical Layer pane, locate and double-click the database that you want to assign query limits to.
  5. Click the Query Limits tab.
  6. Locate the role name that you want to limit, double-click its Max rows field, and enter the maximum number of rows that members of the application role can retrieve from the source database object.
  7. Double-click the Row Limit field and select a row limit.
  8. Click Save.