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

[Vernon Hamberg <vhamberg@xxxxxxxxx>]

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.