× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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'



As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.