Re: Sql statement vs Sql View performance

As I said, "do multiple executions of the statement - first one is almost always slower"

The technique I present was basically what we used when testing the new query engine back in 2001. We also sometimes used CLRPOOL, as I recall, which I do NOT recommend on a production machine!!

Of course, it is almost impossible to get a completely similar set of conditions - on any machine, so much is going on, especially one that is not dedicated to the test. We had the luxury of using a partition all to ourselves, to minimize variability.

Vern

On 7/31/2013 6:50 PM, CRPence wrote:

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).