Query performance

[Robert Stearns]

I am currently using the following query to select rows for a report. It
takes forever, even though there are < 100 rows in the result set; The
problem is that animals, epd and ent_herdid are all large tables, but
the selected rows of animal_sets are always much fewer. I, not being an
SQL guru by any means tried the second select statement in order to
reduce the size of the join operations early. DB2 must know about my
lack of expertise, I swear I heard chuckling along with my error message
(but that might have the currently playing cut on my sound system)
reproduced even further below.

Any suggestions on how I can improve the performance would be greatly
appreciated.

BTW bhid is the primary key or an index of animals, epd and ent_herdid.

"SELECT tattoo, herd_id tag,namex,prefix,regnum,t2.*
FROM ($schema.animals t1 LEFT OUTER JOIN
$schema.epd t2 on t1.bhid=t2.bhid)
JOIN $schema.ent_herdid t3 on t1.bhid=t3.bhid
WHERE t1.bhid IN
(SELECT bhid FROM $schema.animal_sets
WHERE set_name='$setname' AND userid='$setuser')";

SELECT tattoo, herd_id tag,namex,prefix,regnum,t2.*
FROM (is3.animals t1 WHERE t1.bhid IN
(SELECT bhid FROM is3.animal_sets
WHERE set_name='AN' AND userid='jhough')
LEFT OUTER JOIN
is3.epd t2 on t1.bhid=t2.bhid)
JOIN is3.ent_herdid t3 on t1.bhid=t3.bhid;
[IBM][CLI Driver][DB2/LINUX] SQL0104N An unexpected token "WHERE" was
found following "FROM (is3.animals t1". Expected tokens may include:
"JOIN". SQLSTATE=42601

[Serge Rielau]

You need a SELECT ... FROM <tablename> WHERE <predicate>
Just (<tablename> WHERE <predicate>) alone is a not a legal SQL query.
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Knut Stolze]

Robert Stearns wrote:

I am currently using the following query to select rows for a report. It
takes forever, even though there are < 100 rows in the result set; The
problem is that animals, epd and ent_herdid are all large tables, but
the selected rows of animal_sets are always much fewer. I, not being an
SQL guru by any means tried the second select statement in order to
reduce the size of the join operations early. DB2 must know about my
lack of expertise, I swear I heard chuckling along with my error message
(but that might have the currently playing cut on my sound system)
reproduced even further below.

Any suggestions on how I can improve the performance would be greatly
appreciated.

BTW bhid is the primary key or an index of animals, epd and ent_herdid.

"SELECT tattoo, herd_id tag,namex,prefix,regnum,t2.*
FROM ($schema.animals t1 LEFT OUTER JOIN
$schema.epd t2 on t1.bhid=t2.bhid)
JOIN $schema.ent_herdid t3 on t1.bhid=t3.bhid
WHERE t1.bhid IN
(SELECT bhid FROM $schema.animal_sets
WHERE set_name='$setname' AND userid='$setuser')";

I have no hard data to support this, but very ofter I get much better
response times by using a correlated subquery (as opposed to an
uncorrelated one as in your case). You could try this:

SELECT tattoo, herd_id tag, namex, prefix, regnum, t2.*
FROM ($schema.animals t1 LEFT OUTER JOIN
$schema.epd t2 on t1.bhid=t2.bhid) JOIN
$schema.ent_herdid t3 on t1.bhid=t3.bhid
WHERE EXISTS ( SELECT 1
FROM $schema.animal_sets AS a
WHERE a.set_name = '$setname' AND
a.userid = '$setuser' AND
a.bhid = t1.bhid )";

Also, do you have an index on the columns "set_name" and/or "userid" of the
table "animal_sets"?

p.s: The usual performance-related steps should also be applied like looking
at the query plan and physical database design.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena