1. Views are nothing else then stored SELECT statements
2. When executing a view the query optimizer sees the complete view (even if
you have nested views). During the optimization it analyses the
SELECT-Statement (independent on whether it is specified directly or in a
view) and interacts with the statistics manager and the existing access path
(indexes and views) ... and finally an access path is generated.
3. Based on the access path the ODP (Open Data Path) is opened. ... and this
is the most time consuming step in the query execution, because it first has
to "materialize" the access plan, i.e. link the tables, indexes and build
the temporary objects described in the access path and then populates with
data and returns the data.
From SQL perspective you can only affect 2 things:
1. The way how a SQL Statement is written
2. By providing the best indexes
Without having seen more of your queries, data and indexes it is hard to
advice anything.
In either way I'd check if the statistics are up to date. May be you are
working with old data.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
IBM Champion since 2020
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience ? everything else is only information!" (Albert
Einstein)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Greg
Wilburn
Sent: Thursday, 2 November 2023 16:57
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: General SQL Performance Question
Over the years I have created quite a few SQL views over our database. They
greatly simplify our ERP database.
We are having performance issues since going to v7r5 (coincidence?)... IBM
has taken numerous traces, dumps, etc. and the issue appears to be directly
related to disk I/O - response times are very poor (+60 ms). We've added
some indexes advised by IBM and RSS, etc. but they haven't helped much.
One particular SQL view (that gets used a lot) keeps coming up in the
conversation - OPEN_PICKS. This view contains joins from one table to 9
other views. In some cases, those other views contain columns (fields) that
are not necessary for the OPEN_PICKS view. Three of the views were created
over a large table (many rows) specifically to feed OPEN_PICKS and simplify
(for me) the SQL statement.
My question is this... would it improve performance and/or assist the query
optimizer to re-tool OPEN_PICKS and remove the other views in favor of
directly referencing the specific tables/columns that I need? Or, should
this not really affect the overall performance that much?
Thanks,
Greg
[Logo]<
https://www.totalbizfulfillment.com/> Greg Wilburn
Director of IT
301.895.3792 ext. 1231
301.895.3895 direct
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
1 Corporate Dr
Grantsville, MD 21536
www.totalbizfulfillment.com<
http://www.totalbizfulfillment.com>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
midrange.com
