I would like to coerce it into using a SORT MERGE Join but am just now looking into abstract plans. Has anybody got any idea how to make this happen? SC_ID is a unique identifier but, of course, there are no indexes on the Deleted and Inserted pseudo tables in a trigger.

I thought about the temp table route but you cannot create temp tables in the context of a trigger.

The Sort-Merge-Join, even without indexes is quite a bit better than Nested Loop. If you can get it to do it, it creates a sorted work table for each of the base tables then does the merge between them. The merge is actually pretty cheap at that point; just one table scan for each of the tables.

I think the biggest problem with optimizing queries in triggers is that you have no statistics on the Inserted and Deleted pseudo tables. Nested Loop works fine for small N, but I have times when this system updates 30 to 40 thousand rows at a time and the table scans, even though they are in memory, are just killing it.

The trees get pretty large, 30 to 40 thousand members is no rare. When someone deletes one of the trees, (actually an expire with an Update since we effectivity date everything and move old version off into another database) it is done something like:
UPDATE STRUCT_CHAN
SET EFF_DT = GETDATE()
WEHRE ST_ID LIKE '1000A2%'

ST_ID is the enumerated path "SubTree" identifier.

So, if there were just one place in the code where this operation was performed, it would not be too bad. Set the rowcount to some reasonable limit and loop over the UPDATE statement until no rows are affected. This doesn't help with the log because this operation has to succeed or fail as a unit, but it does keep the trigger problem from growing.

The other problem is that we also do fairly extensive updates between old and new versions of the trees. The Insert/Update/Delete triple is actually reduced to Deletes(expireds) and Inserts so we only need to deal with two operations, not three. That code is spread out all over the place so it is simply impractical to batch this stuff everywhere.

BTW, just to add to the fun, a lot of this stuff is driven from a GUI so there is an operator sitting there waiting for each operation to complete.

Trying to do anything in triggers is kind of a pain in the rear. RapidSQL hooks up to the Sybase Debugger and presents a nice interface, and the debugger actually dives down into the triggers if you let it. The debugger has a few ideosyncracies and, though I have not tried it yet, I am willing to bet that tacking a PLAN clause on a query will confuse it.

I have to get a repeatable mockup and see if I can actually force an AQP without it complaining.

A few notes as I close this:
1) I did get it to work in 12.5.4, not 12.5.1 Go figure.
2) It would not swallow a partial plan in a trigger. A complete plan was required
3) I actually got around the problem by joining the deleted pseudo table to the base table
which was, of course, indexed. This works much better than joining the inserted
and deleted tables.
4) All of this became mute with 15.0.x as the optimizer figured out it needed to do a Sort
Merge Join on it's own.

In general, I have found the v15 query optimizer/processor to be a very nice improvement over 12.5.x particularly when it comes to dealing with temp table. We have been able to remove a bunch of force plans and index hints and the application runs faster than it did under 12.5.x

!5.0.0. had some serious problems, at least on Linux but 15.0.1 was very stable and we have had good success with 15.0.2

I have been looking for a good excuse to use the new T-SQL function capability but will have to wait until my client's code base isn't required to run across 12.5.1 (yuck) AND 15.0.2 Its pretty bad when they see a patch from 12.5.1 to 12.5.4 to be an upgrade. We have gotten smarter about it though; starting with v15, we now only refer to 15.x and never the second decimal point. It's all about perception ;-)