Solved

Forcing a Sort Merge Join in a Trigger

Posted on 2007-04-10
9
562 Views
Last Modified: 2008-01-09
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
Comment
Question by:grant300
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 18888261
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
 
LVL 19

Author Comment

by:grant300
ID: 18890948
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
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 500 total points
ID: 18894881
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 19

Author Comment

by:grant300
ID: 18898886
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
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 18902695
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
 
LVL 19

Author Comment

by:grant300
ID: 19040116
Note to Moderator:

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

Thanks.
0
 
LVL 19

Author Comment

by:grant300
ID: 19994672
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
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 19997083
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
 
LVL 19

Author Comment

by:grant300
ID: 19998873
!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

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article outlines some of the reasons why an email message gets flagged as spam on a recipient's end.
Make the most of your online learning experience.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question