Hi,
I thought about such way... And for now - my minds is next
1) No reason create table for rec_id, word (as getwordnum).. because I did it .. and table has 60 000 000 records.. time of prepare such cursor take long time
2) Also bad.. lead % - need to be used.. for single word in field for search .. using lead % - query not optimized and take long time
3) Big String(memo) field - solution looks good.. but length of sum of strings might be long... then memo.. then not possible to use Index, then not possible rushmore optimize.. same not good
4) I thinking the best way will
Create cursor with "rec_id", Description, Description2, productgroup
Each Description, Description2, productgroup will begins with "####" string + Description or +Description2.. etc..
then string will stored for example
"DeskJet HP Printer" in format "####DeskJet HP Printer"
Then Index this field using UPPER(expression)
Then.. at this case.. I can search string using rushmore optimization without lead "%"
like
UPPER(Field) LIKE "####"%HP%Printer"
Then.. no lead "%" using... expression rushmore optimized... bad thing.. I need to have additional table.. I need to have description1,2, group twice in both table.. I need control any update, delete, insert to update this table...
Or... when application starts.. create on user end such template cursor by SELECT "####"+descr as .., "####"+descr2 as n, "#####"+group INTO .... and then INDEX ON UPPER()...
Performance will bad on start application.. + nothing refresh this cursor if changes in network will appears..
Its like static cursor during user session...
Well.....
Still can't choose which solution might be better in this case....
>>Hi,
>>I need help to rushmore optimize query using LIKE... or maybe change sql expression code
>>Example of query
>>UPPER(Description) like "%HP%PRINTER" or UPPER(Description2) like "%HP%PRINTER" or UPPER(Manufacture) like "%HP%PRINTER" or UPPER(productgroup) like %HP%PRINTER"
>>
>>Finally.. I'v got SYS(3054) - partial.. only Deleted tag using for rushmore optimization...
>>
>>Any ideas how to optimize this query, or maybe another way to get same result
>
>OK, here's a SWAG...
>
>
select nkey, upper(Description+Description2+Manufacture+productgroup) as cBig;
> from yourtable into cursor tmp
>
>select from yourtable where nkey in (select nkey from tmp ;
> where cBig like "%HP%PRINTER"
>
>I.e. pull the key and your description fields into a cursor, then do the LIKE search in that cursor. May be faster, I wouldn't know. I really don't have such a big table to try with.