RE: SQL table joins: Join vs. Where

["Himes, Jay" <jehimes@xxxxxxxxxxx>]

I did a little (unscientific) experiment here are the results:

I created 2 source tables and 1 result table:
CREATE TABLE JSPTSTB(BKEY1 NUMERIC (9 , 0) NOT NULL WITH DEFAULT,   
BKEY2 NUMERIC (9 , 0) NOT NULL WITH DEFAULT, BDATA CHAR (50 ) NOT   
NULL WITH DEFAULT, PRIMARY KEY (BKEY1, BKEY2))                      

CREATE TABLE JSPTSTA(AKEY1 NUMERIC ( 9, 0) NOT NULL WITH DEFAULT, 
ADATA CHAR ( 50) NOT NULL WITH DEFAULT, PRIMARY KEY (AKEY1))

CREATE TABLE IDTMP(NMID NUMERIC (9 , 0) NOT NULL WITH DEFAULT)  

I inserted 281961 rows (from a production file) into the primary table
insert into JSPTSTA                         
select c1nmid, c1job from nammsp   

I inserted 5 x 281961 rows into the secondary table
insert into JSPTSTB                            
select c1nmid, 1, c1job || c1usr from nammsp 
insert into JSPTSTB                            
select c1nmid, 2, c1job || c1usr from nammsp 
insert into JSPTSTB                            
select c1nmid, 3, c1job || c1usr from nammsp 
insert into JSPTSTB                            
select c1nmid, 4, c1job || c1usr from nammsp 
insert into JSPTSTB                            
select c1nmid, 5, c1job || c1usr from nammsp

I created the following index:
CREATE INDEX JSPTSTBI1 ON JEHIMES/JSPTSTB (BKEY2) 

I ran the following queries (V5R2 825 1 proccessor LPAR with 2 Gig RAM):

A:
insert into idtmp                                               
select t1.Bkey1 from                                            
jsptsta join jsptstb as t1 on akey1=t1.bkey1 and t1.bkey2 = 3   
left join jsptstb as t2 on akey1=t2.bkey1 and t2.bkey2 = 2      

B:
insert into idtmp                             
select t1.Bkey1 from                          
jsptsta , jsptstb as t1, jsptstb as t2        
where akey1=t1.bkey1 and t1.bkey2 = 3 and     
(t2.bkey1=akey1 or t2.bkey1 is null)          
 and (t2.bkey2=2 or t2.bkey2 is null     

Each run was in a separate submitted job
Run 1:
        B: 20 sec
        A: 14 sec
Run 2:
        B: 21 sec
        A: 13 sec

I removed the index
drop INDEX JSPTSTBI1 

Run 3:
        A: 12 sec
        B: 20 sec

Run 4:
        A: 14 sec
        B: 20 sec

I suspect that your results will very based on the complexity of the
query you are running.

-----Original Message-----
From: midrange-l-bounces+jehimes=liberty.edu@xxxxxxxxxxxx
[mailto:midrange-l-bounces+jehimes=liberty.edu@xxxxxxxxxxxx] On Behalf
Of CWilt@xxxxxxxxxxxx
Sent: Thursday, August 12, 2004 9:37 AM
To: midrange-l@xxxxxxxxxxxx
Subject: RE: SQL table joins: Join vs. Where

Do you mean that the following:

select A.A1, B.B1
from A inner join B
   on A.K1 = B.K1

will be faster than

select A.A1, B.B1
from A, B
where A.K1 = B.K1


I'd like to know where you got that information, because as I understand
it that is simply not the case.  Both queries would be run exactly the
same way.

Thanks,
Charles


> -----Original Message-----
> From: Himes, Jay [mailto:jehimes@xxxxxxxxxxx]
> Sent: Wednesday, August 11, 2004 4:40 PM
> To: Midrange Systems Technical Discussion
> Subject: RE: SQL table joins: Join vs. Where
> 
> 
> Also, the a query written using the join systax is usually 
> significatly faster than an equivalent query using the where clause.
> 
> Jay Himes
> Liberty University
> 
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.