SQL Performance Question

["Jay Himes" <j@xxxxxxxxxxx>]

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'