Sql-server – SQL Server using index scan instead of seek when updating a view that has an INSTEAD OF UPDATE trigger via a cursor

cursorsperformancesql serversql server 2014trigger

I have a 3rd party application which uses a cursor that updates rows in my SQL Server 2014 table. I am unable to modify this code and have therefore devised a scheme to point the cursor to a view which has an INSTEAD OF UPDATE trigger where I can intercept and fully control the update. I am very aware that cursors are evil, but I am forced to work with them because I cannot modify the source programs.

When the UPDATE…WHERE CURRENT OF statement executes, it's performing a clustered index scan rather than a seek to locate the record that the cursor is currently pointing at. I cannot determine why the optimizer is doing a scan when it could be doing a seek. I believe that it's a combination of cursor + view + instead of update trigger that is causing the problem, because if I remove any one of these 3 variables from my tests, the index seek is used properly.

Note that it doesn't matter how many rows are in the table; the optimizer always uses a scan, according to the execution plan. I've even removed all of the logic within the trigger to further simplify the test.

Here's some simple code to recreate the issue:

CREATE TABLE [dbo].[Person](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Name] [varchar](40) NULL,
  CONSTRAINT [PK_Person_Id] PRIMARY KEY CLUSTERED ( [Id] ASC )
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])
  ON [PRIMARY]
GO

CREATE VIEW [dbo].[vPerson]
AS SELECT Id, Name FROM dbo.Person
GO

CREATE TRIGGER [dbo].[InsteadOfUpdate_vPerson] ON [dbo].[vPerson]
INSTEAD OF UPDATE
AS
BEGIN
  SET NOCOUNT ON
END
GO

INSERT INTO Person VALUES('John Doe');
GO

--This will use a seek
UPDATE dbo.vPerson
SET Name = 'Jane Doe'
WHERE Id = 1
GO

--This will not use a seek
DECLARE c CURSOR FOR
SELECT * FROM dbo.vPerson
WHERE Id = 1
FOR UPDATE

OPEN c
FETCH NEXT FROM c

UPDATE dbo.vPerson
SET Name = 'Jane Doe'
WHERE CURRENT OF c
CLOSE c
DEALLOCATE c
GO

Any help or suggestions on things to try would be greatly appreciated.

Best Answer

This looks like an oversight.

The optimizer cannot generate an apply-style indexed loops join when the update is performed using WHERE CURRENT OF and the target view (schema-bound or not) has a T-SQL instead-of update trigger, regardless of the number of rows, or any other considerations:

Execution plan

That shows an example with almost 20,000 rows in the table (copied from the AdventureWorks' Person table, as it happens).

The join predicate gets 'stuck' on the nested loops join operator itself, rather than being pushed into the inner side to produce a seek:

Join properties

Since you cannot change the code, you should report this as a bug through your normal Microsoft Support channel. You may also report the bug on Connect, but the chances are much lower of getting a quick response or fix via that route.

Just for interest's sake, the plan you are after is possible using an API cursor positioned update (the most analogous operation internally):

DECLARE 
    @cur integer,
    @scrollopt integer = 2 | 8192 | 32768 | 131072, -- DYNAMIC | AUTO_FETCH | CHECK_ACCEPTED_TYPES | DYNAMIC_ACCEPTABLE
    @ccopt integer = 2 | 32768 | 131072, -- SCROLL_LOCKS | CHECK_ACCEPTED_OPTS | SCROLL_LOCKS_ACCEPTABLE
    @rowcount integer = 1;

-- Open the cursor
EXECUTE sys.sp_cursoropen
    @cur OUTPUT,
    N'
    SELECT * FROM dbo.vPerson WHERE Id = 1;
    ',
    @scrollopt OUTPUT,
    @ccopt OUTPUT,
    @rowcount OUTPUT;

-- Request a positioned update
EXECUTE sys.sp_cursor
    @cur,
    1, -- UPDATE
    1, -- row number in buffer
    'dbo.vPerson', -- table (unambiguous in this case)
    'Name=''Banana'''; -- new value

-- Close    
EXECUTE sys.sp_cursorclose -1;

The execution plan is:

Execution plan

Notice the index seek on Person (the predicate is not 'stuck'):

Seek properties

This is not a workaround for you since you cannot change the source query. There's no way to hint or plan guide your way around your issue; the optimizer simply cannot generate the seek plan you expect in your specific case. Attempting e.g. a FORCESEEK hint simply results in an error message saying the optimizer could not produce an execution plan.

Related Question