RE: Hash Table Probe

[Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>]

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.