Re: Hash Table Probe

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

I think a fetch loop is using a cursor DECLARE CURSOR - OPEN CURSOR - 
FETCH while SQLCOD > 0 and <> 100

Regards
Vern

On 6/17/2020 4:03 PM, Greg Wilburn wrote:
> 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.