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



Don't use functions which can return different field lengths

Use eg

padl( '(' + Transform(Count(MachineTasks.job_num)) + ')  ' , 20) As job_count;

or 

'(' + padl(Count(MachineTasks.job_num), 20) + ')'

or better

Count(MachineTasks.job_num))  As job_count

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

>
>
>.