RE: SQL Performance Question

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

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'