Re: DB View performance

Daniel Gross <[email protected]>
Newsgroups gmane.comp.hardware.ibm.midrange
Message-ID <[email protected]> 
> Am 27.05.2026 um 21:28 schrieb Greg Wilburn <[email protected]>:

> I’m not sure on the SQL Plan Cache – I IPL every Sunday morning.  Would that get reset (or is that just temporary indexes)?

The plan cache is emptied on IPL - so re-IPL-ing is a hit on performance - normally I would say, only IPL when you need it, as it "hurts" more, than it "heals"

> #1 seems like it strikes a chord with me… IBM actually helped me out with this way back.

Yeah - when I read your text, it rang a bell in my mind. 

> #2 the field is a numeric field and is part of the primary row select – it would always contain a value

If the field is in the primary table, then try COUNT(*) - I know there once was the myth, that COUNT(*) is slower than counting a value. 

See it like that - COUNT(myfield) needs to inspect every value of myfield for every row to find out, if it's NULL, as NULL values are not counted. Whereas COUNT(*) simply counts every row in the result set. 

> Good things to look at.  I’m sure there’s some kind of voodoo going on here. LOL.

"Any sufficiently advanced technology is indistinguishable from magic." -- Arthur C. Clarke

Kind regards,
Daniel


> From: MIDRANGE-L <[email protected]> On Behalf Of Daniel Gross
> Sent: Wednesday, May 27, 2026 2:23 PM
> To: [email protected]
> 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 <[email protected]<mailto:[email protected]>>:
>> 
>> 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:[email protected]> 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 <
>>> [email protected]<mailto:[email protected]>> 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
>>>> [email protected]<mailto:[email protected]<mailto:[email protected]%3cmailto:[email protected]>>
>>>> 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 a message email:[email protected]
>>>> To subscribe, unsubscribe, or change list options,
>>>> visit:https://protect.checkpoint.com/v2/r01/___https://lists.midrange.com/mailman/listinfo/midrange-l___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzoyZGZhMDJkYWIyYzNlZmExYzAwOTQ2ZjQ2MjhkYTBmMzo3Ojg1YzE6OTExOGY3ZjMzODI1NzhlYzY2MTA2ZTFiMmI2YWNkNmQ4NDYwYWUxZTVjMDNmODY2NzI0MDk3ZjgzYTJkZDkxYTpwOlQ6Rg
>>>> or email:[email protected]
>>>> Before posting, please take a moment to review the archives
>>>> athttps://protect.checkpoint.com/v2/r01/___https://archive.midrange.com/midrange-l___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzoyZGZhMDJkYWIyYzNlZmExYzAwOTQ2ZjQ2MjhkYTBmMzo3OmY1M2Q6MDMzMmM4ODU3OTFiZWZlZTI5ODNiZTMzOWY0ZDI2ZDk5ZGMwNzVkYzBmYWMyNDdmODg0MWNkYmE2Njc3N2IxNzpwOlQ6Rg.
>>>> 
>>>> Please [email protected]<mailto:[email protected]> for any subscription related
>>>> questions.
>>>> 
>>>> 
>> --
>> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
>> To post a message email: [email protected]<mailto:[email protected]>
>> 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>
>> or email: [email protected]<mailto:[email protected]>
>> Before posting, please take a moment to review the archives
>> at https://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>.
>> 
>> Please contact [email protected]<mailto:[email protected]> for any subscription related questions.
>> 
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: [email protected]<mailto:[email protected]>
> 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>
> or email: [email protected]<mailto:[email protected]>
> Before posting, please take a moment to review the archives
> at https://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>.
> 
> Please contact [email protected]<mailto:[email protected]> 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: [email protected]
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: [email protected]
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
> 
> Please contact [email protected] for any subscription related questions.
> 
-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: [email protected]
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: [email protected]
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact [email protected] for any subscription related questions.
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.