RE: SQL table joins: Join vs. Where -- MIDRANGE-L

Jay, interesting results.

I am interested in the optimizer messages for each case. Just STRDBG with no program name, then run the SQL. The job log will have what the optimizer did. Or, if that does not say enough, STRDBMON over the job where the statements will run. Of course, you want our tools to analyze the DBMON data more easily. <vbg> Actually, there's a nice document from IBM that can help analyzing this turgid data.

Also, did you run them in the same order for all tests? And did you SETOBJACC *PURGE & CLRPOOL between runs, so that there was no data left in memory? This is necessary to compare apples to apples when doing unit tests such as this. Or, to put it another way, to minimize the independent variables, so that the only thing that changes is the join syntax (that we know of). Oh, yeah! And, if possible, a dedicated machine. Hah! As one person said, given the same conditions, computers always do exactly the same thing. The trouble with the iSeries is, it is in continual flux, so conditions are very difficult to control, hence possible great variability in test results.

Vern

At 09:30 AM 8/13/2004, you wrote:

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.


--
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.