Overcome MERGE JOIN(INDEX SCAN) with explicit single KEY value on a FOREIGN KEY

Added 7/11 The issue is deadlocks occur because of index scan during MERGE JOIN. In this case a transaction trying to get S lock on the whole index at the FK parent table, but previously another transaction puts X lock on a key value of the index.

Let me start with a small example (TSQL2012 DB from 70-461 cource used):

CREATE TABLE [Sales].[Orders](
[orderid] [int] IDENTITY(1,1) NOT NULL,
[custid] [int] NULL,
[empid] [int] NOT NULL,
[shipperid] [int] NOT NULL,
... )

Columns [custid], [empid], [shipperid] are corelated parameters for [Sales].[Customers], [HR].[Employees], [Sales].[Shippers] accordingly. In each case we have a clustered index on a referred column in a parrent table.

ALTER TABLE [Sales].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([custid]) REFERENCES [Sales].[Customers] ([custid])
ALTER TABLE [Sales].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Employees] FOREIGN KEY([empid]) REFERENCES [HR].[Employees] ([empid])
ALTER TABLE [Sales].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY([shipperid])REFERENCES [Sales].[Shippers] ([shipperid])

I am trying to INSERT [Sales].[Orders] SELECT ... FROM another table called [Sales].[OrdersCache] which has the same structure as the [Sales].[Orders] except foreign keys.
Another thing might be important to mention the table [Sales].[OrdersCache] is a clustered index.

CREATE CLUSTERED INDEX idx_c_OrdersCache ON Sales.OrdersCache ( custid, empid )

As expected when Im trying to insert a low volumes of data LOOP JOIN works fine making index seek on the foreign keys.

With high volumes of data MERGE JOIN is used by query optimizer as a most efficient way to maintain foregn key in the query.

And there is nothing to do with it exept using OPTION (LOOP JOIN) in our case with foreign keys or INNER LOOP JOIN in explicit JOIN case.

Below is the query Im trying to run in my environment:

```
INSERT Sales.Orders (
custid, emp

Using OPTION (LOOP JOIN) isnt suitable since it costs almost 15% more than MERGE JOIN

The cost percentages displayed in showplan output are always optimizer model estimates, even in a post-execution (actual) plan. These costs likely do not reflect actual runtime performance on your particular hardware. The only way to be sure is to test the alternatives with your workload, measuring whichever metrics are most important to you (elapsed time, CPU usage and so on).

In my experience, the optimizer switches from loops join to merge join for foreign key validation far too early. In all but the largest operations, I have found loops join to be preferable. And by "large" there I mean tens of millions of rows at least, certainly not the thousand or so you indicate in the comments to your question.

In my opinion there must be a way to make the validation phase for the inserted set faster at least for the immutable attribute.

This makes sense in principle, but there is no such logic today within the plan-building logic that foreign key validation uses. The current logic is deliberately very generic and orthogonal to the wider query; specific optimizations complicate testing and increase the likelihood of edge-case bugs.

Is there any common pattern to overpass the situation above using code structures, additional DDL objects, etc?

Not that I am aware of. The deadlock risk with merge join foreign key validation is well known, with the most widely-used workaround being the OPTION (LOOP JOIN) hint. There is no way to avoid shared locks being taken during foreign key validation, because these are required for correctness, even under row-versioning isolation levels. You may prefer to hint OPTION (LOOP JOIN, HASH JOIN)` to allow the optimizer more freedom for other joins in the same query. Hash join is not available for FK validation though.

There is no better general answer (than the loop join hint) if you want to retain the ability for multiple concurrent processes to add rows to the parent and child tables transactionally. But, if you are willing to serialize the data modifications (not reads), using sp_getapplock is a reliable and simple technique.

StackExchange Database Administrators Q#106319, answer score: 11