RE: sqlrpgle question

" I can imagine the optimizer deciding to rewrite the query to "fetch
first row", but only after it has scanned the field for cardinality. "

Are you sure the optimizer is actually querying the access path to get
the count of distinct values? I thought it was only looking at
statistical information that the system saves for the tables. The
optimizer is going to try and perform its job as quickly as possible and
I would think that data access would slow it down way too much. Mostly
I think the optimizer just guesses and builds its access plan based on
that guess.

"Normally, whenever I see DISTINCT I think of this as a table scan,..."

I see DISTINCT as a grouping method more than as a table scan.


" In this case, where the accessed table is not really used for
anything, I can also see the optimizer realizing that the result set is
completely disconnected from the table, so I suppose I can accept a
forced fetch first row might be applied. I believe you when you say
optimization time is much higher for distinct syntax."

We are in agreement here. All I was trying to point out was that if a
Fetch First 1 Rows only was used instead of a Distinct '1' then
optimizer wouldn't have to try and re-write it.

However, I would have to say that we probably wouldn't see an actual
impact to performance unless this particular program is run across
thousands of jobs every day. And even then, if the data was static the
system wouldn't have to re-optimize the access plan very often.


Chris Hiebert

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of DeLong, Eric
Sent: Wednesday, April 25, 2012 12:39 PM
To: RPG programming on the IBM i / System i
Subject: RE: sqlrpgle question

Chris,

This could be REALLY fast if the field happens to be a primary key in an
access path. The optimizer queries the access path for a count of
distinct key values... Normally, whenever I see DISTINCT I think of
this as a table scan, with a check to see of "this" row already exists
in the result set. Or maybe the optimizer creates a temporary access
path to support the analysis. Either way, not a great performer...

In this case, where the accessed table is not really used for anything,
I can also see the optimizer realizing that the result set is completely
disconnected from the table, so I suppose I can accept a forced fetch
first row might be applied. I believe you when you say optimization
time is much higher for distinct syntax.

-Eric DeLong