Solved

Forcing a Sort Merge Join in a Trigger

Posted on 2007-04-10
9
515 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
  • 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

In this article, I show you step by step with screenshots to assist you - HOW TO: Deploy and Install the VMware vCenter Server Appliance 6.5 (VCSA 6.5), with some helpful tips along the way.
We have come a long way with backup and data protection — from backing up to floppies, external drives, CDs, Blu-ray, flash drives, SSD drives, and now to the cloud.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now