Visual LANSA Forum

Hi,

I understand that Selectlist is faster than Select, but in the following case, Select is faster than Selectlist.

To read 1000 records from a physical file with 10,000 records using Server Module in IBM i:

Select reads 1000 records using With_key.
e.x.)
Select Fields(...) with_key(#Key)
Endselect

Selectlist reads 1000 records using Where from the working list to which all records of the file were added.
e.x.)
* #List has 10,000 records.
Selectlist #List Where(#Key = xxxxx)
Endselect

I understand that this Selectlist reads 10,000 records internally, but still believed that selectlist was faster than Select.
However, the fact is that Select is faster.

Is this due to the improved performance of IBM i Disk access?
Or is the Selectlist Where inefficient?

Can anyone explain this reason?

Regards,
Taku

Your SELECT is reading 1000 records using a KEY, whereas your SELECTLIST is reading 10X more records and evaluating a WHERE clause. In other words, the SELECTLIST has to check each and every record in the working list. That would explain the performance difference.

If you run the same SELECTLIST, without the WHERE clause, it should easily outperform the SELECT. Even though it would be parsing 10,000 records.

You don't mention whether this is RDML or RDMLX.

If it is RDML then the working list is an RPG multiple occurrence data structure which is basically a big dumb chunk of memory in which case I would expect it to very fast indeed (RPG had to be fast on ancient slow machines so in the 21st Century it's smoking!)

If its RDMLX then its a much more complex beast with no upper size limits and dynamic memory allocation and how fast its is a factor of how good the coding is and how much time was spent on optimization. There are things in RDMLX which are not as fast as RDML.

You have to be careful comparing database performance on IBM i because you don't know how much of your table is already in memory and after the first run its almost guaranteed that it all is (for a table with 10k records) so everything ends up happening in memory only.