Nadya,

The following may been suggested before;
If you have only one index, and that is on SearchTB, then SEEK / SCAN WHILE is the fastest it can get. I assume that the table is open with the tag on SearchTB thus you won't have to use SET ORDER repeatedly.

CASE m.lnTriggerType = TriggerTypeCode
   m.lcSearch = PADR(m.lcSearch, FSIZE("searchTB"))
   SCAN WHILE !m.plError AND SearchTB == m.lcSearch FOR NOT EMPTY(CommandField)
      ...
   ENDSCAN
...

Also, SELECT workarea may slow it down, too. If possible, you should try qualifing the fields in EVAL expressions with the alias, instead of SELECTing various aliases.

HTH


>Here is the latest code. I presently have index on SearchTB only. Adding indexes on empty(InsCommand) and others doesn't speed it up but rather slow it down. Can this piece of code be re-worked to achieve maximum performance?
>
>

>do case
>	case m.lnTriggerType = cnUpdateCode && Update trigger
>*	set order to UpdTrig
>		scan for SearchTB = m.lcSearch and not empty(UpdCommand) and !m.plError&&and not empty(UpdCommand)
>			lcParent = ParentTB
>			lcChild = ChildTB
>			do case
>			case m.lcParent = ChildTB && very rare case of self-join
>				luOldKey = oldval(m.lcChildKeyExp, m.lcAlias)
>				select (m.lcAlias)
>				luNewKey = evaluate(m.lcChildKeyExp)
>				if cL_USE_AGAIN
>					select (m.lnSelRIDefi)
>				else
>					select RIDefinitions
>				endif
>
>			case m.lcParent = m.lcSearch
>				if not m.lcParentTag = alltrim(ParentTag)
>					lcParentTag  = alltrim(ParentTag)
>					lcParentKeyExp = alltrim(ParentKey)
>					luOldKey       = oldval(m.lcParentKeyExp, m.lcAlias)
>					select (m.lcAlias)
>					luNewKey       = evaluate( m.lcParentKeyExp )
>					if cL_USE_AGAIN
>						select (m.lnSelRIDefi)
>					else
>						select RIDefinitions
>					endif
>				endif
>
>			case m.lcChild = m.lcSearch
>				if not m.lcChildTag = alltrim(ChildTag)
>					lcChildTag  = alltrim(ChildTag)
>					lcChildKeyExp = alltrim(ChildKey)
>					luOldKey       = oldval(m.lcChildKeyExp, m.lcAlias)
>					select (m.lcAlias)
>					luNewKey       = evaluate( m.lcParentKeyExp )
>					if cL_USE_AGAIN
>						select (m.lnSelRIDefi)
>					else
>						select RIDefinitions
>					endif
>				endif
>			endcase
>
>			if m.luNewKey <> m.luOldKey && Check for IsNull is embedded indirectly)
>				=evaluate(alltrim(UpdCommand))
>			endif
>		endscan
>
>	case m.lnTriggerType = cnInsertCode  && Insert trigger
>*	set order to InsTrig
>
>		scan for SearchTB = m.lcSearch and not empty(InsCommand) and !m.plError && and not empty(InsCommand)
>
>			if vartype(m.pcCascadeParent) <> "C" or m.pcCascadeParent <> alltrim(ParentTB)
>
>				if m.lcChildKeyExp <> alltrim(ChildKey) && we don't want to evaluate more times than needed
>					lcChildKeyExp = alltrim(ChildKey)
>					select (m.lcAlias)
>					luNewKey  = evaluate(m.lcChildKeyExp)
>					if cL_USE_AGAIN
>						select (m.lnSelRIDefi)
>					else
>						select RIDefinitions
>					endif
>				endif
>
>				if !isnull(m.luNewKey)
>					=evaluate(alltrim(InsCommand))
>				endif
>			endif
>		endscan
>
>	case m.lnTriggerType = cnDeleteCode && Delete trigger
>*	set order to DelTrig
>		scan for SearchTB = m.lcSearch and not empty(DelCommand) and not m.plError &&and not empty(DelCommand)
>			if not m.lcParentTag = alltrim(ParentTag)
>				lcParentTag  = alltrim(ParentTag)
>				lcParentKeyExp = alltrim(ParentKey)
>				select (m.lcAlias)
>				luKey       = evaluate( m.lcParentKeyExp )
>				if cL_USE_AGAIN
>					select (m.lnSelRIDefi)
>				else
>					select RIDefinitions
>				endif
>			endif
>
>			if not isnull(m.luKey)
>				=evaluate(alltrim(DelCommand))
>			endif
>		endscan
>	endcase

>
>>I agree with Sylvain. The Scan for is optimizable, although just for Borislav's delight, I'd just change the final code as:
>>
>>

>>*-- Have an index on these two fields:
>>INDEX ON EMPTY (InsCommand) TAG E_InsCmd
>>INDEX ON SearchTB TAG SearchTB
>>SET ORDER TO
>>
>>LOCATE ;
>> FOR SearchTB = m.lcSearch AND NOT EMPTY (InsCommand) ;
>> WHILE !plError
>>

>>
>>If I understood correctly the use of plError. It looks like a parameter, but if this is the case, I can't imagine what does it have to do with the condition...
>>
>>Cheers,