AW: Embedded SQL/Index/Speed/How

["Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>]

Access Plans for all SQL Statements executed by the SQE independent if
tables or views are used get stored within the SQE Plan Cache.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Michael Ryan
Gesendet: Wednesday, 23. March 2011 18:50
An: Midrange Systems Technical Discussion
Betreff: Re: Embedded SQL/Index/Speed/How

Birgitta - does the cached access plan also include views? Thanks...

On Wed, Mar 23, 2011 at 12:59 PM, Birgitta Hauser
<Hauser@xxxxxxxxxxxxxxx> wrote:
> If the query was executed by the SQE (SQL Query Engine).
> The access plan is stored within the SQE Plan Cache.
> Access paths (indexes) that are used within access plans stored within the
> SQE Plan Cache are not deleted before the last access plan that uses this
> index disappears from the SQE Plan cache.
> BTW the SQE Plan cache will be cleared with IPL.
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
>
> -----Ursprüngliche Nachricht-----
> Von: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Michael Ryan
> Gesendet: Wednesday, 23. March 2011 17:32
> An: Midrange Systems Technical Discussion
> Betreff: Embedded SQL/Index/Speed/How
>
> Ok...a co-worker had a program with an embedded SQL statement,
> selecting records with a number of different criteria. It ran dog
> slow. I suggested creating an index to remove some of the 'fixed'
> criteria. Did that, recreated the program, ran like the wind. Then we
> decided to test things out. Deleted the index. Still ran fast. Used an
> old copy of the program that was created before the index was made.
> Still ran fast. Deleted the index and the program, recreated the
> program. Still ran fast.
>
> What happened? Was an access plan created and stored someplace? I
> would think deleting the program and index and recreating the program
> would remove any consideration of an access path.
>
> Thanks for any thoughts.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> 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@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.