• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 594
  • Last Modified:

Forcing a Sort Merge Join in a Trigger

I have an update trigger with the following code fragment.  The performance degrades as the number of rows being updated increases since it will do N table scans.

INSERT INTO MYTABLE_HIST
    SELECT dl.S_ID, dl.NAME_NOTATION, ............ dl.RECORD_STATUS
       FROM deleted dl
        JOIN inserted ir
           ON ir.SC_ID = dl.SC_ID
    WHERE ir.SU_ID < 1000

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.

Thanks in advance,
Bill
0
grant300
Asked:
grant300
  • 5
  • 4
1 Solution
 
Joe WoodhousePrincipal ConsultantCommented:
Hmmm. I'm not sure how to force the sort-merge join. Remember without a clustered index they aren't that useful anyway.

I wonder if a better answer might be to instead clone the inserted/deleted tables into temp tables, build indexes there, and then do the insert into your destination table?

That does 4 table scans plus index scans, but as your N gets large that's a good bargain...
0
 
grant300Author Commented:
Joe,

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.

thanks,
Bill
0
 
Joe WoodhousePrincipal ConsultantCommented:
Temp tables, yeah. I knew that! 8-) You could still fake it with non-temp tables, but ok, if you can force AQPs in a trigger then that will outperform my idea.

Looking at it from another angle, maybe the updates could be batched into smaller numbers. It would help transaction logs and any replication, not just the triggers.

Agree we need more control over optimiser behaviour in triggers!
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
grant300Author Commented:
Batching them could be tough.

The problem is that the data structure in this table contains trees.  The tree structure is maintained by enumerated path  (RootID, ChildID = RootID + Sibling#, GrandChildID = ChildID + Sibling#, etc.)

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.

Wish me luck.

Bill
0
 
Joe WoodhousePrincipal ConsultantCommented:
I'm very interested to see if you can adequately force AQPs in triggers - that could be a useful hack for solving many problems!
0
 
grant300Author Commented:
Note to Moderator:

I am keeping this open a while longer so that I can, hopefully, post the solution I am developing.

Thanks.
0
 
grant300Author Commented:
Joe,

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

Thanks again for the help and being a sounding board.

Bill
0
 
Joe WoodhousePrincipal ConsultantCommented:
All useful to know, thank you.

I've not had a chance to throw any real production workload at 15 yet - people here seem to be very conservative about it, sadly. 15.0.2 in particular looks to be just what the doctor ordered though.
0
 
grant300Author Commented:
!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 ;-)

Regards,
Bill
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now