473,547 Members | 2,391 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

declare global temp table and package cache conciderations


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, educationeventi d)");
[...]

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
Feb 1 '08 #1
3 2437
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, educationeventi d)");
[...]

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
Feb 1 '08 #2
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
Feb 5 '08 #3
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
Feb 5 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
13685
by: Corrine | last post by:
Hi, I am creating a global temporary table that is session-specific. I insert a BLOB into this table, and then select the BLOB from this table into a ResultSet. The ResultSet sees this BLOB object, and I am able to get the binary input stream from this blob. However, when I invoke InputStream.read(byte) on this input stream, I get the...
6
3750
by: pb648174 | last post by:
I have a pivot table implementation, part of which is posted below. It returns no errors in query analyzer, but when profiler is run, it shows that "Error 208" is happening. I looked that up in BOL and it means that an object doesn't exist. This block of code below works fine on my local development machine, but not on our shared development...
10
10796
by: Ranga | last post by:
I was unable to run the statement "CREATE GLOBAL TEMPORARY TABLE" on unix version of DB2, it gave the follwing error db2 => create global temporary table temp ( OGI_SYS_NR char(8) ) DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:...
0
2580
by: gwaddell | last post by:
I have a stored procedure that is loading data into a global temp table. Here is all the code for that stored procedure. CREATE PROCEDURE AS SET NOCOUNT ON BEGIN
41
10640
by: Miguel Dias Moura | last post by:
Hello, I am working on an ASP.NET / VB page and I created a variable "query": Sub Page_Load(sender As Object, e As System.EventArgs) Dim query as String = String.Empty ... query = String.Format("SELECT * FROM dbo.documents WHERE ") & query End Sub
16
10298
by: pukivruki | last post by:
hi, I wish to create a temporary table who's name is dynamic based on the argument. ALTER PROCEDURE . @PID1 VARCHAR(50), @PID2 VARCHAR(50), @TICKET VARCHAR(20)
2
4818
by: tejaloracle | last post by:
how to create global temporary table within procedure? CODE : create or replace procedure temp (pno varchar2) AS p_a varchar2(50); p_b varchar2(20);
0
1815
by: cuddles | last post by:
hi all i need to declare a temporary table within a user-defined function in IBM DB2 ver 8. can someone give me the syntax for the temp table declaration. CREATE FUNCTION PEGASUS.readOrder( wsLoadNumberChar CHARACTER(07), wsDispatchYearFlg CHARACTER(01) ) RETURNS TABLE (ORD_ORDI ...
10
2455
by: Tammy | last post by:
Hello all, I am wondering what is the best way to declare a struct to be used in other c and c++ files. Such as for a C API that will be used by others. 1. Declaring the typedef and the struct in the header file and including this file in all source files that need it? For example: mystruct.h
0
7510
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7947
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7463
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6032
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5362
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5081
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3493
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3473
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1050
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.