Nadya,

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.

>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.

>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 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.


Walter,