Performance of SQL

[Robert Stearns]

In the query below (in php format, but the intent should be clear), the
result set from the sub select is always 1% or less of each of the two
tables which are joined in the main query. Is the query planner smart
enough to know (discover?) this or should rewrite the query as the join
of two sub selects (for which I could use some syntax help)? I ask
because some variant of this query is used by all of my reporting and
multiple row display screens, so its performance will impact the whole
application (also, it feels slow, but since I am the sole user, I could
be seeing buffer or other effects of the application sleeping for "long"
times and having to be woken up). If it makes a difference, I'm running
DB2 v 8.1.5 on a Linux system, with the application being
apache/php/Smarty. Any thought/assistance would be greatly appreciated.

$sql = "SELECT herd_id tag,namex,prefix,regnum,sex,birth_date,
sire_bhid,dam_bhid,location locationid,
'Exp Ranches (100%)' owners
FROM $schema.animals t1
JOIN $schema.ent_herdid
ON t1.bhid=t2.bhid
WHERE t1.bhid IN
(SELECT bhid FROM $schema.animal_sets
WHERE set_name='" . $_SESSION["setid"] . "')";
/

[Kim Bundgaard]

Do a rewrite of the query - making a little result before doing the join.
I can't do the exact wrting, because I can't see which column comes from
which table.
But something like :

Select xxx, yyy
from
(select z
from animals t1
join animal_sets t2
on t1.bhid = t2.bhid
) as s1
join ent_herdid t3
on s1.bhid = t3.bhid
"Robert Stearns" <rs**********@charter.net> wrote in message
news:10*************@corp.supernews.com...

In the query below (in php format, but the intent should be clear), the
result set from the sub select is always 1% or less of each of the two
tables which are joined in the main query. Is the query planner smart
enough to know (discover?) this or should rewrite the query as the join
of two sub selects (for which I could use some syntax help)? I ask
because some variant of this query is used by all of my reporting and
multiple row display screens, so its performance will impact the whole
application (also, it feels slow, but since I am the sole user, I could
be seeing buffer or other effects of the application sleeping for "long"
times and having to be woken up). If it makes a difference, I'm running
DB2 v 8.1.5 on a Linux system, with the application being
apache/php/Smarty. Any thought/assistance would be greatly appreciated.

$sql = "SELECT herd_id tag,namex,prefix,regnum,sex,birth_date,
sire_bhid,dam_bhid,location locationid,
'Exp Ranches (100%)' owners
FROM $schema.animals t1
JOIN $schema.ent_herdid
ON t1.bhid=t2.bhid
WHERE t1.bhid IN
(SELECT bhid FROM $schema.animal_sets
WHERE set_name='" . $_SESSION["setid"] . "')"; /

[Kim Bundgaard]

I forgot your where clause ...

Select xxx, yyy
from
(select z
from animals t1
join animal_sets t2
on t1.bhid = t2.bhid
and t2.set_name='" . $_SESSION["setid"]
) as s1
join ent_herdid t3
on s1.bhid = t3.bhid

"Robert Stearns" <rs**********@charter.net> wrote in message
news:10*************@corp.supernews.com...

In the query below (in php format, but the intent should be clear), the
result set from the sub select is always 1% or less of each of the two
tables which are joined in the main query. Is the query planner smart
enough to know (discover?) this or should rewrite the query as the join
of two sub selects (for which I could use some syntax help)? I ask
because some variant of this query is used by all of my reporting and
multiple row display screens, so its performance will impact the whole
application (also, it feels slow, but since I am the sole user, I could
be seeing buffer or other effects of the application sleeping for "long"
times and having to be woken up). If it makes a difference, I'm running
DB2 v 8.1.5 on a Linux system, with the application being
apache/php/Smarty. Any thought/assistance would be greatly appreciated.

$sql = "SELECT herd_id tag,namex,prefix,regnum,sex,birth_date,
sire_bhid,dam_bhid,location locationid,
'Exp Ranches (100%)' owners
FROM $schema.animals t1
JOIN $schema.ent_herdid
ON t1.bhid=t2.bhid
WHERE t1.bhid IN
(SELECT bhid FROM $schema.animal_sets
WHERE set_name='" . $_SESSION["setid"] . "')"; /

[Mark Yudkin]

You need to use the EXPLAIN facility to find out.

"Robert Stearns" <rs**********@charter.net> wrote in message
news:10*************@corp.supernews.com...

In the query below (in php format, but the intent should be clear), the
result set from the sub select is always 1% or less of each of the two
tables which are joined in the main query. Is the query planner smart
enough to know (discover?) this or should rewrite the query as the join
of two sub selects (for which I could use some syntax help)? I ask
because some variant of this query is used by all of my reporting and
multiple row display screens, so its performance will impact the whole
application (also, it feels slow, but since I am the sole user, I could
be seeing buffer or other effects of the application sleeping for "long"
times and having to be woken up). If it makes a difference, I'm running
DB2 v 8.1.5 on a Linux system, with the application being
apache/php/Smarty. Any thought/assistance would be greatly appreciated.

$sql = "SELECT herd_id tag,namex,prefix,regnum,sex,birth_date,
sire_bhid,dam_bhid,location locationid,
'Exp Ranches (100%)' owners
FROM $schema.animals t1
JOIN $schema.ent_herdid
ON t1.bhid=t2.bhid
WHERE t1.bhid IN
(SELECT bhid FROM $schema.animal_sets
WHERE set_name='" . $_SESSION["setid"] . "')"; /