Performance problem when updating a view (using an instead of trigger) via a cursor in Sql Server 2005

Question

Dear all,

I have a view V in database D1 on which an update trigger U is defined that updates three tables T1, T2 and T3 in database D2. Those three tables contain 0.75, 6.0 & 4.5 million records. The Sql Server version is 9.0.3042, Developer Edition.

When I perform the following update statement on the above view it is ready within one second:

UPDATE V
SET cust_task_id = 11975628
WHERE custno = '0319607'

However when I issue the following batch it takes one minute and 12 seconds to complete:

DECLARE CURSOR c
FOR SELECT custno, cust_task_id FROM V WHERE custno = '0319607'
FOR UPDATE OF cust_task_id

OPEN c
    FETCH NEXT FROM c

    UPDATE V
    SET cust_task_id = 11975628
    WHERE CURRENT OF c    
CLOSE c
DEALLOCATE c

When I use the same databases (with respect to the definition and number of records) and the same update statements on the 2000 version of Sql Server (8.0.2039, Standard Edition) both batches return within one second!

When you look at the query plans of the cursor batches on the two versions of Sql Server you see that the 2005 plan uses index scans on the tables T1, T2 and T3 whereas the 2000 plan uses index seeks. So that explains the difference in performance.

I have tried sp_updatestats on the 2005 databases, but that didn't help.

Has anybody any idea how I can get the 2005 cursor batch to perform how it should??

Thank you in advance for taking time to look into this matter.

Greetings,

Lex Verbeek

Answer 1

Have you done a diff on the schemas for the 2000 version vs. the 2005 version.

If the 2005 version is scanning instead of seeking, it sounds like you might be missing an index on one of the joiner columns that's used to create your VIEW

Answer 2

To solve your immediate problem, we had a similar problem in SQL 2000 a while ago with a cursor using a datasource that itself was modified during the cursor loop (in this case you are selecting from V and also updating V). From memory (and it would be good to have confirmation on this) the datasource for the cursor was either updated or regenerated so as to keep the datasource up to date with the changes made.

Our resolution was to first of all select your data into a temp table or table variable, and using that as the source for your cursor. Of course you would need to modify your SQL code to update using an ID or other unique identifier, but I will assume you have these constraints in the system.

Of course it does sound like there is a bit of room for optimization in general with your solution, cursors for example are generally not the preferred approach - but you may find the above gets you to an acceptable level of performance, at least for now.