>>>My tests showed, much to my surprise, that LIKE was not optimized.
>>
>>Our tests showed partial optimization for LIKE clause and full optimization for =. Now, I thought I read somewhere that VFP 9 has full optimizable LIKE, but I don't remember, where I read it, so do not know, if it's true.
>>
>>I used SET ANSI OFF in order to use =, but I belive, it makes now everything not generic, since other back-ends use ANSI ON. So, it looks like I have to use LIKE clause if I want to easy switch back-end.
>>
>>What do you think about this?
>>
>>Thanks in advance.
>
>Sorry for Jump in.
>
>With SET ANSI OFF and = you write a two side LIKE 'item%' comparison,
>
>expression1=expression2
>is
>expression1 LIKE 'expression2%' OR expression2 LIKE 'expression1%'
>
>and it is impossible optimize it without a key index with variable lenght.
>
>Before VFP9 in many situations VFP it optimized the query and it could return to you wrong results.
>
>On VFP the LIKE 'expr%' can run with a full index filtering,
>but exists two issues:
>1. on VFP don't exists a escape character,
>then for search a '%' or a '_' the LIKE is useless
>2. on all backends the LIKE comparison uses the context Collate order,
>as == ( ANSI ON VFP comparison),
>but on VFP the LIKE uses MACHINE COLLATE,
>and without a MACHINE index the optimization is not full.
>
>Fabio
Fabio,
Thanks for your reply. I am creating a search form and I implemented it using = operator with SET ANSI OFF. But I want to write it the universal way (keeping in mind the possibility of other back-end), so I will need to switch to LIKE operator... I am afraid I will have performance penalty...
BTW, unrelated small question: I have a container based phone control. I set a property lNoExtension, and if this property is set to true I want to remove txtExtension and its label. I wrote it using RemoveObject, but I'm thinking, it would be better to return .f. from the Init of these controls instead. What do you think? Or it's such a minor thing I should not even put my mind into...
If it's not broken, fix it until it is.
My Blog