473,323 Members | 1,550 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Performance questions DB2 UDB 8.1.9 Linux Redux

BHID id the (only) primary key, and thus index, of Animals, which is the
only real table involved.

Pedigree2 is the view based on Animals.

runstats on table is3.animals ON ALL COLUMNS AND DETAILED INDEXES was
run before the test.

The test was run multiple times to eliminate initial buffering bias.

The execution plan shows lots of relatively expensive nested loop JOINs,
which surprises me because all the JOINs are on the primary key of animals.

The following query takes 3-5 seconds which seems a bit excessive:

select * from is3.pedigree2
where bhid in
( 322380, 379701, 380901, 394336, 394342, 396039, 400764)

Furthermore, when extended to the desired use, it generates a warning
message for which I can find no explanation for the reason code:

select * from is3.pedigree2
where bhid in
(select bhid from is3.ANIMAL_SETS
where set_name = '000' and USERID = 'jhoughex')

DB2 SQL error: SQLCODE: 437, SQLSTATE: 01602, SQLERRMC: 1
Performance of this complex query may be sub-optimal. Reason code: "1".

Both queries depend on the same complex view. I have used much more
complex sql without generating such a message or having such poor
running times. Any suggestions greatly appreciated. The view is below
(longish):

CREATE VIEW IS3.Pedigree2 AS
SELECT animal.BHID AS BHID,
animal.Namex AS Name,
animal.assoc AS Assoc,
animal.prefix AS Prefix,
animal.regnum AS Regnum,

Sire.Namex AS Name_Sire,
Sire.assoc AS Assoc_Sire,
Sire.prefix AS Prefix_Sire,
Sire.regnum AS Regnum_Sire,
Dam.Namex AS Name_Dam,
Dam.assoc AS Assoc_Dam,
Dam.prefix AS Prefix_Dam,
Dam.regnum AS Regnum_Dam,

Sire_Sire.Namex AS Name_Sire_Sire,
Sire_Sire.assoc AS Assoc_Sire_Sire,
Sire_Sire.prefix AS Prefix_Sire_Sire,
Sire_Sire.regnum AS Regnum_Sire_Sire,
Sire_Dam.Namex AS Name_Sire_Dam,
Sire_Dam.assoc AS Assoc_Sire_Dam,
Sire_Dam.prefix AS Prefix_Sire_Dam,
Sire_Dam.regnum AS Regnum_Sire_Dam,
Dam_Sire.Namex AS Name_Dam_Sire,
Dam_Sire.assoc AS Assoc_Dam_Sire,
Dam_Sire.prefix AS Prefix_Dam_Sire,
Dam_Sire.regnum AS Regnum_Dam_Sire,
Dam_Dam.Namex AS Name_Dam_Dam,
Dam_Dam.assoc AS Assoc_Dam_Dam,
Dam_Dam.prefix AS Prefix_Dam_Dam,
Dam_Dam.regnum AS Regnum_Dam_Dam,

Sire_Sire_Sire.Namex AS Name_Sire_Sire_Sire,
Sire_Sire_Sire.assoc AS Assoc_Sire_Sire_Sire,
Sire_Sire_Sire.prefix AS Prefix_Sire_Sire_Sire,
Sire_Sire_Sire.regnum AS Regnum_Sire_Sire_Sire,
Sire_Sire_Dam.Namex AS Name_Sire_Sire_Dam,
Sire_Sire_Dam.assoc AS Assoc_Sire_Sire_Dam,
Sire_Sire_Dam.prefix AS Prefix_Sire_Sire_Dam,
Sire_Sire_Dam.regnum AS Regnum_Sire_Sire_Dam,
Sire_Dam_Sire.Namex AS Name_Sire_Dam_Sire,
Sire_Dam_Sire.assoc AS Assoc_Sire_Dam_Sire,
Sire_Dam_Sire.prefix AS Prefix_Sire_Dam_Sire,
Sire_Dam_Sire.regnum AS Regnum_Sire_Dam_Sire,
Sire_Dam_Dam.Namex AS Name_Sire_Dam_Dam,
Sire_Dam_Dam.assoc AS Assoc_Sire_Dam_Dam,
Sire_Dam_Dam.prefix AS Prefix_Sire_Dam_Dam,
Sire_Dam_Dam.regnum AS Regnum_Sire_Dam_Dam,
Dam_Sire_Sire.Namex AS Name_Dam_Sire_Sire,
Dam_Sire_Sire.assoc AS Assoc_Dam_Sire_Sire,
Dam_Sire_Sire.prefix AS Prefix_Dam_Sire_Sire,
Dam_Sire_Sire.regnum AS Regnum_Dam_Sire_Sire,
Dam_Sire_Dam.Namex AS Name_Dam_Sire_Dam,
Dam_Sire_Dam.assoc AS Assoc_Dam_Sire_Dam,
Dam_Sire_Dam.prefix AS Prefix_Dam_Sire_Dam,
Dam_Sire_Dam.regnum AS Regnum_Dam_Sire_Dam,
Dam_Dam_Sire.Namex AS Name_Dam_Dam_Sire,
Dam_Dam_Sire.assoc AS Assoc_Dam_Dam_Sire,
Dam_Dam_Sire.prefix AS Prefix_Dam_Dam_Sire,
Dam_Dam_Sire.regnum AS Regnum_Dam_Dam_Sire,
Dam_Dam_Dam.Namex AS Name_Dam_Dam_Dam,
Dam_Dam_Dam.assoc AS Assoc_Dam_Dam_Dam,
Dam_Dam_Dam.prefix AS Prefix_Dam_Dam_Dam,
Dam_Dam_Dam.regnum AS Regnum_Dam_Dam_Dam

