SQL SELECT vs RPG READ Performance ?

[MFisher]

We're trying to troubleshoot a long running program that currently has a SQL SELECT on a logical file, with the WHERE clause comparing the first 3 fields in the key, and MAX for the 4th key field.

Is there any reason to think that an RPG SETLL & READE statement would be any faster than SQL SELECT ?

[andreas.prouza]

Usually the difference are milliseconds.
It depends which indices and statistics exists for the base table.
The SQL engine can do great stuff to get the data much faster dann SETLL & READE (native i/o) could do.
If you just do a simple READ of a table without any special filter, native i/o is probably faster, because SQL always has some overhead.

Nevertheless I would always prefer to use SQL.
I can do great performance analysis and optimization with the given tools from IBM.
If you use native i/o you can't do that so easy.
Btw IBM is constantly improving SQL.
So it's anyway better for future.

[MFisher]

In this case, it seems SQL is not using the Logical File as I expected. The job takes over an hour to run.

Changing the program to SETLL & READE improved it to only taking a few minutes.

I just added a SQL index with the same attributes as the Logical File, running the original SQL code, and it ran in 50 seconds !

CREATE INDEX ITHL86_I
on ITH
(TLOT , TPROD , TWHS , TTDTE desc)
WHERE TTYPE not in ('C' , 'Y ' , '# ')

[Scott Klement]

The SQL statement should never say "FROM logical-file-name". It should always say "FROM table/pf-name" or "FROM view". It is up to SQL to determine which index to use, and it will automatically pick the one it can use most efficiently (which may be a logical file under certain circumstances.)

It's different from RPG's native I/O. In SQL, you tell the system what you need, and it figures out the most efficient way to get it. In native I/O, you tell it precisely how to get it.

As to which is faster... that depends on how good of a programmer you are vs. the SQL folks at IBM. But they have resources that you don't... and coding things in SQL usually saves you time as a programmer, so in almost all cases, it's better to just let SQL handle it.

[B.Hauser]

1. You never ever should specify a DDS described logical file in a SELECT Statement ... the SQL Precompiler has to rewrite analyze the DDS description and rewrite the query based on the selected fields, join clauses and SELECT/OMIT clause and the physical files/tables used in the DDS. After having rewritten the query it will start with the optimization.
2. Each SQL Statement has to be optimized at runtime (syntax check, evaluating the existing access path in composition with the statistic manager and finally opening the ODP=Open Data Path). FULL Optimization is time consuming. So SQL tries to keep the ODPs open, so that for sub-sequent calls only the data has to be updated (PSEUDO Open). Using native I/O there is no optimization, i.e. a specified logical file in the F-Specs is taken.
3. SQL is fast as soon if it can block. If only a single record is returned, RPG may win.
4. Also when using aggregate functions in SQL has to do additional work. In your example an SELECT TTDATE FROM WHERE .... ORDER BY TTDATE Desc could be faster than your example.

In either way SQL Performance optimization and understanding the Query Optimizer is not as trivial.

[MFisher]

Thanks for the replies.

So if I want the Select/Omit values from an LF, I should SELECT FROM the PF and code the select/omit in the WHERE in the SELECT statement instead ?

[Scott Klement]

Yes, use the WHERE clause. (Or an SQL view.) Never use the select/omit in an LF, they are terrible for SQL performance.

[B.Hauser]

If you create a View with a WHERE condition and include EXACTLY the same WHERE Condition in an INDEX ... SQL might be able to use this index.
In either way (currently) SQL can only use SPARSE Indexes (with WHERE Conditions) if the syntax is 100% identical ... and in my experience if it is hard coded in a SELECT statement it is quite often not used. If coded in a view and the view is used in a SELECT statement the index is (mostly) used.

Nevertheless SPARSE Indexes are at first for native I/O, i.e. derived and sparse indexes can be specified in the F-Specs and used like any keyed logical file.