Re: Stored Procedure Speed/Visual Explain

On 10/18/13 11:22 AM, Michael Ryan wrote:

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,
<<SNIP>>
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;

I have very little experience with the effects for using the variable ordering, so I would be tempted to divide the query into seven separate declarations, each with its own specific static ORDER BY clause.... at least to test the effects.

There is also a possibility that recomposing the BETWEEN predicates to move that selection work into their own separately optimized queries that could be cached, would assist. If those columns had foreign key constraints defined, the optimizer would in effect, implicitly add those selections against the parent file. Without the constraints, the CREATE INDEX for each column can be done separately for the benefit of each subquery. Effectively, but using the same file instead of a parent [I believe SELECT DISTINCT is implied for each subquery, but that could be specified explicitly on each]:

Select * From INVMSTP
Where Imvnda in( select Imvnda from INVMSTP where
Imvnda Between :Xxvnda1 And :Xxvnda2 )
And Immdl in( select Immdl from INVMSTP where
Immdl Between :Xxmdl1 And :Xxmdl2 )
And Impdcd in( select Impdcd from INVMSTP where
Impdcd Between :XxDept1 And :XxDept2 )
And Imminr in( select Imminr from INVMSTP where
Imminr Between :Xxclas1 And :Xxclas2 )
And Imfeat in( select Imfeat from INVMSTP where
Imfeat Between :Xxstyl1 And :Xxstyl2 )
And Imdesc in( select Imdesc from INVMSTP where
Imdesc Between :Xxdesc1 And :Xxdesc2 )
And Imszcl in( select Imszcl from INVMSTP where
Imszcl Between :Xxszcl1 And :Xxszcl2 )
And Imdel in( select Imdel from INVMSTP where
Imdel Between :Xxdel1 And :Xxdel2 )
And Imsys in( select Imsys from INVMSTP where
Imsys Between :Xxset1 And :Xxset2 )
And Imbinl in( select Imbinl from INVMSTP where
Imbinl Between :Xxbinl1 And :Xxbinl2 )
And Imdree In(:FldDrxA,:FldDrxB,:FldDrxD)
And Immrch In(:Fldmrc1,:Fldmrc2,:Fldmrc3,
:Fldmrc4,:Fldmrc5)