Re: Stored Procedure Speed/Visual Explain

Since he has an SP he is calling out from to RPG, your suggestion, Dan, is like what I tried to say - put it all in the SP.

He might need to declare some host variables in the SP, but the nice thing is, you don't need the colons for host variables in SPs - very nice!

Vern

On 10/18/2013 4:50 PM, Dan Kimmel wrote:

To really make it fly, build an index over each of the individual fields mentioned in the Where and Order By clauses. Use an SQL stored procedure and eliminate the cursor. Index Advisor will tell you which of the fields you use most often; build those indexes first.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Michael Ryan
Sent: Friday, October 18, 2013 1:23 PM
To: Midrange Systems Technical Discussion
Subject: Re: Stored Procedure Speed/Visual Explain

So I have this in a service program procedure which is called by a stored
procedure. I set the 'between' values to *LOVAL and *HIVAL, and then plug
in one (lower limit or only value) or two (upper limit) for each of the
values. I then fetch until I run out of records that fit the criteria. I
know it's ugly, but it encompasses all the different search criteria. What
can I do to make this more efficient? Create indexes over the
first/second/more values? Dynamically build the statement and don't include
columns that aren't used?

Exec SQL
DECLARE C1 CURSOR FOR
Select * From INVMSTP
Where Imvnda Between :Xxvnda1 And :Xxvnda2 And
Immdl Between :Xxmdl1 And :Xxmdl2 And
Impdcd Between :XxDept1 And :XxDept2 And
Imminr Between :Xxclas1 And :Xxclas2 And
Imfeat Between :Xxstyl1 And :Xxstyl2 And
Imdesc Between :Xxdesc1 And :Xxdesc2 And
Imszcl Between :Xxszcl1 And :Xxszcl2 And
Imdel Between :Xxdel1 And :Xxdel2 And
Imsys Between :Xxset1 And :Xxset2 And
Imbinl Between :Xxbinl1 And :Xxbinl2 And
Imdree In(:FldDrxA,:FldDrxB,:FldDrxD) And
Immrch In(:Fldmrc1,:Fldmrc2,:Fldmrc3,
:Fldmrc4,:Fldmrc5)
Order By Case When :DspSort = 1 Then Imvnda Else NULL End,
Case When :DspSort = 1 Then Immdl Else NULL End,
Case When :DspSort = 2 Then Immdl Else NULL End,
Case When :DspSort = 2 Then Imvnda Else NULL End,
Case When :DspSort = 3 Then Impdcd Else NULL End,
Case When :DspSort = 3 Then Imminr Else NULL End,
Case When :DspSort = 3 Then Imfeat Else NULL End,
Case When :DspSort = 3 Then Imvnda Else NULL End,
Case When :DspSort = 3 Then Immdl Else NULL End,
Case When :DspSort = 4 Then Imminr Else NULL End,
Case When :DspSort = 4 Then Imfeat Else NULL End,
Case When :DspSort = 4 Then Impdcd Else NULL End,
Case When :DspSort = 4 Then Imvnda Else NULL End,
Case When :DspSort = 4 Then Immdl Else NULL End,
Case When :DspSort = 5 Then Imfeat Else NULL End,
Case When :DspSort = 5 Then Imminr Else NULL End,
Case When :DspSort = 5 Then Impdcd Else NULL End,
Case When :DspSort = 5 Then Imvnda Else NULL End,
Case When :DspSort = 5 Then Immdl Else NULL End,
Case When :DspSort = 6 Then Imdesc Else NULL End,
Case When :DspSort = 6 Then Imvnda Else NULL End,
Case When :DspSort = 6 Then Immdl Else NULL End,
Case When :DspSort = 7 Then Imszcl Else NULL End,
Case When :DspSort = 7 Then Imvnda Else NULL End,
Case When :DspSort = 7 Then Immdl Else NULL End
For Read Only;


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?