RE: sql performance question

That is one gotcha with interactive SQL (STRSQL). It defaults to *FIRSTIO
setting which in turn defaults to biasing query optimizer toward populating
the first screen as fast as possible. This is normally a good thing, as
query optimizer will then be biased toward using an index.

One way to get a more accurate comparison between the two statement's total
runtimes is to force their output to a file and compare the time to get ALL
of the rows out.
You'd effect this via F13 option in STRSQL and changing the Output to 3
(File).

In general, STRSQL is not a good way to test performance metrics. Doing it
in a program repetitively will give you a better taste of the actual
runtimes you can expect.

Elvis

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


-----Original Message-----
Subject: Re: sql performance question

Brad,

On Tue, Mar 18, 2008 at 3:57 PM, Bradley V. Stone <bvstone@xxxxxxxxxxxx>
wrote:

did you try changing the order? Possibly the second time it's reusing a

DP.

It didn't matter what order I ran them in. I could run both, one
right after the other, several times in a row, the select * always ran
faster than the other - at least - and this may mean something - it
displayed the first page worth of records faster.

I did not test paging through the file to see which one paused longer
between pages, so that may be the issue - it waited till after
gathering more records on the select fld,fld,fld than the * ?

...testing one last time, I ran the first one and paged down 10 times
without regard to response, and did the same thing with the second
one.

It was relatively the same amount of time for both to finally display
the 10th page of data, so that must be the deal - the first one just
displayed the first page quicker than the second one, while the second
one waited till several pages had loaded before displaying the first
page -

just guessing off the top of my head, maybe they were both probably
filling a fixed buffer size of data before displaying - the select *
filled that buffer more quickly than the select fields?