RE: SQL Question

Jeff,

You're going to have problems with this one due to the use of TRIM() and the 
leading % on the LIKE.

Basically, for an index to be used, you can't perform any operations on the 
table columns in the
where.  In addition, when using LIKE, you can't have a leading %.

Here's what I'd do:

Create an index that contains just the file's unique key field(s) and the 
hhorn1 and hhorn2 fields.

Then for your SQL do the following:

Select * from OEHISHED
Where myKey in (select mykey
                from OEHISHED
                where TRIM(hhorn1) || TRIM(hhorn2) like('%user data%)
                )
Order by hhorm1


Note that assuming %user data% wouldn't be spread across hhorn1 and hhorn2, 
then as suggested by
sombody else use 
  WEHER TRIM(hhorn1) like('%user data%) or TRIM(hhorn2) like('%user data%)


Now, since we're still using TRIM() and a leading %, the optimizer will have to 
do a full table scan.
But since the only fields we need for the inner select are on the index we 
created, the optimizer
_should_ just scan the index.  Assuming the size of index fields are 
significantly smaller than the
whole record, then the scan of the index should be significantly faster than 
the scan of the file has
been.

Lastly, if this file is defined with DDS, consider defining it with SQL DDL 
instead.  You'll get
faster read performance and you can usually do so in such a way that you don't 
even have to recompile
any programs.

HTH,
Charles

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx 
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Young
Sent: Thursday, March 29, 2007 3:44 PM
To: midrange-l@xxxxxxxxxxxx; rpg400-l@xxxxxxxxxxxx
Subject: SQL Question

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.

This e-mail transmission contains information that is intended to be 
confidential and privileged.  If you receive this e-mail and you are not a 
named addressee you are hereby notified that you are not authorized to read, 
print, retain, copy or disseminate this communication without the consent of 
the sender and that doing so is prohibited and may be unlawful.  Please reply 
to the message immediately by informing the sender that the message was 
misdirected.  After replying, please delete and otherwise erase it and any 
attachments from your computer system.  Your assistance in correcting this 
error is appreciated.