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

["Alexei Pytel" <pytel@xxxxxxxxxx>]

I do not think that SQL statement cache makes statements run faster - it
only helps optimizer to skip analysis and optimization phase, which is good
for short-running frequently-repeated SQL statements.
I also think that statement cache is not used for embedded SQL - embedded
SQL does not need it, because access plan is pre-built by precompiler in
most cases.

To make performance measurements reliable and repeatable, it helps to purge
storage pool by CLRPOOL command.
Note that if your file can be used by other jobs on system, it may end up
in any pool - and still be used by your job.

Finally, there is nothing wrong with relying on system object caching,
especially for small frequently used objects. System will try to keep them
in storage and all accesses to them will be a lot faster.

    Alexei Pytel
speaking only for myself




                      Vernon Hamberg
                      <vhamberg@attbi.co        To:       
midrange-l@midrange.com
                      m>                        cc:
                      Sent by:                  Subject:  Re: Retrieving 1, and 
only 1, row in SQL
                      midrange-l-admin@m
                      idrange.com


                      03/15/2002 04:45
                      PM
                      Please respond to
                      midrange-l





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.

_______________________________________________
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.