FWIW - when doing complex queries against VFP data I find it MUCH faster to prequery the main tables on simple rushmore optimizable expressions and then use those cursors in my "real" query

select fieldnames from maintable1 where somenum=x into cursor c_maintable1 readwrite nofilter

select fieldnames from maintable2 where somenum=x into cursor c_maintable2 readwrite nofilter


select c_maintable1.fieldnames,c_maintable2.fieldnames
from c_maintable1 inner/left join on c_maintable2 on somecriteria blah blah blah


Using this technique I have sped up queries by a factor of 10 or more.




>Can a SELECT statement with Transform() or Count() functions be optimized?
>
>I'm finding that what shoudl be a pretty simple SQL query runs kind of slow, and I must call it 4 times with different value to build 4 separate cursors. I'm looking at ways to make it faster. I have index on all the fields, but I suspect use of Transform and/or Count() is slowing it down since VFP cannot optimize it. The query is running against FoxPro DBF tables, not SQL server.
>
>Function:
>
>

>Lparameters lcMachineClass, tcMachineType, tcCursor
>
>Select  Machines.mach_num, Machines.mach_name, ;
>	'(' + Transform(Count(MachineTasks.job_num)) + ')  ' As job_count;
>	From Machines;
>		Left Join ;
>			(Select Schedule.job_num, Schedule.mach_num As sched_mach_num ;
>			  From Schedule ;
>			  Left Join Jobs On Schedule.job_num = Jobs.job_num ;
>			  Where Jobs.Status = 'A' And Schedule.Status != 'C') MachineTasks ;
>			On Machines.mach_num = MachineTasks.sched_mach_num;
>	Where Machines.Status = 'A';
>	And Machines.Class = lcMachineClass;
>	And Machines.Type = tcMachineType;
>	Group By Machines.mach_num, mach_name;
>	Into Cursor (tcCursor) Readwrite
>
>Replace job_count With '' For job_count = '(0)' In (tcCursor)
>Goto Top In (tcCursor)
>
>
>
>

>
>
>.