By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,369 Members | 1,147 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,369 IT Pros & Developers. It's quick & easy.

Performance of SQL

P: n/a
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"] . "')";
/

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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"] . "')"; /

Nov 12 '05 #2

P: n/a
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"] . "')"; /

Nov 12 '05 #3

P: n/a
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"] . "')"; /

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.