Re: Hash Table Probe -- MIDRANGE-L

Greg,

Thanks for your feedback. My company generates and builds broadly scoped
full featured business applications. We deploy them in our web portal,
using a modular architecture. But once in a while, I like to create little
apps that demonstrate a particular function, such as the scrolling list in
that example. "Entities" was meant to be generic. The records are actually
"locations" that one of our Utah school districts bus students to.

Other than an occasional hourglass, we don't use animation to show that an
app is "processing", but rather just provide snappy performance.

Nathan

On Tue, Jun 23, 2020 at 6:51 AM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

Nathan,

This particular app was created using Nitro App Builder by CNX Corp. Very
little (if any) coding is required. Like your examples it uses SQLRPGLE on
the backend and HTML/Javascript for the front.

Your examples below are quite interesting. I noticed a lot of UT
addresses in your sample data, and that you referred to them as
"Entities". Our ERP was written by a company in UT and they refer to
Customers and Vendors as "Entities". Made me go hmm.

Greg

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf
Of Nathan Andelin
Sent: Friday, June 19, 2020 12:57 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Hash Table Probe

Greg,

Since you're showing a "paging grid", you might want to take a look at this
example and use your mouse wheel to navigate page-to-page:

http://rd.radile.com/rdweb/info3/iui15/subfile.html

The code behind the app is explained here:

http://rd.radile.com/rdweb/info2/ibmiui15.html

In the RPG code, see that the variable "row_count" is incremented as rows
are "read" from the SQL result set. Although you seem to be okay with 800
milliseconds response time, I think you could do much better.

On Fri, Jun 19, 2020 at 9:47 AM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

I thank you all for your responses... I have learned quite a bit from you
and from the IBM DB group. I finally conquered this issue. We're down

to

sub-second response time.

Birgitta is 100% correct about having to know the SQL statement behind

the

views... It's actually a view with joins to several other views. So I

know

my question was pretty vague.

FYI - the application displays a "paging grid" showing up to 200 rows per
page. However, it also displays the total number of rows available based
on the filter. I'm guessing that's why it does two selects (one to count
all rows and one to select the rows you're looking at). Anyway...

In case this could help anyone else with this type if performance issue,
here is what I learned.

1. Index Advisor is a 5 or 6 out of 10 when it comes to creating
indexes. In my case, the "stupid human" was better. At IBM's

suggestion,

I created a view that matched exactly to the join to the large table.
Before I could refresh the screen to see my index, it was already used 4
times.

2. Avoid sort or join on columns created by a CAST. My table contains a
numeric time (6:0). I CAST this to character "HH:MM:SS" and was sorting

by

this ON A JOIN (eek). I added the numeric time field to my view and
sorted/joined using that field while displaying the "pretty" time.

3. Use OPTIMIZE FOR nn ROWS to force the I/O type

4. Run SQL Scripts uses *FIRSTIO which is very quick when retrieving a
few rows (versus *ALLIO which is better for a large group)

The combination of using OPTIMIZE, tweaking my views, and creating MY OWN
indexes to match my join statements took response time from 24 seconds to
800 milliseconds.

Thanks again! You all are the greatest.

Greg

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On

Behalf

Of Birgitta Hauser
Sent: Thursday, June 18, 2020 1:10 AM
To: 'Midrange Systems Technical Discussion' <

midrange-l@xxxxxxxxxxxxxxxxxx

Subject: RE: Hash Table Probe

IMHO without knowing the SQL Statement behind the view it is very hard to
suggest anything.
It could be that an Encoded Vector Index with an Include Clause may help

to

speed up the query or any other index.

The select is faster, because it does not run the complete query, but
returns only the first block of data.
To get the complete number of rows the complete query have to be run.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"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)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of

Greg

Wilburn
Sent: Mittwoch, 17. Juni 2020 23:03
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx

Subject: RE: Hash Table Probe

Nathan...

I don't have that kind of control (I also don't know what you mean by a
"fetch loop"). I am an RPG programmer that has learned SQL as went

along.

The application I'm using runs two separate SQL statements.

1. Select count(*) from my_sql_view where...
Then
2. Select field1, field2, field3, etc. from my_sql_view where...

The file and where clause are always the same.
The app creates a grid in the browser and displays a row count at the
bottom.
The app has filters that likely add to the SQL statement.

I'm just trying to get the "count" statement to not take 6x as long as

the

select rows statement.
But I can't really change the fundamental way the software works... it

runs

two separate statements.

Thx,
Greg

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On

Behalf

Of
Nathan Andelin
Sent: Wednesday, June 17, 2020 2:17 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx

Subject: Re: Hash Table Probe

Greg,

Are we to understand that you're running two select statements? One that
generates a result set? Another that returns a count of 20-40?

Since the count is so low, it might be quicker to count the rows yourself
during a "fetch" loop as opposed to running "select count(*) ..."

On Wed, Jun 17, 2020 at 12:06 PM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

Sorry... I should have mentioned that I've already been through some
of these explanations.

It's returning a count of 20-40 (that's how many rows meet the
criteria)... The Select is using OPTIMIZE FOR 250 ROWS (as suggested
by the IBM DB group)

The SQL statements are executing within a CGI application... so I can
only really control the source (view) and the indexes.

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

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://amazon.midrange.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.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://amazon.midrange.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.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.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.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.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.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.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.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com