RE: JDBC and SQL Performance Under V5R3

Nigel,

Have you tried using:

where fieldname between 'value' and 'VALUE' 

to handle the case insensitive search?


Now why exactly to you need LTRIM and RTRIM?  Do you actually have a field
with '    some data    '?

I never had a problem with trailing blanks, but I could see where the
leading blanks would be a problem.

Charles Wilt
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: NGay@xxxxxxxxxxxxx [mailto:NGay@xxxxxxxxxxxxx]
> Sent: Wednesday, November 03, 2004 10:50 AM
> To: java400-l@xxxxxxxxxxxx
> Subject: Re: JDBC and SQL Performance Under V5R3
> 
> 
> 
> Ed,
> 
> I don't know, and I'd be extremely interested to hear 
> anyone's experience
> on the matter as well.  We have very similar problems, 
> currently at V5R2.
> The website currently does some horrid queries, even 'equals' 
> searches must
> be space and case insensitive, so do "WHERE
> LTRIM(RTRIM(UCASE(FIELDNAME)))='VALUE'" and as such make 
> terrible (or no)
> use of indexes/logical access paths.  We're looking to get 
> around this by
> duplicating the field in the table, once in its original form 
> and once as
> an already trimmed and upper cased copy of the field.  Then 
> we can build an
> index/logical access path over the trimmed and upper cased copy of the
> field and the queries simply becomes "WHERE NEWFIELD='VALUE'" - the
> performance improvement from doing this is enormous, anything 
> from 4x to
> 200x on some queries.
> 
> The LIKE keyword is a bit more tricky, from what I can tell 
> if you do a
> 'starts with' type query (such as WHERE FIELDNAME LIKE 
> 'XYZ%') the database
> will be able to make efficient use of an index sorted on that 
> field, but if
> you do a 'contains' type query (such as WHERE FIELDNAME LIKE 
> '%XYZ%') then
> you're basically out of luck no matter what, since there's no 
> index that
> will ever run this quickly, the database has to check every 
> record one at a
> time.  Although we've proved that "WHERE FIELDNAME LIKE 
> '%XYZ%'" is still a
> significant improvement over "WHERE UCASE(FIELDNAME) LIKE '%XYZ%'"
> 
> So I'd also appreciate any info anybody has with SQL queries on V5R3!
> 
> Thanks,
> 
> Nigel Gay.
> Computer Patent Annuities.
> 
> 
> 
> |---------+------------------------------>
> |         |           EDoxtator          |
> |         |           <doc6502@xxxxxxxxx>|
> |         |           Sent by:           |
> |         |           java400-l-bounces@m|
> |         |           idrange.com        |
> |         |                              |
> |         |                              |
> |         |           03/11/2004 11:54 AM|
> |         |           Please respond to  |
> |         |           Java Programming on|
> |         |           and around the     |
> |         |           iSeries / AS400    |
> |         |                              |
> |---------+------------------------------>
>   
> >-------------------------------------------------------------
> -------------------------------------------------|
>   |                                                           
>                                                    |
>   |       To:       java400-l@xxxxxxxxxxxx                    
>                                                    |
>   |       cc:                                                 
>                                                    |
>   |       Subject:  JDBC and SQL Performance Under V5R3       
>                                                    |
>   
> >-------------------------------------------------------------
> -------------------------------------------------|
> 
> 
> 
> 
> All
> 
> I'm working at a client right now who is using OS/400 V5R1 
> and client has
> written a series of Java (JDK 1.4) with WSDS, and is seeing 
> some fairly
> awful
> performance issues.
> 
> I've suggested some things (limiting the use of the like 
> keyword, using
> existing logical file access paths, etc), but they've got a 
> huge amount of
> data
> (2 TB) to sift through.
> 
> >From what I have read from IBM, there are significant gains 
> to be made in
> overall SQL performance in V5R3.
> 
> Are these claims valid?  Has anyone been using V5R3?
> 
> Thanks
> 
> -Ed
> --
> This is the Java Programming on and around the iSeries / 
> AS400 (JAVA400-L)
> mailing list
> To post a message email: JAVA400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/java400-l
> or email: JAVA400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/java400-l.
> 
> 
> 
> 
> 
> 
> 
> 
> **************************************************************
> ******************
> The information in this message is confidential and may be legally
> privileged. It is intended solely for the addressee; access to this
> email by anyone else is unauthorised.
> 
> If you are not the intended recipient: (1) you are kindly requested
> to return a copy of this message to the sender indicating that you
> have received it in error, and to destroy the received copy; and (2)
> any disclosure or distribution of this message, as well as any action
> taken or omitted to be taken in reliance on its content, is prohibited
> and may be unlawful.
> **************************************************************
> ******************
> 
> --
> This is the Java Programming on and around the iSeries / 
> AS400 (JAVA400-L) mailing list
> To post a message email: JAVA400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/java400-l
> or email: JAVA400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/java400-l.
>