RE: Views vs. Direct SQL - Overhead Savings?

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