Re: AW: SQL Question

Jeff
Birgitta, as usual, always supplies information that makes me shake my head
in amazement as to how the @!# does she know this stuff.
There is one other thing that you may not have considered with your SQL
statement
The individual fields, by themselves may NOT contain the user data, BUT the
concatenated result may. I don't think this is what you are after.



Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill

midrange-l-bounces@xxxxxxxxxxxx wrote on 03/30/2007 06:46:47 AM:

Hi Jeff,

May be you'll be disapointed, but if your complete select statement looks

as

you showed (with the like predicate as single WHERE clause), always a

table

scan will be performed, no way around.

The optimizer cannot use any access path (neither a SQL index nor a DDS
described logical file, because:
1. The optimizer can only use access paths if the original fields are
specified on
   the left hand of the comparison sign or predicate,
   i.e. adding TRIM and concating two fields modifies the original fields

   and an access plan cannot be used. (Trim(HHORN1) concat Trim(HHORN2)

like

'%user data%').
2. Using the percent sign in the first position after the like ('%user

...)

   will also prevent the optimizer to use an index.

Because of the Order By clause an additional sort of the result is
necessary. Adding Fetch first x rows only also will not speed up the
execution, because first ALL rows must be found, a temporary object built
and the result sorted. After the first 70 rows will be returned.

If, according to your statement, you could use the DDS described logical
file where HHORN1 must not be blank, you may change your SQL-Statement as
follows:
Select *
  From OEHISHED
  Where HHORN1 > ' ' and (HHORN1 like '%user data%' or HHORN2 like '%user
data%')
  Order By HHORN1

Writing the SQL-Statement in this way, the CQE-Optimizer may use the

access

path in the DDS described logical file.
(SQE optimizer will not be used due to the fact that DDS described

logical

files with select/omit clauses are defined over the base table)

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them?  Not training

them

and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jeff Young
Gesendet: Thursday, March 29, 2007 21:44
An: midrange-l@xxxxxxxxxxxx; rpg400-l@xxxxxxxxxxxx
Betreff: 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 Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,

unsubscribe,

or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.





--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.