RE: SQL Question -- RPG400-L

It'd be a little faster.  But the leading % still throws a monkey wrench into 
performance.

I didn't even think about them not really being needed.  Go ahead and take them 
out.

Charles

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx 
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Young
Sent: Friday, March 30, 2007 8:59 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: SQL Question

Would it be better (faster) if I removed the TRIM() from the fields?
I only added to try to speed things up by eimlimating the 
leading & trailing blanks from the fields I was searching.

Thanks,

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 

----- Original Message ----
From: "Wilt, Charles" <WiltC@xxxxxxxxxx>
To: RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
Sent: Friday, March 30, 2007 8:38:32 AM
Subject: 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.

-- 
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.


 
______________________________________________________________
______________________
Bored stiff? Loosen up... 
Download and play hundreds of games for free on Yahoo! Games.
http://games.yahoo.com/games/front
-- 
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.