Walter,


>How many subqueries can we have in one query? Is there a limitation also?
>
>Yep, there is. AFAIK a maximum of 2 subqueries could be used.

Ok, it this case I can not implement SubQuery approach, because in theory I may have more than 2 long lists of selected codes in my app. In real life it's very unusual situation, but it's possible...
So, I exclude this approach from possible scenarios...

>>I think, I'd leave the first condition without changes.
>>Now I have a question: what is faster: INLIST() or SubQuery?
>>Do you know the answer?
>
>INLIST, but the difference might be small. In general INLIST can take advantage of rushmore (when the compared filed is the first parameter), Working with subqueries requires an extra query to be executed and I don't know how this is internally optimized by VFP.

Ok, so I leave INLIST() condition as it is right now. If user selects less than 24 codes, I'd use INLIST() function (my current implementation).

>
>>If INLIST(), I should leave this as it it also. If not, I need to change both last cases to be
>>myField IN (Select code from CursorName).
>
>
>>>The subquery is rushmore optimizable and can pull as big a cursor as you like, up to the usual vfp table limits. However, wherever you have
>>>
>>>SELECT yourfields FROM yourtable WHERE searchfield IN (SELECT searched FROM searchcursor)
>>>
>>>you can also use
>>>
>>>SELECT yourfields FROM yourtable INNER JOIN searchcursor ON searchfield = searched
>>>
>>>which may actually be faster (and Ed probably knows if it is).
>>
>> It's also very important. I may implement this case. Do you know what is faster? Did somebody test the speed difference for huge (~1-4GB)databases?
>
>
>I'll guess you've got to test this yourself as indexes do play an important role here. My guess is that the JOIN might be faster as it only requires one query to be executed. The subquery in fact consist of 2. OTOH if you don't have any index on searchfield, the subquery might be faster as the optimizer has to

I have indexes on seachfield.

create internal indexes to speed up the join. I think much depends on the actual size of the two tables.
>
>>AFAIK, each additional join takes time and I already have 5 or 6.
>>Is there a limitation of number of Joins also?
>
>Yes there is, as you discovered yourself at the beginning of your post, I think the maximum was about 10.

I think, that I have only 3 multilists with potential problems, so it may be only 3 additional joins. So, the total number would be 9 or 10...
Therefore, I'd concentrate on this approach.

BTW, it will also affect the classes, which I put in Files section. Right now wherexp method contains wrong code :(...

Thanks a lot.