Post reply

How can I delete data from a table that has 750000 rows in it fast without using truncate?

  • November 28, 2006 at 12:28 pm

    #115287

    Hi,

    How can I delete data from a table what has more a million rows in it fast? I can't use truncate, since I have to have a where clause in the delete?

    thanks

    Newbie

     

  • November 28, 2006 at 12:32 pm

    #674489

    How much data is there in the table?

    How much do you plan to delete/keep (pourcent (%))?

     

    Do you have a window to do this?

  • November 28, 2006 at 12:38 pm

    #674493

    You might consider loading only the rows you need into another table, dropping and recreating the original table, and then copying the data back over. Or just renaming the holding table.

  • November 28, 2006 at 12:40 pm

    #674496

     750000 rows. This delete is the beginning of a application event. I want to be able to delete the rows by a passed in parameter . That is why I can't use truncate table. Currently to delete this amount of data took me 5 mins even with the primary key defined on the table.

    SO I want to delete data based on the passed in parameter

  • November 28, 2006 at 12:45 pm

    #674498

    Thanx for the info.

    Would you care to answer my previous questions as they will help me point you out to the best possible solution instead of listing 'em all.

     

    Also does this delete absolutely have to be completed before the application is loaded, or can this process be done after hours in the night, or even has a continuous job?

  • November 29, 2006 at 7:35 am

    #674648

    How about something like this:

    DECLARE @varname Varchar(10)

    SET ROWCOUNT 5000 -- Set this to however many rows you want to delete at a time

    WHILE 1 = 1

    BEGIN

    DELETE FROM dbo.table1 WHERE column1 = @varname -- original delete statement

    IF @@rowcount < 5000

    BREAK

    END

    SET ROWCOUNT 0

    This code will basically delete 5000 rows at a time, and keep doing it until all the rows based on your WHERE clause are deleted. This can be made into a stored procedure and called as a nightly job or for whatever duration you need.

  • November 29, 2006 at 9:35 am

    #674704

    5 minutes seems like a long time to delete only 750,000 rows. Are the rows big (i.e., how many rows per page)? Might there be issues with having to grow the transaction log file to accomodate the deleted data? Are there foreign key constraints between this and other tables? How many indexes are on the table? Any delete triggers (or, god forbid, cascading deletes)? Are the physical components (server, hard drives) not the most efficient?

    Philip

  • November 29, 2006 at 9:42 am

    #674708

    Lol, see now why I didn't try to answer the question without all the info... didn't have time to write a whole article for this one .

  • November 29, 2006 at 11:22 am

    #674754

    Thanks for all the response. I tried Eric's method and combined with other checking in the beginning of the delete. It is working now.

  • November 29, 2006 at 3:45 pm

    #674819

    hi.Anyone help.... I have a question about creating a table to mimic a fix length queque. Thank you very much.

    For example, table like (quencialId int, symbol char(6), dt dateime, price money)..

    data : 1. AUDCHF, 16:04:30, 0.945

    2. AUDCHF, 15:58:01, 0.947

    ....

    2000. AUDCHF, 10:01:01,0.9481

    1. EURAUD, 16:04:31, 1.678

    .....

    2. EURAUD, 10:01:01, 1.681

    .....

    When new row insert , the data would be on the top one, and delete oldest one.

    is there any better way to create a table to mimic a queque in SQL 2000

    Thankx

  • November 29, 2006 at 4:12 pm

    #674820

    F Chen,

    Your post has nothing to do with this thread. Please start a new thread with your question.

    -SQLBill

Viewing 11 posts - 1 through 10 (of 10 total)

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