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 <[email protected]> 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 <[email protected]>
> 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 <[email protected]> On Behalf Of
> Greg
> > Wilburn
> > Sent: Wednesday, 27 May 2026 19:42
> > To: Midrange Systems Technical Discussion <[email protected]
> >
> > 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
> > [email protected]<mailto:[email protected]
> >
> > 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: [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.
> >
> >
> --
> 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.