Re: Retrieving 1, and only 1, row in SQL

[Vernon Hamberg <vhamberg@xxxxxxxxx>]

This has implications for testing performance. In order to test runs after
SQL resources are loaded, you want to run a statement at least 3 times. And
if you want to keep things cold, you want to modify the statement slightly,
so that the cache sees it as a different statement. Just adding a space
does this, so it's not found, although the parser actually ignores this. Go
figure.

At 04:39 PM 3/15/02 -0600, you wrote:
>There's a job-level thing called the prepared statement area. That goes
>away with the job. There's also a system statement cache. That gets cleared
>at IPL.
>
>Also, if the files are still in memory, things will go faster. And if other
>jobs use the same files, or statements.
>
>Verrrrry eenteresting!
>
>At 04:56 PM 3/15/02 -0500, you wrote:
>
>>And that statement caching stays active even when I sign off and sign back
>>on?  That's the part I find interesting.
>>
>>Rob Berendt
>>--
>>"They that can give up essential liberty to obtain a little temporary
>>safety deserve neither liberty nor safety."
>>Benjamin Franklin
>>
>>
>>
>>                     Vernon Hamberg
>>                     <vhamberg@attbi.com       To:
>> midrange-l@midrange.com
>>                     >                         cc:
>>                     Sent by:                  Fax to:
>>                     midrange-l-admin@mi       Subject:     Re: Retrieving
>>1, and only 1, row in SQL
>>                     drange.com
>>
>>
>>                     03/15/2002 04:41 PM
>>                     Please respond to
>>                     midrange-l
>>
>>
>>
>>
>>
>>
>>If I remember, the 3rd run of the same statement results in some kind of
>>statement caching. Besides, the SQL resources are all in place.
>>
>>At 04:41 PM 3/15/02 -0500, you wrote:
>>
>> >Normally I would agree to running the debugger.  But my question is this:
>> >If I do see a performance suggestion, won't it be something like 'create
>>an
>> >index-CPI432F'?  And I am not going to do that on a IBM supplied file.
>> >But I ran the debugger.
>> >
>> >select dbname
>> >from qsys2/syscolumns
>> >where dbname='DATDIVF'
>> >fetch first 1 row only
>> >Time=2 sec - not bad
>> >CPI432C - QSYS/QADBILLB  0
>> >CPI432F - DBIREL, DBILB2, DBIATR
>> >
>> >select count(*) from qsys2/syscolumns
>> >where dbname='DATDIVF'
>> >  and TBNAME between 'AAAAAAAAAA' and 'ZZZZZZZZZZ'
>> >  and NAME between  'AAAAAAAAAA' and 'ZZZZZZZZZZ'
>> >Time=55 sec
>> >CPI432C - QSYS/QADBILLB  0
>> >CPI432F - DBIREL, DBILB2, DBIATR, DBILFI, DBILFL
>> >
>> >I'll probably either bust this down into 2 sql's or 1 api call and 1 sql.
>> >
>> >Now for the weird part.  I ran several consecutive time trials.  I would
>> >signoff, signon, strdbg, strsql, and run the above statement.  After a
>> >couple of runs it got to be pretty fast.  Does it store a temporary access
>> >path that it uses, and does this path actually span jobs?
>> >
>> >
>> >Rob Berendt
>> >--
>> >"They that can give up essential liberty to obtain a little temporary
>> >safety deserve neither liberty nor safety."
>> >Benjamin Franklin
>> >
>> >_______________________________________________
>> >This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
>>list
>> >To post a message email: MIDRANGE-L@midrange.com
>> >To subscribe, unsubscribe, or change list options,
>> >visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
>> >or email: MIDRANGE-L-request@midrange.com
>> >Before posting, please take a moment to review the archives
>> >at http://archive.midrange.com/midrange-l.
>>
>>_______________________________________________
>>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
>>To post a message email: MIDRANGE-L@midrange.com
>>To subscribe, unsubscribe, or change list options,
>>visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
>>or email: MIDRANGE-L-request@midrange.com
>>Before posting, please take a moment to review the archives
>>at http://archive.midrange.com/midrange-l.
>>
>>
>>
>>
>>
>>_______________________________________________
>>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
>>To post a message email: MIDRANGE-L@midrange.com
>>To subscribe, unsubscribe, or change list options,
>>visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
>>or email: MIDRANGE-L-request@midrange.com
>>Before posting, please take a moment to review the archives
>>at http://archive.midrange.com/midrange-l.
>
>_______________________________________________
>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
>To post a message email: MIDRANGE-L@midrange.com
>To subscribe, unsubscribe, or change list options,
>visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
>or email: MIDRANGE-L-request@midrange.com
>Before posting, please take a moment to review the archives
>at http://archive.midrange.com/midrange-l.