Re: DB View performance -- MIDRANGE-L

Would a CTE help here? Something like:
With the as(
Select count(*) as nrows

From my table

)
Select to.nrows... etc

Regards,
Luis

Sent from my Moto G phone. Please excuse my brevity.

On Thu, May 28, 2026, 02:47 Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx> wrote:

First the problem is not the View (a view is only a stored SELECT
Statement).
But without seeing the SQL-Statement that is stored in the View it is hard
to suggest anything.
There are only 2 thing we can affect when optimizing SQL:
1. How the query (Select Statement) is written, there are a lot of things
which may prevent the optimizer to use an index.
2. Creating the right indexes

In your case, i.e. adding a count(*), the optimizer has to read "all" rows.
There might be a way creating an Encoded Vector Index (EVI) with an Include
Aggregate Function
... but without knowing any thing else about your query and your database
it
is like looking in a cristal ball.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i
Database and Software Architect
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: Wednesday, 27 May 2026 19:42
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: DB View performance

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://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.

--
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.