Hi Sorin,

I tested it and it's slow, if I need to open/close RIDefinitions table on each trigger's execution. If I can leave this open, this would be another story, but I can not guarantee, that this table would be opened in advance. So, I tested the worst scenario (50000 replaces of the field with the same value) and found, that SCAN WHILE is much slower than SCAN FOR.

I agree with the second comment as well. Perhaps, I can add a check:
if not '(' or '+' in Expr, then I don't need to change workareas. However, two additional checks (I need to check for both chars, I guess, that's enough, though it also could be -) may slow this down too.

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