Post reply

Problem with deleting huge amount of rows

  • July 21, 2009 at 5:07 pm

    #136381

    Hi guys, I need your help

    I'm trying to delete old information from my auditory table from a week (I could prolly try day by day but i need to delete a couple of months and that's a huge task, a week has about 2M rows) but I have a problem, before deleting I use a Begin Transaction statement in case anything goes wrong and then I use the following query:

    delete

    FROM

    dbo.Audit

    WHERE StartTime BETWEEN '20090214 00:00' AND '20090221 00:00'

    AND RowNumber >= 5306311

    AND RowNumber <= 6815695 Reason I don't use only the date is because that's just a non-clustered index and it gives me bookmark lookups and only Rownumber (which is the clustered index) is because for some reason the are some transactions between those numbers that are not from that dates (maybe because sometimes the auditory stopped by server bugs), anyway, while I run this query the auditory keeps inserting information from right now but it gets blocked by the delete running even tho it's accessing just old information (I think so..) Also it takes a lot (around 20min). When I run my execution plan I have the following things that are used: - Non-clustered index: 67%
    - Clustered index: 1% (don't know why is so low)
    - Top: 19% (I don't know why it uses an order)
    - Sequence: 1% (this divides the plan in two ways, one of them has the above objects the other one has the following object)
    - Table Spool/Eager Spool: 49% (I readed a bit about this and found it has to do with temp tables being recorded in tempdb, everyone that had this had a query with temp tables but I don't, anyway it worries me it has a high number).

    Any ideas?

    Thanks!

  • July 21, 2009 at 5:37 pm

    #1028024

    Yep.... do a SELECT into a Temp Table using the date criteria and capture just the row numbers to delete. Then, do a joined delete between the temp table and the audit table. Best thing to do would be to also add a WITH (TABLOCKX)... if you're table is right and the index on the row numbers is actually good, then delete will happen very quickly. Lose the transaction while you're at it. It's just slowing things down.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 21, 2009 at 5:49 pm

    #1028032

    ...and if the tablock isn't enough, limit your deletes to a certain number (which you can determine on execution), and just keep fire the delete off serveral times until the data is all gone.....

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply