Re: 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!
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.