>Can a SELECT statement with Transform() or Count() functions be optimized?
>
VFP cannot optimize GROUP BY directive.
>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)
>
>
>
>>
>
>.
