You are here:

'sql_top': Limit the Number of Rows Returned by the Query

'sql_top' Setting Overview

sql_top defines the number of rows the cursor returns / keeps using the SELECT TOP SQL clause.

It is useful on large tables with thousands of records, as returning a large number of records can impact performance.

Note: If sql_top is not set, the entire table data will be loaded at the program runtime.

Enabling the Setting

Note: Before proceeding, get acquainted with the Settings Levels section of LyciaLowCode Settings page (the concept of levels is essential for configuring multiple interactions and the multitable support).

The definitions like LET <setting_variable>.<setting_name> = <value> refer to the InteractForm_Settings level, while LET <setting_variable>.views["<db_table_name>"].<setting_name> = <value> refers to the View level.

This setting can be defined for:

InteractForm_Settings level: No

View level: Yes

Setting format:

<InteractForm_Settings>.views["<db_table_name>"].sql_top INT

Example:

LET l_rec_settings.views["test05"].sql_top = 5

Tip: If you want to limit the number of shown / retrieved records but want to ensure that the latest created / inserted n rows are shown, use a descending sorting order with sql_order_by setting before using the sql_top setting.

Example:

LET l_rec_settings.views["test05"].sql_order_by = "date_created DESC"

LET l_rec_settings.views["test05"].sql_top = 10

Note: Alternatively, you can specify this setting in JSON array in .fm2 form file by the following example: Defining Settings in .fm2 Form File.