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

Good point on the next 100 passes. I'd forgot to include that technique. That's why, in the IBM lab, we would run a couple warm-up passes, to stabilize things and make it more like production - if that is what we wanted to do. It was necessary when testing the new query engine, because various things need to smooth out, even in unit tests.

To address Joe's comment about clearing memory, etc., it definitely depends on the question. A tacit assumption often is "all other things being equal". Those other things can include a cold start (memory purged of data, etc.) or a hot start (a few runs to get memory populated with data). Any assumptions along this line, IMO, need to be expressly stated.

I'm finally going to get over to IAAI - just have not had time.

Vern

At 11:45 AM 8/13/2004, you wrote:

I added the test to the IAAI forum and included the debug information.
There isn't a program; just a bunch of SQL statements (you will need to
provide your own data). I calculated the times from the joblog of each
job.

I did not clear objects from memory between runs.  In my environment it
doesn't really make sense; I don't care about the performance on the
first pass as much as I do the next 100 passes.

Jay Himes
Liberty University

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta
Sent: Friday, August 13, 2004 12:19 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL table joins: Join vs. Where

> From: Vern Hamberg
>
> Jay, interesting results.
>
> I am interested in the optimizer messages for each case. Just STRDBG
with
> no program name, then run the SQL.

A good alternative would be to get the test over on the IAAI site and
then anybody who wanted to could download the programs and run them on
their own machine and report the results.


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

"Necessary" depends highly on what you're trying to prove, but feel free
to come on over to the IAAI and help us add some of those features to
the test suite.  Personally, I think that stuff is overkill because in a
real production environment you don't have those issues, and it will
skew the results towards techniques that don't take advantage of the
inherent cache mechanisms of OS/400.


In any event, the test suite is almost ready for public discourse.  I
will try to make the first version available this weekend.  It's not
intended to be commercial grade; it certainly won't worry about all of
your concerns, Vern.  But it will run tests and it will provide a simple
framework for adding more tests.  The only question is how much
documentation I'll have in place.  But those of you who are have strong
opinions about how benchmarks should be run, please come on over and add
your suggestions.


Joe
http://forums.plutabrothers.com/IAAI

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