How to: |
The RECORDLIMIT phrase sets a maximum for the number of rows the adapter fetches from the answer set returned by the RDBMS. It does not restrict the RDBMS in its construction of the answer set. However, if the RDBMS does not have to sort the answer set, using this feature may result in a significant reduction in response time.
The READLIMIT phrase is synonymous with RECORDLIMIT except in the following cases:
The READLIMIT and RECORDLIMIT phrases are effective in reducing RDBMS-to-FOCUS communication traffic, the volume of formatted report data, and terminal and disk I/Os.
Search limit tests are helpful when:
In all three cases, a few rows are sufficient to verify results.
{WHERE|IF} {RECORDLIMIT|READLIMIT} {EQ|IS} n
where:
Is the number of records to be included in the TABLE request.
The FOCUS database administrator may place the READLIMIT or RECORDLIMIT test in a Master File to limit the fetching of rows for all users. If the Master File and the report request both contain such a test, the adapter uses the lower of the two values.
Note:
The following request turns on the STMTRACE trace component and issues a retrieval request with a READLIMIT phrase. The SQL request contains the FETCH FIRST 2 ROWS clause:
SET TRACEUSER=ON SET TRACEOFF=ALL SET TRACEON=STMTRACE//CLIENT TABLE FILE EMPINFO PRINT LAST_NAME FIRST_NAME CURRENT_SALARY BY EMP_ID IF READLIMIT EQ 2 END
The generated SQL request contains the FETCH FIRST 2 ROWS clause:
SELECT T1."EID",T1."LN",T1."FN",T1."CSAL" FROM USER1."EMPINFO" T1 ORDER BY T1."EID" FETCH FIRST 2 ROWS ONLY FOR FETCH ONLY;
If the test specifies READLIMIT EQ 1, the SQL request contains the clause FETCH FIRST 1 ROW ONLY:
SELECT T1."EID",T1."LN",T1."FN",T1."CSAL" FROM USER1.EMPINFO" T1 ORDER BY T1."EID" FETCH FIRST 1 ROW ONLY FOR FETCH ONLY;
Assume the EMPLOYEE Master File describes the Oracle table USER1.PAYROLL and consider the following request:
TABLE FILE EMPLOYEE PRINT EMP_ID WHERE READLIMIT EQ 5 END
The Adapter for Oracle generates the following SQL for this request:
SELECT T1.EMP_ID FROM "USER1"."PAYROLL" T1 WHERE ROWNUM <=5;
Because the screening condition is passed to the RDBMS, only the first five rows retrieved by Oracle are returned. This results in less communication between the RDBMS and FOCUS and less processing by FOCUS prior to displaying the report output.
Information Builders |