Best way to retrieve data from a large database file (Web)

[Joerg Hamacher]

Hi everybody,

a customer is struggling with reading files with thousands of entries in RDMLX.
We looked at the sample "LargeLists" but this one works without key fields and always reads the same entries so it is not a suitable example for reading keyed logical files (views).

We read the first 100 entries of a file with more than 10000 entries that is keyed by let's say the EmployeeID.
100th Employee ID that was read is 123.
Then the user can push a button to retrieve the next 100 entries following the entry with ID 123.

We tried with
Select Fields(#ListFields) From_File(LogicalFile1) With_Key(#EmployeeID) Options(*STARTKEY)
in our server module:

#LastEmployeeID = *Zero at the first call, later on it receives the value of the last List item.

Select Fields(#ListFields) From_File(LogicalFile1) With_Key(#LastEmployeeID) Options(*STARTKEY)
#STD_NUM += 1
If (#STD_NUM <= 100)
Add_Entry To_List(#ListFields)
Else
Leave
Endif
Endselect

But this does not work!

Is Options(*STARTKEY) not usable in web server modules?


Then we tried
Select Fields(#ListFields) From_File(LogicalFile1) With_Key(#EmployeeID) Where(#EmployeeID > #LastEmployeeID)

#STD_NUM += 1
If (#STD_NUM <= 100)
Add_Entry To_List(#ListFields)
Else
Leave
Endif
Endselect

and got the result that they wanted.

Now my question:
Does this SELECT..WHERE statement make any sense in reading large files? If I understand correctly all table entries have to be read until I get the entries I want to add into my list. So I fear getting performance problems when reaching higher IDs.

What is the fastest way to retrieve table entries form a very large file in a key order "step by step"?


Many thanks in advance and best regards,
Joerg

[caseywhite]

I would consider using SELECT_SQL with the USING parm. This technique works on small and large files. I try not to use the *STARTKEY method anymore. It requires using WITH_KEY and eventually gets ugly when users start asking for lots of search parms. Here is a nice way to do it that works well for me.

Here is SQL that can be used for paging. The first request is the very first time and gets me my first set of 3 records to show on the page. The reason I am asking for 4 records is so that I know whether to show the next button. If only 3 records come back then I don't show the next. Use whatever page size works best. In your case if it is 100 then change the 4 to 100 in the first SQL. The second SQL asks for ROW_NUM between 4 and 7 which will return the 4th through 7th record matching your ORDER BY. That is is how you handle the paging. You don't need to use keys or anything like the last record read or anything like that. This is clean and can be used generically for any of the tables you need to search.

SELECT DEPTMENT,DEPTDESC FROM
(SELECT ROWNUMBER() OVER(ORDER BY DEPTDESC)
AS ROW_NUM,DEPTMENT,DEPTDESC FROM DEPTAB
WHERE (DEPTDESC <> '')) AS RowConstrainedResult
WHERE ROW_NUM BETWEEN 1 AND 4
FETCH FIRST 4 ROWS ONLY

SELECT DEPTMENT,DEPTDESC FROM
(SELECT ROWNUMBER() OVER(ORDER BY DEPTDESC)
AS ROW_NUM,DEPTMENT,DEPTDESC FROM DEPTAB
WHERE (DEPTDESC <> '')) AS RowConstrainedResult
WHERE ROW_NUM BETWEEN 4 AND 7
FETCH FIRST 4 ROWS ONLY

The LANSA command would be:
SELECT_SQL FIELDS(#DEPTMENT #DEPTDESC) USING(#SQLSTATEMENT)
... your logic here.
ENDSELECT

The #SQLSTATEMENT would be set to the statement above.

You still need a logical file so that it performs well but you don't need to reference it. You also don't need to load it into LANSA. Keep in mind that the OAM will not run so if you have PJFs or virtual fields you would need to add that logic yourself.

In the above example i used DEPTDESC<>'' just to show how to have a WHERE clause. You don't need one or you can have one.