Re: DB View performance

 Can you run it through ACS and let "Explain" check it for table scans  etc.
    On Friday, May 29, 2026 at 10:35:36 AM CDT, 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.