>>>Could it be that the definition of faster includes the time to transmit the resulting data set? In that case, if UNION ALL results in (say) 1,000,000 records while UNION results in only 5000 the transmission time for the 995000 extra records is more than the time to filter them?
>>
>>Yes, I think you nail the possible problem.
>
>When you use SELECT in VFP all tables included in that SELECT is transmitted to local computer. And that means ALL, no matter hat you select and what fields you select, whole tables are downloaded here. So the downloading time is not an option.
Are you sure about this? What if you use where clause like where 1=0?
>Using other backend like SQL Server that is a matter of "How smart SQL Optimizer is"
>Just my opinion.
If it's not broken, fix it until it is.
My Blog