Re: Stored Procedure Speed/Visual Explain

Michael, will the user be able to key an actual range to search for on one
particular field?

If not, then consider this method...


Where :hostvar1 in (' ', field_from_table)
and :hostvar2 in (' ', field2_from_table)


It's basically a shorthanded "or" statement.


When the host variables are populated, it will try to use an index.

It wouldn't matter what option you choose, as the user changes search
criteria, the access plan will change.


Since you don't know what the user's request will be, It would probably be
best to create an encoded vector index for each selection field. SQL will
decide which ones to use. Also make the statement dynamic.


select = 'Select * from invmstp ';

If Immdl_host <> '';
If Whereclause = '';
whereclause = 'where ';
endif;
whereclause += 'Immdl = ' + Immdl_host;
endif;

// continue building for the remaining fields.










On Mon, Oct 21, 2013 at 1:16 PM, Michael Ryan <michaelrtr@xxxxxxxxx>
wrote:

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.

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