Re: DB View performance

In the past, CTEs sometimes offered performance improvement over Nested
Table Expressions(NTE). But only when the CTE/NTE is referenced multiple
times in statement.

With a single reference, there's no real difference.

Charles

On Fri, May 29, 2026 at 9:35 AM Luis Rodriguez <luisro58@xxxxxxxxx> wrote:

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.

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