RE: SQL Performance Question -- MIDRANGE-L

This is a LONG POST!

Jay, I'm glad you asked this question because I'm just starting to bump up 
against the same issue in my development environment. Here are the steps I went 
through to optimize the query. I hope it helps!

I wanted to get some logic out of my program that lends itself well to SQL, so 
I created a view as:

----------

CREATE VIEW ST_PRES AS

SELECT * FROM DISTTEST.ST_DET (yep, this is a VIEW of table ST)

 LEFT JOIN DISTTEST.CD
  ON STKEY=CDSTKEY

 LEFT JOIN DISTTEST.CN
  ON STKEY=CNSTKEY

 LEFT JOIN DISTTEST.AL_DET (dear lord, another VIEW)
  ON
   ALTYP='*CD' AND CDKEY=ALKEY OR
   ALTYP='*CN' AND CNKEY=ALKEY OR
   ALTYP='*ST' AND STKEY=ALKEY

----------

If I expand this whole mofo into one big SQL statement, I get:

SELECT * FROM DISTTEST.ST


 <was st_det>

 LEFT JOIN DISTTEST.STF
  ON STSTF=STFKEY

 LEFT JOIN DISTTEST.TXT
  ON TXTTYP='*ST' AND STKEY=TXTKEY

 </st_det>


 LEFT JOIN DISTTEST.CD
  ON STKEY=CDSTKEY

 LEFT JOIN DISTTEST.CN
  ON STKEY=CNSTKEY

 LEFT JOIN DISTTEST.AL
  ON
   ALTYP='*CD' AND CDKEY=ALKEY OR
   ALTYP='*CN' AND CNKEY=ALKEY OR
   ALTYP='*ST' AND STKEY=ALKEY


 <was al_det>

 LEFT JOIN DISTTEST.ART
  ON ALART=ARTKEY

 LEFT JOIN DISTTEST.LBL
  ON ALLBL=LBLKEY

 </al_det>

----------

So, I got me the non-recommended two-views-within-a-view.

Performance on this query is downright pathetic, but then I expected as much on 
my 510-2143. With debug on, running a SELECT * over this view in ISQL takes 12 
seconds. Obviously something's gotta be done if I'm going to use this in 
production. For this application, I stand by my decision to put this "logic" in 
a view (well, for now anyway), so I'm intent on improving the query performance.

So I review the messages in the joblog and find:

First, the query is being processed in multiple steps

01   DISTTEST/AL_DET       AL_DET     DISTTEST/AL           AL        ,
     DISTTEST/AL_DET       AL_DET     DISTTEST/ART          ART       ,
     DISTTEST/AL_DET       AL_DET     DISTTEST/LBL          LBL       ,
