Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Multiple Select Queries vs Single Join Query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vasif

New User


Joined: 11 Feb 2008
Posts: 35
Location: Chennai

PostPosted: Thu Dec 03, 2009 11:58 am    Post subject: Multiple Select Queries vs Single Join Query
Reply with quote

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
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6970
Location: porcelain throne

PostPosted: Thu Dec 03, 2009 12:44 pm    Post subject:
Reply with quote

deleted by poster
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6970
Location: porcelain throne

PostPosted: Thu Dec 03, 2009 4:31 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Dec 03, 2009 5:12 pm    Post subject:
Reply with quote

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
View user's profile Send private message
vasif

New User


Joined: 11 Feb 2008
Posts: 35
Location: Chennai

PostPosted: Thu Dec 03, 2009 8:45 pm    Post subject:
Reply with quote

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
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Dec 03, 2009 9:01 pm    Post subject:
Reply with quote

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
View user's profile Send private message
vasif

New User


Joined: 11 Feb 2008
Posts: 35
Location: Chennai

PostPosted: Fri Dec 04, 2009 6:22 pm    Post subject:
Reply with quote

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
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Dec 04, 2009 6:47 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6970
Location: porcelain throne

PostPosted: Fri Dec 04, 2009 6:54 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to select record x+7 or x+2 based... JoAnn Kulcyk SYNCSORT 0 Tue Jan 16, 2018 10:49 pm
No new posts reg query on DYNALLOC feature raghuraman123 SYNCSORT 12 Wed Jan 10, 2018 2:42 pm
No new posts Sum the fields in particular format f... sharana64 DFSORT/ICETOOL 9 Fri Jan 05, 2018 1:11 am
No new posts query to fetch record which has only ... maxsubrat DB2 12 Mon Dec 11, 2017 5:03 pm
No new posts Merging 2 records at multiple rows wi... Bijesh DFSORT/ICETOOL 2 Wed Dec 06, 2017 1:50 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us