Re: Hash Table Probe

Greg, your original post compares the performance of "count" vs. "select",
and suggests that the performance should be comparable. But what if "count"
is doing a lot more, under the covers? What if IBM and their SQL engine
didn't offer "count" as an option. Wouldn't you fall back to reading the
result set row-by-row, top-to-bottom in order to derive a count?

If you're running SQL in "server" mode, the QSQSRVR Job may be returning
quickly from the SQL select statement, not actually having created the
entire result set. But the moment you add "count" to the select, it
actually has to generate the complete result set and count the rows?



On Wed, Jun 17, 2020 at 9:48 AM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

I don't know what you mean?

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf
Of Nathan Andelin
Sent: Wednesday, June 17, 2020 11:29 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Hash Table Probe

Try comparing the performance of select + fetch all rows to count. I
suspect count would be quicker.

Nathan Andelin

On Jun 17, 2020, at 9:10 AM, Greg Wilburn <

gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

I have and SQL COUNT statement running over a complex SQL View... The

SELECT statement of the same rows takes 1-2 seconds. The COUNT(*) takes
6-8 seconds.

When I ran this through RUN SQL SCRIPTS using run & explain, I see:
Hash Probe <- Temporary Hash Table <- Table Probe

The Table Probe is 90% of the time used. I'm just not sure how to

eliminate or reduce the amount of CPU time this takes.

I already have quite a few indexes over the DB2 table... I can see that

in the "List of Indexes Optimized"

Anyone point me in the right direction?
--
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