RE: DB View performance

So I'll share the current/original view that exhibits good performance, along with what I added that dramatically affected performance.

Before I do that...  This view is a data source for an app inside CNX Valence.  The app will create other queries over the view based on user actions.
After restoring the view from backup, I decided to add my new field to the data source in Valence rather than in the view.  I did not observe any performance hit.


So below is the ORIGNIAL select statement from the view definition.  Note the "from" ASTDTA/OEORDLOD and "where" ODCTT#>0.

SELECT
                           DFT_WHS,
                                CLT.COM_NBR,
                                CLT.COM_NAME,
                                SUBSTR(ODGRC#,1,8) AS ENT_NBR,
                                CUS.ENT_NAME AS ENT_NAME,
                                SHIP_NAME,
                                ODWHS# AS WHS_NBR,
                                ODORD# AS ORD_NBR,
                                ODREL# AS REL_NBR,
                                SHPV_DESC AS SHP_VIA,
                                PIK_DATE,
                                PIK_TIME,
                                PAK_DATE,
                                PAK_TIME,
                        BOX_CNT,
                                TRN_TYPE,
                                PTY_CODE,
                                PTY_DESC


                        FROM ASTDTA/OEORDLOD
                        LEFT OUTER JOIN ASTHHOBJ/V_COMPANY CLT ON ODCOM# = CLT.COM_NBR
                        LEFT OUTER JOIN WQ_TBF/V_DEFAULT_WHS WHS ON ODCOM# = WHS.COM_NBR
                        INNER JOIN ASTHHOBJ/V_PICK_TRANS PIK ON PIK.COM_NBR=ODCOM# AND PIK.ORD_NBR=ODORD# AND PIK.REL_NBR=ODREL#

                        LEFT JOIN LATERAL (SELECT * FROM ASTHHOBJ/V_PACK_TRANS WHERE ODCOM#=COM_NBR AND ODORD#=ORD_NBR AND ODREL#=REL_NBR ORDER BY
                                    PAK_DATE DESC, PACK_TIME DESC FETCH FIRST ROW ONLY)
                        AS PAK ON PAK.COM_NBR=ODCOM# AND PAK.ORD_NBR=ODORD# AND PAK.REL_NBR=ODREL#

                        LEFT OUTER JOIN WQ_TBF/V_CUSTOMER_ADDRESSES CUS ON CUS.ENT_NBR=ODENT# AND CUS.ENT_SFX=ODSFX#
                        LEFT OUTER JOIN WQ_TBF/V_SHIP_ADDRESSES ON ODGRC# = SHIP_ID
                        LEFT OUTER JOIN WQ_TBF/V_PRIORITY_CODES ON ODPRTC = PTY_CODE
                        LEFT OUTER JOIN WQ_TBF/V_SHIP_VIA ON ODVIAC = SHPV_CODE
                   LEFT OUTER JOIN ASTHHOBJ/V_PACK_SUMMARY PSM ON PSM.COM_NBR=ODCOM# AND PSM.ORD_NBR=ODORD# AND PSM.REL_NBR=ODREL#
                        WHERE ODCTT# >0
                        GROUP BY DFT_WHS,
                                CLT.COM_NBR,
                                CLT.COM_NAME,
                                SUBSTR(ODGRC#,1,8),
                           CUS.ENT_NAME,
                                SHIP_NAME,
                                ODWHS#,
                                ODORD#,
                                ODREL#,
                                SHPV_DESC,
                                PIK_DATE,
                                PIK_TIME,
                                PAK_DATE,
                                PAK_TIME,
                        BOX_CNT,
                                TRN_TYPE,
                                PTY_CODE,
                                PTY_DESC)


I simply wanted to add a count of the records selected from OEORDLOD.

So I added a single column defined as "count(odctt#)".  I did this because I thought "count(*)" was bad, and the "where" statement already qualified on that field.

*This is where the performance took a big hit*

Now, I've left the view alone and added the column I need to my Valence data source using a sub-select.  Performance seems ok.

The data source looks like this:

SELECT
  com_nbr,
  com_name,
  ent_nbr,
  ent_name,
  ship_name,
  whs_nbr,
  ord_nbr,
  rel_nbr,
  ship_via,
  pik_date,
  pik_time,
  pak_date,
  pak_time,
  box_cnt,
  trn_type,
  pty_code,
  pty_desc,
  (
    SELECT
      COUNT(*)
    FROM
      oeordlod
    WHERE
      (
        odcom# = com_nbr AND odord# = ord_nbr AND odrel# = rel_nbr
      )
  ) AS line_cnt
FROM
  v_opnpicks
WHERE
  (pty_code not in ('MMC', 'SHR'))
OPTIMIZE
  FOR 250 ROWS


To me this seems silly because the view is already querying the same file.

Greg


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org> On Behalf Of Birgitta Hauser
Sent: Thursday, May 28, 2026 2:48 AM
To: 'Midrange Systems Technical Discussion' <[email protected]>
Subject: RE: DB View performance

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)


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: MIDRANGE-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org for any subscription related questions.