Plan caching may be involved. The optimizer compiles a plan without looking
at the current values of variables, in some cases this can cause a poor plan
to be selected. This special plan is used on the 10th execution of a
statement eligible for caching.

Try setting the option max_plans_cached=0 either globally or within the
procedure. If this changes the behaviour, you can use the "OPTION( FORCE
OPTIMIZATION )" query hint to prevent plan caching for the individual
statement that is causing the problem.

The server uses a policy intended to avoid poor plans being generated by
plan caching but there are some cases that are not detected by this policy.

--
Ivan T. Bowman
SQL Anywhere Research and Development
[Sybase iAnywhere]

<Harrald K. Ott> wrote