Whole Word, Case Sensitive control

[Ruslan Zasukhin]

On 4/22/04 10:25 PM, "Juergen Schreck" <lists at innernine.com> wrote:

Hi Juergen, 

It seems you already know what is BaseObject method, yes?

> Is there any benefit in specifying a fldLwr BaseObject Method for my
> case insensitive comparison rather than using 'LOWER(fld)' in the SQL
> statement?
> 
> How are indexes being used in either situation?

:-) good question.

Detailed answer is:

1) assume you have 

    WHERE LOWER(fld) = 'lower_case_word'

    NOTE: Valentina 1.x cannot do this.
            so in any case you need use BaseObject method.
            but Valentina 2.0 do support this, and many dbs also
        

If you have just such query, then OPS, search will be NOT indexed.

To make it indexed, you MUST do

        CREATE INDEX iname LOWER(fld)

Btw, a lots of db do not allow expression in CREATE INDEX,
They allow just 

        CREATE INDEX iname f1, f2

Okay, assume DBMS allow create index by ANY expression.

Now SQL Executor/Optimizer, must find the best index for search.
IF you have a lots of indexes in db, this is additional waste of time.

Working with BaseObject method, you get INDEX for a field, based on
expression. This index belong to virtual field, you can consider this as
NAMED INDEX. So when you say

    WHERE fldLwr = '....'

DBMS (Valentina) spemd ZERO time to determinate the best and only the best
index for that search, Because you self have point it.

Please read all docs, and faqs, if more questions, then ask.



-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------