Re: Sql statement vs Sql View performance

On 31 Jul 2013 12:51, Vernon Hamberg wrote:

Good points - especially about testing - it's important to get back
to the same basic conditions. In Navigator's SQL processor, do
multiple executions of the statement - the first one is almost always
slower, since it has to load stuff into memory.

Then reconnect. The 2nd job may still run faster, because the table
may partially be in memory from the first job. The solution there is
to use SETOBJACC over the table[s] in order to clear from memory, as
here -

SETOBJACC OBJ(YOURLIB/YOURFILE) OBJTYPE(*FILE) POOL(*PURGE)

You could probably do this in the Navigator interface by using the
SQL CALL function on QCMDEXC.

IIRC the Run SQL Scripts interface allows prefixing a CL command with the un-delimited "CL:" to avoid having explicitly to use the SQL CALL to pass a CL request as a parameter to a CL request processor.

Then it's all apples to apples.

Still possibly apples to oranges, because even with that request to deal with memory the testing may be flawed. Formulating a valid test could be more complex and\or difficult than just purging data from memory. There is possibly a [even multiple] Materialized Temporary Index (MTI) from a prior run [which would appear as a noticeable cost in a prior run], as well as any permanent indexes [keyed access paths] that may remain in memory from prior activity; i.e. beyond just data from the TABLE having a noticeable impact. Even having explicitly ensured MBRDATA(*BOTH), I believe the AccPth purged with that request will only be the access path(s) defined for the named database file; i.e. the purge will not process\include the keyed access paths of all keyed logical files based-on that member or any MTI.?

A better option may be to discard the first run(s), and concentrate only on the latter run(s).