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: 6968
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: 6968
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: 6968
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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts ejck thru rexx to multiple jobs insid... Susanta All Other Mainframe Topics 5 Tue Sep 19, 2017 1:39 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts More than 1 Include criteria for sing... Roy Ware SYNCSORT 6 Wed Sep 13, 2017 11:45 pm
No new posts PLI - printing from multiple programs Pedro PL/I & Assembler 8 Fri Sep 08, 2017 6:36 am

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