Re: SQL Question

It's because you are joining two fields together to perform the search...
change it to be an "or" statement instead.

Select * from OEHISHED where TRIM(hhorn1) like('%user data%') or
TRIM(hhorn2) like ('%user data%')
order by Hhorn1.


Michael Schutte
Admin Professional
Big Farm Salads - Hurry in to enjoy the newest farm-fresh taste from Bob
Evans, the "Home of Homestyle."


rpg400-l-bounces@xxxxxxxxxxxx wrote on 03/29/2007 03:43:42 PM:

I have an application that needs to search a physical file based on
the value entered by the user appearing anywhere in either of 2
fields, each of which is 50 bytes.
The SQL that I have set up for this is as follows:
Declare data Cursor for
Select * from OEHISHED where TRIM(hhorn1) || TRIM(hhorn2) like('%
user data%) order by Hhorn1.
The statement works fine, but the file in question has a very large
number of records and the initial OPEN of the cursor takes over 30
seconds.  This is an interactive program.
Running the program in DEBUG mode, I do not get any recommendation
for indexes, just a statement that the optimizer selected arrival mode.
Does anyone have any suggestions for improving the time to open the

cursor?

Once I have opened the cursor, the select takes almost no time.
I am processing a maximum of 70 records per selection, and am using
the feature FOR 70 ROWS on the FETCH.

There is a logical file keyed by HHORN1 with a selection criteria
that HHORN1 must not be blank, but the optimizer rejects this file
due to the static select.
When I remove the select, the optimizer rejects this file due to
performance reasons.
It produces a recommendation that I create an index using '0' of the
leftmost order by fields.

TIA,

Jeff Young
Sr. Programmer Analyst
Dynax Solutions, Inc.
A wholly owned subsidiary of enherent Corp.
IBM -e(logo) server Certified Systems Exper - iSeries Technical
Solutions V5R2
IBM  Certified Specialist- e(logo) server i5Series Technical
Solutions Designer V5R3
IBM  Certified Specialist- e(logo)server i5Series Technical
Solutions Implementer V5R3

____________________________________________________________________________________

Don't pick lemons.
See all the new 2007 cars at Yahoo! Autos.
http://autos.yahoo.com/new_cars.html
--
This is the RPG programming on the AS400 / iSeries (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.