|
View previous topic :: View next topic
|
| Author |
Message |
vasif
New User
Joined: 11 Feb 2008
Posts: 35
Location: Chennai
|
|
|
|
Hi,
Which one of the 2 approaches below is better provided indexes are availabe on the columns - id & dno in respective tables.
| Code: |
select f.dno, d.dname, c.sname....
into :f-dno, :d-dname, :c-sname...
from
faculty f, dept d, stud s
where f.dno = d.dno
and f.dno = s.dno
and f.id = :host-var-fac-id
|
------OR----------
| Code: |
select f.dno,... into :f-dno,...
from faculty f where f.id = :host-var-fac-id
select d.dno,... into :d-dno,....
from dept d where d.dno = :f-dno
select s.dno,... into :s-dno,...
from stud s where s.dno = :d-dno
|
----------------------------------------------------
Elaborate explanation would be appreciated.[/code] |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| deleted by poster |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
better for what?
and it depends
A three table join, with good indexing should not be too much of a monster.
The three selects mean 3 CALLs to DB2, instead of 1.
The join only has one host variable, whereas all three of the selects have a host variable.
I would check out the EXPLAIN for both scenarios.
would personally use the JOIN if the EXPLAIN does not show great impact. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
| I'll vote in for "it depends". Just execute both the queries for different sets of data and compare the results. Scratch a graph and pick-up your choice out of comparison. |
|
| Back to top |
|
|
vasif
New User
Joined: 11 Feb 2008
Posts: 35
Location: Chennai
|
|
|
|
please give me some elaboration on what it 'depends'?
What to check for in the explain output for the 'above case' i.e any specifics would be highly helpful. |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
What if you need to handle more than 1 host-var-fac-id ; would you be executing the 3 selects multiple times ? DB2 has provided a way to retrieve data from multiple tables - JOINS - Use it.
As mentioned earlier "A three table join, with good indexing should not be too much of a monster." |
|
| Back to top |
|
|
vasif
New User
Joined: 11 Feb 2008
Posts: 35
Location: Chennai
|
|
|
|
Anybody please give me some elaboration on what it 'depends'?
What to check for in the explain output for the 'above case' i.e any specifics would be highly helpful.
Also consider the scenario where in the indexes can have duplicates on the columns id & dno .
Can there be a situation where the 2nd approach proves to be quicker than the 1st. If yes, exemplify. |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
Vasif, Do you have any problem in checking with your seniors ?
If no, please do that.
They will be able to help you based on table statistics , indexes on which all cols etc |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
The fact that Vasif has posted this question on two websites indicates
that he has no intention of either- doing any reading/research/testing on his own
- asking anyone at his site anything, as he has probably already told them that he knows it all
|
|
| Back to top |
|
|
|
|