FROM is3.animals animal
/* Sire and Dam of the Animal */
JOIN is3.animals Sire ON animal.Sire_bhid = Sire.bhid
JOIN is3.animals Dam ON animal.Dam_bhid = Dam.bhid
/* Sire and Dam of the Sire */
JOIN is3.animals Sire_Sire ON Sire.Sire_bhid = Sire_Sire.bhid
JOIN is3.animals Sire_Dam ON Sire.Dam_bhid = Sire_Dam.bhid
/* Sire and Dam of the Dam */
JOIN is3.animals Dam_Sire ON Dam.Sire_bhid = Dam_Sire.bhid
JOIN is3.animals Dam_Dam ON Dam.Dam_bhid = Dam_Dam.bhid
/* Sire and Dam of the Sire_Sire */
JOIN is3.animals Sire_Sire_Sire ON Sire_Sire.Sire_bhid =
Sire_Sire_Sire.bhid
JOIN is3.animals Sire_Sire_Dam ON Sire_Sire.Dam_bhid =
Sire_Sire_Dam.bhid
/* Sire and Dam of the Sire_Dam */
JOIN is3.animals Sire_Dam_Sire ON Sire_Dam.Sire_bhid =
Sire_Dam_Sire.bhid
JOIN is3.animals Sire_Dam_Dam ON Sire_Dam.Dam_bhid = Sire_Dam_Dam.bhid
/* Sire and Dam of the Dam_Sire */
JOIN is3.animals Dam_Sire_Sire ON Dam_Sire.Sire_bhid =
Dam_Sire_Sire.bhid
JOIN is3.animals Dam_Sire_Dam ON Dam_Sire.Dam_bhid = Dam_Sire_Dam.bhid
/* Sire and Dam of the Dam_Dam */
JOIN is3.animals Dam_Dam_Sire ON Dam_Dam.Sire_bhid = Dam_Dam_Sire.bhid
JOIN is3.animals Dam_Dam_Dam ON Dam_Dam.Dam_bhid = Dam_Dam_Dam.bhid
Jul 12 '06 #1
1 1616
Ian
Please see my reply to your previous thread.
Jul 12 '06 #2

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

Similar topics

4
by: Andreas Bauer | last post by:
Hi, I need some information on how to deal with strings which I have divided by using split(). I'm new to python and don't know how to handle these split up strings. And I some one could...
1
by: Sue | last post by:
Anyone have any ideas on why the code below will show up in a browser's sourcecode as an empty table, and is not visible? aspx: <headertemplate> <asp:Table ID="MyTable" runat="server" /> ...
8
by: Lu | last post by:
Hi there, I got a program to write data to a randomly accessed file (the program moves file pointer to a certain position of the file according the current "keyword" and then writes data). It...
3
by: jane | last post by:
HI, I have an performance questions... is # of tables in one tablespaces matter for performance point of view? why?
115
by: Mark Shelor | last post by:
I've encountered a troublesome inconsistency in the C-language Perl extension I've written for CPAN (Digest::SHA). The problem involves the use of a static array within a performance-critical...
2
by: tomdean20 | last post by:
Does anyone have a general idea of the difference in performance when compiling PHP with primarily shared extensions rather than static? A recent Yahoo presentation "PHP at Yahoo" highlighted that...
1
by: marcfischman | last post by:
Please help. I have a website running on a linux/apache/mysql/php server. I receive about 8,000-10,000 visitors a day with about 200,000 to 300,000 page views. The server is a RedHat Linux...
30
by: GeorgeRXZ | last post by:
Hi Friends, I have some questions related to C Language. 1What is the difference between the standard C language and Non standard C language ? 2which is better C Lanugage, C under Linux/...
4
by: joa2212 | last post by:
Hello everybody, I'm posting this message because I'm quiet frustrated. We just bought a software from a small software vendor. In the beginning he hosted our application on a small server at...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.