A statement over a large table can return more rows than a person looks at. The grid shows at most 1,000,000 rows of a result, and warns when it cuts the result off. The limit is available from release 28.0. ## The Limit A select statement which is run in a query tab with "Execute" is read until it has given 1,000,000 rows. The product reads one row more to know whether there are more. When there are, the grid shows the first 1,000,000 rows and the product warns in three places: - The strip under the grid shows a plus behind the number of rows, such as "1.000.000+ Rows Affected" on a device which writes the thousands separator as a point. - The messages pane says "Only the first" and the number of rows, followed by "rows of the result are shown." - A local statement which uses the last result, such as `local export results`, gets a warning in the messages pane that it sees only the first rows of the result: "A local statement which uses the last result sees only its first" and the number of rows. The rows beyond the limit are not in the grid, so they are not in an export which follows the view either; see [[Invantive Query Tool Multi-Platform/Exporting Results|Exporting Results]]. A filter, a group or a summary works on the rows which are shown. With the limit set to 5, a select of 12 rows from the data dictionary gives the strip "5+ Rows Affected" and the message "Only the first 5 rows of the result are shown.": ```sql select r.value as id from range@datadictionary(12) r ``` ## Where the Limit Does Not Apply The limit is for the rows which a person looks at, so it is not applied where a file or a local statement depends on every row: - The statements of a script run with "Execute All" are read in full. - The formats which run the statement again, XML, JSON, NDJSON, JSON Data Set and the three SQL formats, write the complete result; see [[Invantive Query Tool Multi-Platform/Exporting Results|Exporting Results]]. ## Changing the Limit The environment variable `INVANTIVE_QUERY_TOOL_MAXIMUM_ROWS_SHOWN` sets the number of rows. It defaults to 1,000,000. A run in which the variable has the value `5` shows at most five rows. See [[Environment Variables]] for how an environment variable is set. ## Asking for Less The cheapest large answer is the one which is never fetched: - name the columns instead of `select *`, which saves the platform the work and the connection the traffic; - narrow with a `where` which the platform itself can apply, so the rows never travel; - add a `limit` while writing the statement, and take it out when the statement is right; - aggregate in the statement rather than in the grid; see [[Invantive Query Tool Multi-Platform/Grouping Results|Grouping Results]]. The panes "Progress" and "Connection Monitor" show how many rows have passed each step and which calls were made; see [[Invantive Query Tool Multi-Platform/Progress|Progress]] and [[Invantive Query Tool Multi-Platform/Connection Monitor|Connection Monitor]].