RE: Which of the SYSIBM tables/views show the row count for

Write overhead is real for any index maintenance (including traditional
keyed LFs). Write overhead of data validation for SQL tables is real as
well, as is read improvement since data validation is moved out of there.

Rob's sample is totally write-biased so results jump out at you.

There's always a trade off, so we're back to informed decisions.
Are added data integrity and potential query and read performance
improvements worth it?
My experience is that most applications fall in that 25-1 read to write
ratio, so I'd reason that for most application it's worth it.

As usual, there is only one right answer - "It depends".

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Re: Which of the SYSIBM tables/views show the row count for

rob@xxxxxxxxx wrote:

However, if I add this "standard" logical
R IIMJOER PFILE(IIMJOE)
IPROD 25A
K IPROD
Against that same physical file then you will see the same times.
12.199
13.313
12.277

So, why not just have the UNIQUE key on the physical?

My response to this would be to put the primary key on the physical file
where it belongs.

And I'm not sure why this causes such a change. In fact, this is a real
problem as far as I'm concerned. It's one of the few places where a
DDL-defined file seriously underperforms a DDS-defined file. Even
creating a non-unique index using CREATE INDEX still causes the very
nasty 7-to-1 perform decrease.

It seems that the logic that allows a physical file to be keyed is much
faster than a separate index object (i.e. logical file), and that SQL
always uses a separate index object. That's a painful reality. I
normally recommend DDL files over DDS files because up until now there
was no reason not to. However, it seems that you're stuck with that
performance hit in DDL.

Ouch. Score a big one for DDS.

Joe