MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » September 2009

Re: Views vs. Direct SQL - Overhead Savings?



fixed

To add to the other remarks - a view is not like a traditional logical file, in that there is really not an access PATH stored there. It basically encapsulates a SELECT statement, and that statement will be executed afresh each time the view is used. As Birgitta suggests, there might be an access PLAN associated with the view. And those can now be cached, for improved performance. Of course, if the environment or number of records changes or whatever, then all bets are off - the engine has to run optimization again.

Now there ARE improvements, as others have said. I worked with Elvis some years ago, and we often saw nested views being very slow. I'm glad that seems to be fixed.

HTH
Vern

Elvis Budimlic wrote:
DB2 can implement queries against views in two ways: view optimization and
view materialization. Default option is view optimization which means DB2 will combine two (or
more if view over view) queries and rewrite them internally into a single
query. This usually performs quite well, but not necessarily better than a
straight query you'd write.
If the combined query (views and final select) is really complex, DB2 may
decide to materialize the view. This may be slow. This used to happen a lot
on past releases when folks built views on top of other views, but I haven't
seen that as an issue in several releases now. So you can ignore that
scenario for the time being.

What does all that mean? Views may indeed help your queries performance,
but that would be a side-effect of a different access plan, not because
views are somehow inherently performance boosters.

What you need to do as a DBA (I know that's probably not your title, but it
sure sounds like that's part of your job duties) is to ensure proper SQL
performance tuning has been done. As Birgitta said, best starting place is ensuring key tables are properly
indexed (not views, SQL indexes).
Step you're taking with separating these jobs into their own memory pool is
likely to help as well, provided jobs aren't starved of memory in the
process (i.e. too small of a memory pool is allocated to the subsystem).
Once you got those tasks completed, then you may have to analyze individual
queries that are still performing poorly for a possibility of rewrite.
Rewriting is really not DBA's responsibility, but a DBA can help identify
poorly performing queries to the developers and let them take it from there
(CTEs have proven a tremendous tool for this step because they often cause
DB2 to create a different access plan, usually a better one).

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Views vs. Direct SQL - Overhead Savings?

Hi All,

Can someone tell me if there are any obvious overhead savings by using a
view to access data on an AS400 as opposed to direct SQL requests? I know
there are obvious reasons for using a view (security, optimization, etc.),
but will this result in less CPU usage?

As you may recall, we have had issues in the past with our QZDASOINIT job
being an absolute pig and consuming a lot of resources on the systems. We
have combated this issue by reducing the priority on the QZDASOINIT jobs and
are looking to move it to it's own memory pool. Would the addition of
moving these SQL statements to a view help?

Thanks,

/b;

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Brian Piotrowski








Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot 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 here. If you have questions about this, please contact