Re: Stored Procedure Speed/Visual Explain

Thanks for the info Rob.


On Thu, Oct 17, 2013 at 3:26 PM, <rob@xxxxxxxxx> wrote:

Actually I think the index advisor is getting a lot better at accepting
select/omit logicals.
In fact, at 6.1 there was some "ignorederivedindex" setting or some such
thing for this.
At 7.1 "At V7R1 of the operating system, the SQE engine can process
queries and take advantage of your DDS-LFs that contain Select/Omit
criteria."
http://www-01.ibm.com/support/docview.wss?uid=nas8N1018857

I just googled +select +omit +logical +file +sqe


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Michael Ryan <michaelrtr@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>,
Date: 10/17/2013 03:20 PM
Subject: Re: Stored Procedure Speed/Visual Explain
Sent by: midrange-l-bounces@xxxxxxxxxxxx



Thanks Gary...I'll do that next. I think some/most of the logicals may
have
select/omit criteria, and I think that disallows them from consideration.
Right?


On Thu, Oct 17, 2013 at 2:50 PM, Gary Thompson
<gthompson@xxxxxxxxxxx>wrote:

Michael,
If nothing from the Advisor, have you at least
checked all Where clauses in your SP for a match
or near match to existing index/LF ?

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Ryan
Sent: Thursday, October 17, 2013 12:31 PM
To: Midrange Systems Technical Discussion
Subject: Stored Procedure Speed/Visual Explain

Visual Explain/index Advisor woes. I have a stored procedure that calls

an

RPG procedure that returns a result set. All is well. But it's slow. So

I

take the stored procedure call, put it into Navigator->Run SQL Scripts,
highlight the statement, click Visual Explain, get a chart of objects

used

in the query, click Actions->Advisor, and no indexes are suggested. This
query can take a long time to run - 50+ seconds first time, 30+ seconds
after that.

How can I get more information regarding the indexes used or needed?

Thanks!
--
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.

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