declare global temp table and package cache conciderations

[Lennart]

Any thoughts on the following scenario anyone?

During a performance test I discovered that the application asked one
specific query extremely often. It turned out that this particular
query where asked 25/50/100 or 200 times from a "htmlpage", dependent
of user preferences. I figured that using a global temp table, looping
and inserting, then join would do the trick.

However, it turned out that this killed performance totally. Why,
apparently using a global temp table in the query forces a preparation
of the query each time the page is shown to a user:

.. Number of executions = 57190
Number of compilations = 55
Worst preparation time (ms) = 43265
Best preparation time (ms) = 1500
[...]
Total execution time (sec.ms) = 119286.111630

The current solution is to build a lateral clause in the code and
stuff things there as in:

[...]
sb.append("from LATERAL(VALUES");
for (int i = 0; i < codes.size(); i++) {
sb.append(" (cast(? as int), cast(? as int), cast(? as varchar(64)),
cast(? as varchar(64))) ");
if ((i + 1 ) < codes.size()) {
sb.append(", ");
}
}
sb.append(") as oc(year, period, code, educationeventid)");
[...]

and then another loop setting parameters.

The performance is ok, but I don't like the looks of the code ;-). Any
thoughts anyone? Would using a global temp table from a stored
procedure help? Any other way to avoid the preparation cost of the
query?

uname -a
Linux db2-01 2.6.9-42.ELsmp #1 SMP Wed Jul 12 23:27:17 EDT 2006 i686
i686 i386 GNU/Linux

db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release
"SQL08028"
with level identifier "03090106".
Informational tokens are "DB2 v8.1.2.136", "s070720", "MI00189", and
FixPak
"15".
Product is installed at "/opt/IBM/db2/V8.1".
Thanx
/Lennart

[Serge Rielau]

Lennart wrote:

Any thoughts on the following scenario anyone?

During a performance test I discovered that the application asked one
specific query extremely often. It turned out that this particular
query where asked 25/50/100 or 200 times from a "htmlpage", dependent
of user preferences. I figured that using a global temp table, looping
and inserting, then join would do the trick.

However, it turned out that this killed performance totally. Why,
apparently using a global temp table in the query forces a preparation
of the query each time the page is shown to a user:

. Number of executions = 57190
Number of compilations = 55
Worst preparation time (ms) = 43265
Best preparation time (ms) = 1500
[...]
Total execution time (sec.ms) = 119286.111630

The current solution is to build a lateral clause in the code and
stuff things there as in:

[...]
sb.append("from LATERAL(VALUES");
for (int i = 0; i < codes.size(); i++) {
sb.append(" (cast(? as int), cast(? as int), cast(? as varchar(64)),
cast(? as varchar(64))) ");
if ((i + 1 ) < codes.size()) {
sb.append(", ");
}
}
sb.append(") as oc(year, period, code, educationeventid)");
[...]

and then another loop setting parameters.

The performance is ok, but I don't like the looks of the code ;-). Any
thoughts anyone? Would using a global temp table from a stored
procedure help? Any other way to avoid the preparation cost of the
query?

uname -a
Linux db2-01 2.6.9-42.ELsmp #1 SMP Wed Jul 12 23:27:17 EDT 2006 i686
i686 i386 GNU/Linux

db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release
"SQL08028"
with level identifier "03090106".
Informational tokens are "DB2 v8.1.2.136", "s070720", "MI00189", and
FixPak
"15".
Product is installed at "/opt/IBM/db2/V8.1".

I'm not entirely sure on what you are doing but I can provide general
background on DGTT, do's and don'ts

Any statement that depends on a DGTT needs to get recompiled next time
around if:
* The DGTT got dropped/replaced between the two invocation
* The connection is new.

Most often I have seen performance issues with DGTT when they get
defined within a stored procedure and that stored proc is called often.
Every procedure invocation causes a recompile of every statement which
has teh DGTT in it.
A better approach is to define the DGTT ONCE and then simply truncate it
(DELETE FROM SESSION.TEMP;) within the procedure instead of replacing.
The same concept hold outside of procs of course. Define once. Use many
times.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Ian]

Serge Rielau wrote:

A better approach is to define the DGTT ONCE and then simply truncate it
(DELETE FROM SESSION.TEMP;) within the procedure instead of replacing.
The same concept hold outside of procs of course. Define once. Use many
times.

Serge,

I take it that the issue where issuing DELETE FROM SESSION.TEMP does not
release the pages allocated to the table has been resolved?
Ian Bjorhovde

[Serge Rielau]

Ian wrote:

Serge Rielau wrote:

>A better approach is to define the DGTT ONCE and then simply truncate
it (DELETE FROM SESSION.TEMP;) within the procedure instead of replacing.
The same concept hold outside of procs of course. Define once. Use
many times.

I take it that the issue where issuing DELETE FROM SESSION.TEMP does not
release the pages allocated to the table has been resolved?

Yes.. a long time ago.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab