In Pyramid Analytics, managing data queries efficiently is critical for system performance and memory usage.

Understanding Query Limit Settings

The below settings are located in the Admin Console under Data > Query Limit

• SQL Result Limit: Defines the maximum number of rows that can be processed during query execution before the final result is returned to the client.
• Result Limit: Specifies the maximum number of rows that can appear in the client (e.g., a report)

Typically, the SQL Result Limit is set higher than the Result Limit because Pyramid’s engine can process more data than a browser can display.

Impact of Adjusting Limits

While these limits can be modified in the Admin Console, increasing them can lead to higher memory usage by Pyramid’s Run-Time Engine (RTE). If performance issues arise, consider allocating additional memory resources to the machine running the RTE to handle the increased load.

How Are Query Limits Calculated?

• SQL Result Limit
  
  The SQL "Select Top" or "Limit" in the query is determined by the lower of the following two factors:

  • The admin-defined SQL Result Limit.

  • A calculated limit, based on the number of columns in the query:
    SQL Result Limit = (Admin "SQL Result Limit" × 4) / Number of Columns

For example, if the admin limit is 25 million rows and the query has 9 columns, the SQL Result Limit will be:

SQL Result Limit = 25M × 4 / 9 ≈ 11,111,111 rows

The 11,111,111 value will appear in the "Select Top" / "Limit" part of the query.

 

• Result Limit
  
  The number of rows displayed in the report is determined by the lower of the following two factors:
  • The admin-defined Result Limit.
  • The calculated limit, based on the number of columns in the query:
    Result Limit = (Admin "Result Limit" × 4) / Number of Columns

For example, if the admin limit is 500,000 rows and the query has 9 columns, the Result Limit will be:

Result Limit = 500,000 × 4 / 9 ≈ 222,222 rows

The 222,222 value represents the maximum number of rows that will be displayed in the report.

 

Note: If a query returns more rows than the limit specified in one of the above query limit settings, a warning icon will appear in the bottom right-hand corner of the screen with the message: "The SQL cell limit of (#max rows) was reached while processing this result".

Note: For how to determine the Number of Columns in a Query see below.

Discovery Row Limit Settings

The Row Limit settings allow users to override both the Result Limit and SQL Result Limit. These settings can be accessed as follows:

• Discovery > Query Ribbon > Query Settings
• The User Defaults settings, which are located on the Home page, Discovery, and the Admin Console

When the 'Amount of Shown Rows' value is set, it will override the default Result Limit setting in the Admin Console.

Enabling the 'Apply this limit to queries against the data source' option will enforce the 'Amount of Shown Rows' value on both the Result Limit and the SQL Result Limit.

---

How to Determine the Number of Columns in a Query

 

To understand how query limit settings impact your queries, it's crucial to determine the number of columns involved. However, the columns shown in the grid or under the (i) Information > Statistics section may not represent all the columns queried by the engine. For example, attributes sorted by another column (e.g., month name sorted by month number) may result in additional columns being queried.

To accurately determine the Number of Columns in a Query:

1. Inspect the SQL Query: Find the SELECT clause, which specifies the columns to be included in the result set.
2. Count the Columns: Each item in the SELECT clause (separated by commas) represents a column. These can include:
   • Aggregate functions like SUM, MIN, or MAX.
   • Individual column names.
   • Expressions or calculations.