We are on ASE 15.0.3/EBF 21284 ESD#4.3 working on a application with over 3000 stored procedures.
Our server optimization goal is allrows_mix.
The Merge-Join is giving us problems. When a query uses Merge-Join, it usually take an order of magnitude longer to run than if we force it
to use the other types of joins - nested-loop, n-ary-nested-loop, or hash-join.
The query plan shows sorting on worktables leading into the merge-join.
I know I can disable it with "set merge_join off", or "set plan optgoal allrows_oltp", but I'd rather not if I can fix the problem instead.
Question: Are there configuration options that would help merge-join?
I've done variations of this:
I've also done variations of this in the proc:
When I run the following command, I see sort buffer starvation:
Maybe there are other configuration option to help merge-joins? Any ideas?