Mike,
your comment about index tags on deleted() are not valid anymore! VFP9 introduced binary tags, which makes that old discussion invalid. "Index on deleted() tag deleted binary" will in most cases result in much quicker Selects than without the index tag.
>Hi Mk
>
>I take a slightly different approach to this kind of thing. If you need all records then it is a waste to add "AND .T.". So I construct exactly the query I need using TEXT...ENDTEXT and execute it with a single &. If you had more than one dynamic condition you would use & for each one which I would avoid. I use text merge to add the conditions. UT does not like the text merge delimiters. I added extra spaces to " < < m.lcLiftcond > >" please remove those before you run it.
>
>Remember empty() is not Rushmore optimizable. Avoid using it in WHERE clauses and FOR conditions.
>
>See below for the changes I made. :)
>
>>How to Full optimize below Select - sql ?
>>
>>
>>clear
>>Local lcLiftcond
>>Close Databases All
>>Use 1001\psaudac In 0
>>Use 1001\psaudam In 0
>>Use 1001\customer In 0
>>Use 1001\pcode1 In 0
>>
>>Do Case
>> Case Thisform.Optiongroup1.Value = 1 && All Records
>> lcLiftcond = ''
>> Case Thisform.Optiongroup1.Value = 2 && Only Pending Records
>> lcLiftcond = " and liftdate == {} "
>> Case Thisform.Optiongroup1.Value = 3 && Only Final Records
>> lcLiftcond = " and not liftdate == {} "
>>Endcase
>>
>>= Sys(3054, 1)
>>
>TEXT TO lcCmd TEXTMERGE NOSHOW
>>Select ;
>> Str(Val(psaudam.saudano), 10) As dono, ;
>> godown.acname As locname, ;
>> party.acname As acname, ;
>> pcode1.wood As prodname, ;
>> mill.acname As millname, ;
>> psaudac.Lotno, ;
>> psaudac.Bags, ;
>> psaudac.Weight, ;
>> psaudac.liftdate, ;
>> psaudac.Rem1, ;
>> psaudac.Childid ;
>> From psaudam ;
>> Left Outer Join customer godown ;
>> On godown.accode = psaudam.loccode ;
>> Left Outer Join customer party ;
>> On party.accode = psaudam.accode ;
>> inner Join psaudac ;
>> On psaudac.Masterid = psaudam.Masterid ;
>> Left Outer Join pcode1 pcode1 ;
>> On pcode1.pcode = psaudac.pcode ;
>> Left Outer Join customer mill ;
>> On mill.accode = psaudac.millcode ;
>> Where psaudam.trnstatus = 'YO' ;
>> < < m.lcLiftcond > > ;
>> Order By 1 ;
>> Into Cursor junk1 Readwrite
>ENDTEXT
>*Now execute the command.
>>Select 0
>&lcCmd.
>
>I would remove all indexes on deleted. Don't try to optimize deleted at all. To test if you have "full optimization" just SET DELETED OFF, run the query and SET DELETED ON after. SET DELETED ON should be in production. That way you only test the important parts of the query.
