Re: Stored Procedure Speed/Visual Explain

That's what I'm thinking....build the statement (dynamic). It seems I would
have a lot of indexes - 10 based on the 'between' criteria. This selection
logic is used a lot - for subfile load on a green screen and for exposing a
result set to PHP. If I was to make this dynamic (which I believe means
creating the SQL statement by building a string and executing it), would I
have issues because the access plan will be rebuilt every execution of the
program?

Thanks...

- Michael


On Mon, Oct 21, 2013 at 1:04 PM, Michael Schutte <mschutte369@xxxxxxxxx>wrote:

Yeah, probably so. However, the OP isn't doing any joins. I was thinking
that he was. The problem that he's having is because he's trying to do a
range with all values. I tried that once and found that I was always
performing a table scan whenever my range was from loval to hival.

If you are doing that kind of range, it will never pick up the index you
want it to use.

I usually go one of two ways. Go dynamic or instead of using the range, I
would use the IN clause.


By either creating a temporary table of valid values. (insert into
qtemp/table select distinct Immdl from invmstp) This would obviously need
an index over Immdl.
Then
where Immdl in (select Immdl from qtemp/table).

Or if something was selected,
I would do
Where :Hostvar in (' ', Immdl ).

When hostvar was blank, all records would be returned.


Now that I think about it. I'm wondering about creating views and indexes
over each of those views.
create view invmstp_Immdl_view on invmstp as ( select distinct Immdl from
invmstp)

create encoded vector index invmstl on invmstp (Immdl);

select * from invmstp
where Immdl in (select Immdl from invmstp_immdl_view where :hostvar in ('
', Immdl )


I don't just a thought.





On Mon, Oct 21, 2013 at 9:27 AM, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx

wrote:

I'm generally in agreement with you, Michael - I've found over-complex
JOINs to perform somewhat badly at times.

Maybe the OP could use a global temporary table or 2 in his stored
procedure - could be used for a chain of SELECTs, right?

Vern

On 10/21/2013 7:35 AM, Michael Schutte wrote:

I don't doubt this. My experience though has been the opposite.


On Fri, Oct 18, 2013 at 2:04 PM, Vernon Hamberg <

vhamberg@xxxxxxxxxxxxxxx>wrote:

Was just at RPG-DB2 Summit - we are encouraged to put as much into a
single SELECT statement as we can, by the IBMer, Tom McKinley, who

did a

presentation on set-based thinking with SQL.

Tom specifically said to avoid multiple steps of putting data into
separate tables in QTEMP. instead, look for ways to combine as JOINs

or

maybe as IN predicates against a subquery.

If the OPs stored procedure could do what the RPG program does - and

it

probably can - then everything could be put into the SP. SPs have lots
of programming capability, and some of it is easier than doing RPG!

Vern

On 10/18/2013 12:02 PM, Michael Schutte wrote:

Sometimes it's better to split the SQL statements up and return

multiple

result sets. then let the business logic take care of the rest.

Other

times, I have selected what I've needed from each file and put them

into

QTEMP. Then returned the result set of the QTEMP table.

declare global temporary table tmp_mytable like mytable with replace;
declare global temporary table tmp_mytable2 like mytable2 with

replace;

insert into tmp_mytable select * from mytable where field = 1;


insert into tmp_mytable2 select * from mytable2 where field = 'abc';


select * from tmp_mytable a join tmp_mytable2 b on (a.otherfield =
b.otherfield);

etc.





On Thu, Oct 17, 2013 at 7:56 PM, CRPence <CRPbottle@xxxxxxxxx>

wrote:

Could gather and review the PRTSQLINF against the SQL RPG

program.

Could also try to get debug messages in a job that invokes the

SQLRPG.

Regards, Chuck

On 10/17/13 12:17 PM, Michael Ryan wrote:

No, it's definitely SQL. Big honking SQL statement in there.

On Thu, Oct 17, 2013 at 2:40 PM,<rob@xxxxxxxxx> wrote:

Maybe it doesn't need/use any?

You have it call an RPG program. Perhaps your RPG program does
RLA instead of SQL <<SNIP>>

Michael Ryan on 10/17/2013 02:31 PM wrote:

Visual Explain/index Advisor woes. I have a stored procedure that
calls an RPG procedure that returns a result set. <<SNIP>>

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

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