Re: JDBC and SQL Performance Under V5R3

[Raul Jager <raul@xxxxxxxxxx>]

Will the "text extension" be of any help?
_____________________________________________________________________
El Wednesday 03 November 2004 12:50, NGay@xxxxxxxxxxxxx escribió:
> 
> 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.