RE: SQL Performance Question -- MIDRANGE-L

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.