I'm a bit confused here; I'm using a procedure to move data from one table to another. The procedure is pretty straight forward: a fast_forward cursor selects the data from table1, it's values are placed in variables, which on their turn are inserted into table2. After the insert, the data from table1 is deleted.
Pretty much as:
- select field1, field2,... from table1
- insert into table1 (field1, field2, ...) values (@field1,...)
- delete from table1 where field1 = @field1
Both tables have clustered indexes on 'em which are hit 100% according to the queryplan. However, the delete brings the sqlserver down to a grinding halt (100% cpu time) moving records by the minute instead of seconds. I could easily delete those records copied afterwards, but it's some sort of a default solution of moving data. It's quite a bunch of data so I'd like to know what's causing it or where to look for.
I tried the with nolock option which doesn't give any other result. The performance monitor barely gives results during the time of the move. As far as I have results, the number of locks/deadlocks, waittime etc. are way below average. The only thing that's up as far as I've seen is the cpu-time.
Like I said: I could do the delete after the select/insert-statements. It's just that I use the select/insert/delete order more often and I'd like to find out why this one is so horribly slow and clogs up my dev.