Re: Embedded SQL/Complex Statement/Speed

Just out of curiosity, what is the structure of the file, and how many
records does it contain?

I've been tinkering with some scenarios, but I think everyone could help
you better if we knew the layout and size your talking about.


David L. Mosley, Jr.
Technical Solutions Architect
Dancik International, Ltd.
2000 CentreGreen Way, Suite 250
Cary, NC 27513

www.dancik.com



Michael Ryan <michaelrtr@xxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
03/23/2011 04:16 PM
Please respond to
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>


To
Midrange Systems Technical Discussion <MIDRANGE-l@xxxxxxxxxxxx>, "RPG
programming on the IBM i / System i" <rpg400-l@xxxxxxxxxxxx>
cc

Subject
Embedded SQL/Complex Statement/Speed






Well, here's another. I have an SQL statement that looks like this:

C/Exec SQL
C+ DECLARE C1 CURSOR FOR
C+ Select * From MYFILE
C+ Where Imfld1 Between :HostFld1 and :HostFld2
C+ And Imfld2 Between :HostFld3 and :HostFld4
C+ And Imfld3 Between :HostFld15 and :HostFld16
C+ And Imfld4 Between :HostFld17 and :HostFld18
C+ Order By Case When :DspSort = 1 Then Imfld1 Else NULL End,
C+ Case When :DspSort = 1 Then Imfld2 Else NULL End
C+ For Read Only
C/End-Exec

This is a simple example - some are more complex (as in more
comparisons). Using the Between clause is great with high and low
values, but it can be very slow. How do I attack a problem like this?
How do I know what indexes or views or whatever to make? Do I even
want to do that? I know this is incomplete information, but I have to
start somewhere.

Cross posted since it's RPG but it's SQL in nature.

Thanks!