Options for Complex (slow) data source

[gwilburn]

I have created an SQL statement containing a CTE. While I can get the desired result in Run SQL Scripts (ACS), it consistently takes 22 seconds to build. I'm not sure if creating an SQL view and/or suggested indexes will increase performance dramatically.

In that case, what options do I have?

I thought about having a pre-execution program that rebuilds an "output file" based on how "old" the data is (i.e. the last time it was updated). If the data is relatively new, it doesn't rebuild the file.

I could also have a button to force a rebuild and reload.

This would be replacing an old printed report that we ran from the job scheduler.

Other ideas?

[robert.swanson]

I can't think of any other approaches beyond what you've suggested here. Ideally you don't want users to wait more than a few seconds for data to come back. So if creating indexes doesn't achieve the performance gains you're hoping for, having the logic just add additional records (or update existing records) in a work table on-demand, based on the user filter, may be your best option. This is typically handled through an exit program, either on the data source or, more commonly, on the grid filters.

[gwilburn]

I have a case open with IBM. I built an index that, when used, has the response time below 3 seconds.
The problem is that it doesn't always use the index (according to ACS visual explain). sometimes it uses the index, other times it builds a temporary hash table.

ugh.

[gwilburn]

OK... So with indexes I got both data sources running quickly in Run SQL Scripts (about 1.5 seconds).

However, the NAB data sources are slow enough they're being cached.

When I try them in a chart or grid, they never populate.

[sean.lanktree]

They never populate in NAB or when using it within an application?

[gwilburn]

That's right... I have both the chart and the grid in an application. I see the "loading circles" but they eventually stop and they're empty

[sean.lanktree]

Sounds like it is timing out. You may want to increase your "Data source timeout" setting. You can find this in Portal Admin > Settings > Nitro App Builder.

[sean.lanktree]

I should mention, the cached data is only used in NAB when configuring your widget. Otherwise your application would be using the wrong and/or old data.

[sean.lanktree]

Not sure if this is related...but did you try changing your "Sort Sequence" to *HEX on your data source?

[gwilburn]

Already had sort sequence set to *HEX
Increased the data source timeout to 60 seconds.

It loads now.. BUT, it still takes quite a bit of time compare to Run SQL Scripts (1.5 seconds each)

I believe I've seen this before...

[robert.swanson]

Can you confirm the SQL being executed in your data source exactly matches what you're running in Run SQL Scripts? You can see the statement being executed by turning logging in Portal Admin > Settings > Hidden Settings > "Log all SQL statements executed from Nitro App data sources". The statements will be written along with a timestamp to file VVGENLOG.

If you don't see a "Hidden Settings" section, you'll first need to make that visible in Portal Admin > Settings > Portal Administration > "Show hidden settings".

[gwilburn]

This is from Run SQL Scripts:

Select whs_nbr,
sum (case when BIN = 'TRK' then EST_MOVES end) as TRK_MOVES,
sum (case when BIN <> 'TRK' then EST_MOVES end) as PRIMARY_MOVES
from v_replenishment_by_loc
group by whs_nbr;

Statement ran successfully, with warnings (1,271 ms = 1.271 sec)

This is from VVGENLOG

2021-12-14 13:30:37.695000
375913
GWILBURN
SELECT whs_nbr , sum(case WHEN bin = 'TRK' THEN est_moves END) AS trk_moves, sum(case WHEN bin <> 'TRK' THEN est_moves END) AS primary_moves FROM v_replenishment_by_loc GROUP BY whs_nbr
19503
2021-12-14 13:30:17.145000
375919
GWILBURN select SUM(EST_MOVES) AS EST_MOVES FROM v_replenishment_by_loc
20611

I'm not sure why there are multiple entries - maybe for total?
Regardless you can see the time difference.

This was a third one i missed
2021-12-14 13:30:17.126000
375913
GWILBURN
COUNT statement: select count(1) from (SELECT whs_nbr , sum(case WHEN bin = 'TRK' THEN est_moves END) AS trk_moves, sum(case WHEN bin <> 'TRK' THEN est_moves END) AS primary_moves FROM v_replenishment_by_loc GROUP BY whs_nbr) temp
20568

[robert.swanson]

Well the first SQL statement from VVGENLOG certainly looks identical to your Run SQL Scripts statement. I'm not sure what to make of that second statement in VVGENLOG. It looks like the third statement is to retrieve the total number of records for the grid paging.

I think it's most interesting that the simple second statement, whatever's triggering it, takes 20.6 seconds. I presume if you ran that same simple statement in Run SQL Scripts it completes much faster?

Perhaps you can do a STRSRVJOB on the two most active CGI jobs in that instance (you'd need to do it in two separate 5250 sessions), and then do a STRDBG in each session. This will make the jobs throw SQL debug data into the job log. Perhaps it'll show that it's not using the expected index for some reason. In which case it might require some feedback from IBM as to why the SQL engine is behaving differently.

For shits and giggles it might also be interesting to see if you could get better performance using CTEs to get the results you're looking for, something like this:

with TRK as
      (select whs_nbr, sum(est_moves) as moves
         from v_replenishment_by_loc
        where bin='TRK'
        group by whs_nbr),
NONTRK as
      (select whs_nbr, sum(est_moves) as moves
         from v_replenishment_by_loc
        where bin<>'TRK'
        group by whs_nbr),
WHSES as (select distinct whs_nbr as whs_nbr
            from v_replenishment_by_loc)
select WHSES.whs_nbr as whs_nbr,
       coalesce(TRK.moves,0) as trk_mv,
       coalesce(NONTRK.moves,0) as nontrk_mv
  from WHSES
       left join TRK on TRK.whs_nbr=WHSES.whs_nbr
       left join NONTRK on NONTRK.whs_nbr=WHSES.whs_nbr
 order by WHSES.whs_nbr

[robert.swanson]

One more thing I just thought of... Are you on a fairly recent 6.0 build? If not, I'd also recommend downloading the latest. I believe one of the 6.0 updates released back in July included some improvements to the VVOUT procedures that might apply here.

[gwilburn]

I'll try updating...

The CTE was horrible - over one minute in RSS (run sql scripts)