Post reply

Optimizing inserts and deletes on a single large table

  • July 5, 2011 at 11:38 pm

    #1348743

    In terms of the delete (and depending on indexes), instead of using "IN" in your where clause, I'd use something like:

    DECLARE @Id INT

    SELECT @Id = MAX(Id) FROM (SELECT TOP 3000 ID FROM MyTable ORDER BY Id)

    DELETE a

    FROM

    MyTable a

    INNER JOIN MyTable b ON b.ID = a.ID AND b.ID < @Id

  • July 7, 2011 at 4:04 am

    #1349531

    Hello,

    as Kevin said your DELETE instruction lacks on a WHERE clause, I suppose you coded something like

    DELETE TOP 3000 FROM table WHERE too_old_record

    Do you need to run this delete process every few minutes?. Maybe you can schedule it to a point in time with few activity and execute it daily or weekly, avoiding run it on stressed working hours.

    Francesc

  • July 7, 2011 at 12:07 pm

    #1349916

    Hey guys, please check the dates on threads you are going to reply to. Please do not reply to 1.5 year old threads! Thx.:-)

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 3 posts - 16 through 17 (of 17 total)

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