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