Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    492

    Unanswered: SQL Performance down the drain

    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
    - loop

    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.

    thanx,

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto
    Posts
    28
    I think that it is a locking problem, because the delete on table1 is nested inside the select with a cursor on table1.

    You could try a select ... with (nolock) on the cursor select, or better yet try a set based approach instead:

    First an insert
    insert into table2(...)
    select (...) from table1
    from table1

    Then a delete
    delete from table1
    where ........

  3. #3
    Join Date
    Jul 2004
    Posts
    23
    Also, on the set based select limit your clause to top 1000 or similar. It will give the server time to breathe

    First an insert
    insert into table2(...)
    select top 1000 (...) from table1
    from table1

  4. #4
    Join Date
    Feb 2004
    Posts
    492
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •