Is Select faster than Selectlist?

This Q&A forum allows users to post and respond to "How Do I Do ....." questions. Please do not use to report (suspected) errors - you must use your regional help desk for this. The information contained in this forum has not been validated by LANSA and, as such, LANSA cannot guarantee the accuracy of the information.
Post Reply
Taku Izumi
Posts: 24
Joined: Thu Dec 15, 2016 2:03 pm

Is Select faster than Selectlist?

Post by Taku Izumi » Thu Mar 19, 2020 2:26 pm

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

LANSAfolks
Posts: 13
Joined: Thu Oct 10, 2019 9:32 am

Re: Is Select faster than Selectlist?

Post by LANSAfolks » Thu Mar 19, 2020 4:08 pm

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.

soa
Posts: 329
Joined: Mon Dec 07, 2015 3:15 pm

Re: Is Select faster than Selectlist?

Post by soa » Thu Mar 19, 2020 6:08 pm

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.

Post Reply