02   DISTTEST/ST_DET       ST_DET     DISTTEST/ST           ST        ,
     DISTTEST/ST_DET       ST_DET     DISTTEST/STF          STF       ,
     DISTTEST/ST_DET       ST_DET     DISTTEST/TXT          TXT       ,
     DISTTEST/CD           CD         DISTTEST/CD           CD        ,
     DISTTEST/CN           CN         DISTTEST/CN           CN        ,
     *N/*QUERY0001         *N         *N/*QUERY0001         *N        .


For step 1:

The access path for AL that I thought the system would be using was rejected 
for r/c 17, mismatched keys. AL_IDX_PRI is an index on ALTYP, ALKEY. Looks like 
a match to me, but the optimizer doesn't agree.

ART and LBL had usable access paths, so we're cool there.

The next "problem" is a temp file is being build for ST_DET. This alone took 
2.2 seconds.


For step 2:

All access paths for ST were rejected

CN and CD had useable access paths.

----------

So, to get a better idea of what is going on, I rewrote the query without using 
any other views (thanks to Vern!):

SELECT * FROM DISTTEST.ST
 LEFT JOIN DISTTEST.STF
  ON STSTF=STFKEY
 LEFT JOIN DISTTEST.TXT
  ON TXTTYP='*ST' AND STKEY=TXTKEY
 LEFT JOIN DISTTEST.CD
  ON STKEY=CDSTKEY
 LEFT JOIN DISTTEST.CN
  ON STKEY=CNSTKEY
 LEFT JOIN DISTTEST.AL
  ON
   ALTYP='*CD' AND CDKEY=ALKEY OR
   ALTYP='*CN' AND CNKEY=ALKEY OR
   ALTYP='*ST' AND STKEY=ALKEY
 LEFT JOIN DISTTEST.ART ON ALART=ARTKEY
 LEFT JOIN DISTTEST.LBL ON ALLBL=LBLKEY

(I know it's a lot of left joins, but I use null values pretty extensively)

----------

Boom! The query now takes 4 seconds, down from 12.

Examining the log now shows:

ST: No access path found
CN: Access path Ok
CD: Ok
AL: Now Ok!
ART: Ok
LBL: Ok

No mention of TXT.

(What I'm not showing here is that I have created quite a few indices already, 
and those are now getting used, otherwise I should be able to find enough info 
to know which to create to help it out)

----------

I created a view called ST_PRES_2 using the query above. In debug, a SELECT * 
from that view completes in 1 second. Success!

I do still get this in the joblog:

QRY1157: Omit bad or useless data to make query run faster. (mentions 18 
instances)...but I'm not sure what to do, if anything, about it.

----------

You can debate whether or not this would have been "better" done in RPG or 
whatever, but I find SQL to be much simpler to read than a series of chains, 
and much easier to code, so although I agree that I could probably get better 
performance in RPG, I'd hate to have to make changes to it. But in SQL...no 
problem!

Z


-----Original Message-----
From: Vern Hamberg [mailto:vhamberg@centerfieldtechnology.com]
Sent: Thursday, December 26, 2002 6:08 PM
To: midrange-l@midrange.com
Subject: RE: SQL Performance Question


Jay again

Eric's recommendations are right on the money. You just STRDBG and run your
query. Then look at your joblog. If it's ODBC, it's harder to identify the
server job. But V5R1+ ODBC driver has the option to turn on debug for the
connection.

Views are only SQL statements stored in a logical file. There is no access
plan attached, so optimization is done every time you use the view. Nesting
a view inside another select is probably going to be disastrous for
performance.

Even index recommendations for views will not help much - a temp result set
has no reference to an existing index, so a temp index must be built. And
if it's built, it WILL be used, no matter the efficiency of doing so.
Whereas if you have an index against a table, the optimizer can decide
whether the index is helpful - optimization is much faster. (Of course,
that can be less important in a long-running query.)

Nesting views 3-deep and more is really awful.

The benefit to views is simplicity in development - cost is potentially
horrible performance.

I believe the same issue can apply to "with tempname as select ...." in a
SELECT statement.

Blatant commercial - we have products that are focused on SQL performance -
if interested, look at <www.centerfieldtechnology.com> - our stand-alone
product called sql/OPTIMIZER lets you do more with various settings,
including the QAQQINI file, than OpsNav does - although that one is OK. And
you don't need to start a monitor, as you do in OpsNav.

End of commercial

HTH

Vern

At 01:50 PM 12/26/02 -0600, you wrote:
>Jay,
>
>Ok, Black magic time.....
>
>The question appears to be "Why doesn't the SQL view help performance".
>
>My answer: I suspect that the statement that does NOT utilize the the view
>is taking advantage of access path information (ie., index) to construct the
>joined dataset.  A view does NOT have an index, so SQL thinks it must create
>a temporary table (from the view), index the temp table, then join the data.
>
>
>You can get a great deal of information from OS400 about how to improve
>performance.  For a start, running this in debug and reviewing the joblog
>should get you a wealth of information, as would STRDBMON and Visual
>Explain.
>
>Eric DeLong
>Sally Beauty Company
>MIS-Project Manager (BSG)
>940-898-7863 or ext. 1863
>
>
>
>-----Original Message-----
>From: Jay Himes [mailto:j@liberty.edu]
>Sent: Thursday, December 26, 2002 1:27 PM
>To: midrange-l@midrange.com
>Subject: SQL Performance Question
>
>
>I am having problems with a query I have written. The example below uses a
>view to access data for a website. The version using the view takes about
>1.5 seconds to return records; however, the version which does not use the
>view takes only .3 seconds. The reason appears to be that the query using
>the view builds a temporary table of the data in the view and then a
>temporary index on the temporary table; while the query without the view
>uses the view I have created to improve performance.
>
>Obviously I can just use the version with out the view; but I am curious as
>to why this happens; IMO views should improve performance (as well as ease
>of use); not degrade it.
>
>I am on V5R2 with the latest cume; the tables referenced have between
>100,000 and 500,000 records; the view contains 17,000 records.
>
>FASTER (.3 sec):
>select c1fnam, c1lnam, c1mnam, sbloaa, pycd23,
>sblnaa, avpycd, ifc5aa, SBQOAK, SBU2A3, sbpeae,
>  GDUD70
>   from nammsp
>    left join stdmsp on c1nmid = sbnmid
>    left join PPARCHVP on c1nmid =  nmid23 and year23=2002 and term23=40
>      and ACTV23 = 'Y' AND WTHD23 <> 'Y'
>    left join buavrel0 on c1nmid = avnmid
>      and ava7cd = 'RE'
>    left join nmintl0 on c1nmid = ifnmid
>    left join finadl on c1nmid = nmid70 and ACYR70 = 2002
>   where c1nmid = 3600
>
>SLOWER (1.5 sec):
>  select c1fnam, c1lnam, c1mnam, sbloaa, pycd23,
>  sblnaa, avpycd, ifc5aa, SBQOAK, SBU2A3, sbpeae,
>   GDUD70       from nammsp
>     left join stdmsp on c1nmid = sbnmid
>     left join enrolled_plans on c1nmid =
>       nmid23 and year23 = 2002 and term23 = 40
>      left join buavrel0 on c1nmid = avnmid
>       and ava7cd = 'RE'
>      left join nmintl0 on c1nmid = ifnmid
>      left join finadl on c1nmid = nmid70 and
>       ACYR70 = 2002
>   where  c1nmid = 3600
>
>CREATE VIEW ENROLLED_PLANS AS SELECT * FROM LBUCMFIL.PPARCHVP WHERE
>ACTV23 = 'Y' AND WTHD23 <> 'Y'
>
>_______________________________________________
>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
>To post a message email: MIDRANGE-L@midrange.com
>To subscribe, unsubscribe, or change list options,
>visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
>or email: MIDRANGE-L-request@midrange.com
>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@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.