|
But.... Greg was doing the weekly IPL's with the old view so the impact of reoptimization should have been felt with subsequent runs of that as well.
Roger Harman
COMMON Certified Application Developer - ILE RPG on IBM i on Power
-----Original Message-----
From: MIDRANGE-L<midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Vern Hamberg via MIDRANGE-L
Sent: Wednesday, May 27, 2026 1:16 PM
To:midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Vern Hamberg<vhamberg@xxxxxxxxxxxxxxx>
Subject: Re: DB View performance
Greg
The plan cache gets reset every time you IPL, and optimization starts
afresh after IPL. The first time any statement is run, it has to be
optimized after an IPL. Subsequent runs will get better. But IPLing as
often as this might reduce the benefit of the plan cache - there's a
balance to be maintained in there somewhere.
Something to try - you can use Visual Explain on a statement in the plan
cache - find the new version and use VE on it - and find the old
version, if you've run both after the IPL. See what is different.
*Regards*
*Vern Hamberg*
IBM Champion 2025<cid:part1.sF0ENVnp.1feitfog@centurylink.net> CAAC
(COMMON Americas Advisory Council) IBM Influencer 2023
On 5/27/2026 2:28 PM, Greg Wilburn wrote:
Vern,--
Prior to posting (and restoring the view), I ran through visual explain. Actually, I ran the SQL query being executed over the view through VE. I did NOT compare that to the original view.
I can recreate the offending view in a test library over the same data. I will say that RSS (Run SQL Scripts) completes the query in a few seconds while the application takes 30-40 seconds.
I’m not sure on the SQL Plan Cache – I IPL every Sunday morning. Would that get reset (or is that just temporary indexes)?
Daniel,
#1 seems like it strikes a chord with me… IBM actually helped me out with this way back.
#2 the field is a numeric field and is part of the primary row select – it would always contain a value
Good things to look at. I’m sure there’s some kind of voodoo going on here. LOL.
From: MIDRANGE-L<midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Daniel Gross
Sent: Wednesday, May 27, 2026 2:23 PM
To:midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Re: DB View performance
EXTERNAL EMAIL - This email was sent by a person from outside your organization. Exercise caution when clicking links, opening attachments or taking further action, before validating its authenticity.
Secured by Check Point
Aside from Visual Explain I have 2 ideas:
1. try to rebuild your indexes after the view is recreated - I had such a strange experience lately with a "coupled" view/index pair
2. does "myfield" contain NULLs? Or is "myfield" part of a LEFT JOINed table/view? If not, try COUNT(*) as it does not have to inspect every value of "myfield" it is normally faster in this particular case
HTH
Daniel
Am 27.05.2026 um 20:09 schrieb Vern Hamberg via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>>:--
In addition to Rob's suggestion, have you run Visual Explain over both the original and the new views? Something about this makes me wonder about a table scan, although the system should know how many rows are in a table without doing a table scan. You might do a table scan on the old view, as well.
I also wonder about the plan cache - the old view has been used a lot, and optimization has found the best plan. The new view might need to be run many times to get better performance. And Visual Explain would help with getting things better sooner.
Also, if you can, run VE in an environment that has substantially the same environment - table sizes, memory, things like that.
*Regards*
*Vern Hamberg*
IBM Champion 2025<cid:part1.PkYYiEO0.NE0lbl8t@centurylink.net> CAAC (COMMON Americas Advisory Council) IBM Influencer 2023
On 5/27/2026 12:47 PM, Rob Berendt wrote:--
Did you use the tool to retrieve the SQL source of your old view and
compare it to the one you recreated without the new column?
On Wed, May 27, 2026 at 1:42 PM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>> wrote:
I have a relatively complicated database view that references other
views. Back in 2020, I was able to create some indexes to improve
performance when this view was used.
Without going into a lot of detail, I added a column to the view to simply
count the lines in the primary table (the view already groups results). I
did this generating the DDL from ACS Schemas tool. Using run sql scripts,
I did a "create or replace view" after adding the column. This change
killed the performance, so I removed the new column and ran "create or
replace view" again.
Now to the odd part... the performance was still horrible. I didn't know
what to do, so I restored the view from our cloud backup - this fixed the
performance issue.
My question is why? What is happening behind the scene that I'm not
considering?
I honestly can't imagine that adding a "count(myfield) as mynewfield"
column would dramatically affect the performance.
Greg
[Logo]<https://protect.checkpoint.com/v2/r01/___https://www.totalbizfulfillment.com/___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzoyZGZhMDJkYWIyYzNlZmExYzAwOTQ2ZjQ2MjhkYTBmMzo3OjA3Njk6YjVhMjYxMTVjNWZmNWNmNWYyNjEwM2ViMmYyZjZhNTBkOTY4N2EwMThhNGRkMDQyNmM4YTBlZjEyMjBlODUyMDpwOlQ6Rg<https://protect.checkpoint.com/v2/r01/___https:/www.totalbizfulfillment.com/___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzoyZGZhMDJkYWIyYzNlZmExYzAwOTQ2ZjQ2MjhkYTBmMzo3OjA3Njk6YjVhMjYxMTVjNWZmNWNmNWYyNjEwM2ViMmYyZjZhNTBkOTY4N2EwMThhNGRkMDQyNmM4YTBlZjEyMjBlODUyMDpwOlQ6Rg>> Greg Wilburn
Director of IT
301.895.3792 ext. 1231
301.895.3895 direct
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx%3cmailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>>
1 Corporate Dr
Grantsville, MD 21536
https://protect.checkpoint.com/v2/r01/___www.totalbizfulfillment.com___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzoyZGZhMDJkYWIyYzNlZmExYzAwOTQ2ZjQ2MjhkYTBmMzo3OmFiNDY6M2QxM2ZmMjA4YjY1M2Y2ZmRlNWQ3NmUzNzM4NjdlYzlkYTY0MGFiODk3YmIwYTJkYTRhMzkwYzEyMjZhMjdkMDpwOlQ6Rg<https://protect.checkpoint.com/v2/r01/___http://www.totalbizfulfillment.com___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzoyZGZhMDJkYWIyYzNlZmExYzAwOTQ2ZjQ2MjhkYTBmMzo3OjU5NTQ6NTkzNjVmMjVlN2E0MTgxOTc4ZDRiYjk1YWI2OGViZjY1YzBhZDZlMGFkNjI4MDU1Nzc1YWZkZWU4ODg2ZWVmMzpwOlQ6Rg<https://protect.checkpoint.com/v2/r01/___www.totalbizfulfillment.com___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzoyZGZhMDJkYWIyYzNlZmExYzAwOTQ2ZjQ2MjhkYTBmMzo3OmFiNDY6M2QxM2ZmMjA4YjY1M2Y2ZmRlNWQ3NmUzNzM4NjdlYzlkYTY0MGFiODk3YmIwYTJkYTRhMzkwYzEyMjZhMjdkMDpwOlQ6Rg%3chttps:/protect.checkpoint.com/v2/r01/___http:/www.totalbizfulfillment.com___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzoyZGZhMDJkYWIyYzNlZmExYzAwOTQ2ZjQ2MjhkYTBmMzo3OjU5NTQ6NTkzNjVmMjVlN2E0MTgxOTc4ZDRiYjk1YWI2OGViZjY1YzBhZDZlMGFkNjI4MDU1Nzc1YWZkZWU4ODg2ZWVmMzpwOlQ6Rg>>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post amessageemail:MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:https://protect.checkpoint.com/v2/r01/___https://lists.midrange.com/mailman/listinfo/midrange-l___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzoyZGZhMDJkYWIyYzNlZmExYzAwOTQ2ZjQ2MjhkYTBmMzo3Ojg1YzE6OTExOGY3ZjMzODI1NzhlYzY2MTA2ZTFiMmI2YWNkNmQ4NDYwYWUxZTVjMDNmODY2NzI0MDk3ZjgzYTJkZDkxYTpwOlQ6Rg
oremail:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
athttps://protect.checkpoint.com/v2/r01/___https://archive.midrange.com/midrange-l___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzoyZGZhMDJkYWIyYzNlZmExYzAwOTQ2ZjQ2MjhkYTBmMzo3OmY1M2Q6MDMzMmM4ODU3OTFiZWZlZTI5ODNiZTMzOWY0ZDI2ZDk5ZGMwNzVkYzBmYWMyNDdmODg0MWNkYmE2Njc3N2IxNzpwOlQ6Rg.
Pleasecontactsupport@xxxxxxxxxxxxxxxxxxxx<mailto:contactsupport@xxxxxxxxxxxxxxxxxxxx> for any subscription related
questions.
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a messageemail:MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit:https://protect.checkpoint.com/v2/r01/___https://lists.midrange.com/mailman/listinfo/midrange-l___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzoyZGZhMDJkYWIyYzNlZmExYzAwOTQ2ZjQ2MjhkYTBmMzo3OmI4ZDk6M2I0YzA4MjU1ZmQ4NmMyMzZkNWNmMGQ0MzJhYmM4ZjU5MGNlZDNlMjkyY2JlZjliMWY5MmRiY2YwMGZhZTM4YTpwOlQ6Rg<https://protect.checkpoint.com/v2/r01/___https:/lists.midrange.com/mailman/listinfo/midrange-l___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzoyZGZhMDJkYWIyYzNlZmExYzAwOTQ2ZjQ2MjhkYTBmMzo3OmI4ZDk6M2I0YzA4MjU1ZmQ4NmMyMzZkNWNmMGQ0MzJhYmM4ZjU5MGNlZDNlMjkyY2JlZjliMWY5MmRiY2YwMGZhZTM4YTpwOlQ6Rg>
oremail:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
athttps://protect.checkpoint.com/v2/r01/___https://archive.midrange.com/midrange-l___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzoyZGZhMDJkYWIyYzNlZmExYzAwOTQ2ZjQ2MjhkYTBmMzo3OjIwMmY6YjYwZTJkZmQ1YjlkMDliODQ3MjZiODFmOGM5MTlmODA5MjU0ZGNiNWM4MzdiYTcwMDNmNGFkMjM3Y2Y4NzVmMzpwOlQ6Rg<https://protect.checkpoint.com/v2/r01/___https:/archive.midrange.com/midrange-l___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzoyZGZhMDJkYWIyYzNlZmExYzAwOTQ2ZjQ2MjhkYTBmMzo3OjIwMmY6YjYwZTJkZmQ1YjlkMDliODQ3MjZiODFmOGM5MTlmODA5MjU0ZGNiNWM4MzdiYTcwMDNmNGFkMjM3Y2Y4NzVmMzpwOlQ6Rg>.
Pleasecontactsupport@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a messageemail:MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit:https://protect.checkpoint.com/v2/r01/___https://lists.midrange.com/mailman/listinfo/midrange-l___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzoyZGZhMDJkYWIyYzNlZmExYzAwOTQ2ZjQ2MjhkYTBmMzo3OjllNjc6YmQ3ZTRhZjU4ZmZhOTg2MGVkOTQ1Yjk2ZGNlODA4ZjM4ZDAxOWQ2MWEzZTlhYmNhMmYwNjMxMTRlNWY0YjJkMjpwOlQ6Rg<https://protect.checkpoint.com/v2/r01/___https:/lists.midrange.com/mailman/listinfo/midrange-l___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzoyZGZhMDJkYWIyYzNlZmExYzAwOTQ2ZjQ2MjhkYTBmMzo3OjllNjc6YmQ3ZTRhZjU4ZmZhOTg2MGVkOTQ1Yjk2ZGNlODA4ZjM4ZDAxOWQ2MWEzZTlhYmNhMmYwNjMxMTRlNWY0YjJkMjpwOlQ6Rg>
oremail:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
athttps://protect.checkpoint.com/v2/r01/___https://archive.midrange.com/midrange-l___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzoyZGZhMDJkYWIyYzNlZmExYzAwOTQ2ZjQ2MjhkYTBmMzo3OmNhZDM6ODA4MGViYzQxMTIxYzA4NzUwM2Q0MTIyMmQyM2YzNGJmZjk0OGMwNWQ0YmI2MWVjMmM2NjViMmEzNWMyZDliZDpwOlQ6Rg<https://protect.checkpoint.com/v2/r01/___https:/archive.midrange.com/midrange-l___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzoyZGZhMDJkYWIyYzNlZmExYzAwOTQ2ZjQ2MjhkYTBmMzo3OmNhZDM6ODA4MGViYzQxMTIxYzA4NzUwM2Q0MTIyMmQyM2YzNGJmZjk0OGMwNWQ0YmI2MWVjMmM2NjViMmEzNWMyZDliZDpwOlQ6Rg>.
Pleasecontactsupport@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.
Greg Wilburn
Director of IT
301.895.3792 ext. 1231
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
athttps://archive.midrange.com/midrange-l.
Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2026 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.
midrange.com
