>Hi!
>
>>Yes, I thought about it too, but unfortunately, we don't have them organized this way.
>>The typical examples :BAD
>>UAD, etc.
>
>Is it problem to make simple routine to sort them? It is jusy 1 run for large table, but when it will be kept sorted in future, you will never need to do this again.

I think, I can not change the order, because they may be placed in order by importance.

>>BTW, I ran already bunch of tests, using $ operator and Sergey's ideas and found, what for 2 items $ operator is better, for 3 items first Sergey's idea works faster, but time difference is ~20 sec. and time values like:
>>&& 3 items
>>214 s && 2 chrtran
>>243 s && 3 $
>>227 s && occurs + chrtran
>
>Did you tried to run query with 'LIKE' operator as I mentioned? I'm very curious about speed, is my idea worth enough?

Yes.
This is my test program:

********************************************************************
*  Description.......: Test_Amenities - tests amenities criteria
*  Calling Samples...:
*  Parameter List....:
*  Created by........:
*  Modified by.......: Nadya Nosonovsky 10/12/2000 02:38:10 PM
********************************************************************
*set talk on
clear
local lcSelList, lnSec, lcCrit, lcFileName, lcStr, lnElapsed, li
lcStr=''
lcFileName='TestAmenities.txt'
lcSelList='ABD'
for li=1 to 10
     lnSec=seconds()
     lcCrit = [Len(Chrtran(amenities, Chrtran(amenities, lcSelList, ""), "")) = 3]
     select amenities from bldgmstr where &lcCrit into cursor tt
     lnElapsed=round(seconds()-lnSec,1)
     ? lnElapsed, _tally
     lcStr=lcStr+chr(13)+'2 chrtrans: '+'Elapsed time: '+transform(lnElapsed)+' sec. Number of records: '+transform(_tally)
***********************************
     lnSec=seconds()
     lcCrit = ['A' $ amenities and 'B' $ amenities and 'D' $ amenities]
     select amenities from bldgmstr where &lcCrit into cursor bb
     lnElapsed=round(seconds()-lnSec,1)
     ? lnElapsed, _tally
     lcStr=lcStr+chr(13)+'3 $: '+'Elapsed time: '+transform(lnElapsed)+' sec. Number of records: '+transform(_tally)
************************************************
     lnSec=seconds()
     lcCrit = [occurs("~", Chrtran(amenities, lcSelList, "~~~")) = 3]
     select amenities from bldgmstr where &lcCrit into cursor bb
     lnElapsed=round(seconds()-lnSec,1)
     ? lnElapsed, _tally
     lcStr=lcStr+chr(13)+'Occurs+chrtran : '+'Elapsed time: '+transform(lnElapsed)+' sec. Number of records: '+transform(_tally)
************************************************
     lnSec=seconds()
     lcCrit = [amenities like '%BDA%']
     select amenities from bldgmstr where &lcCrit into cursor bb
     lnElapsed=round(seconds()-lnSec,1)
     ? lnElapsed, _tally
     lcStr=lcStr+chr(13)+'Like : '+'Elapsed time: '+transform(lnElapsed)+' sec. Number of records: '+transform(_tally)
************************
     strtofile(lcStr,lcFileName,.t.)
next
modi file (lcFileName)

It's still running... :) But 2 previous result indicated no speed benefit.

>>I run these quieries directly from the server and on one table (1,8 mln. records). From my local machine it should be worse...
>
>Why you thinnk so? All these expressions don't use indexes for optimization (empty function or chrtran or '$' operator or LIKE operator), so no extra overhead because indexes. Delay will be just in data transferring.
>
>In addition, you have good reason to move that database to SQL server... Just a thought.

Right. But it's not an easy process...