Delete too long

  • I am running hte following query to delete since 7 days its not yet finished, any thoughts ? and the number of records involved are 56734591

    set nocount on

    SET ROWCOUNT 200

    WHILE 1 = 1

    BEGIN

    delete from dbo.RevenueT where Cdate>='2004-01-01'

    IF @@ROWCOUNT = 0

    BREAK

    END

    set rowcount 0

  • Tara-1044200 (6/25/2010)


    I am running hte following query to delete since 7 days its not yet finished, any thoughts ? and the number of records involved are 56734591

    set nocount on

    SET ROWCOUNT 200

    WHILE 1 = 1

    BEGIN

    delete from dbo.RevenueT where Cdate>='2004-01-01'

    IF @@ROWCOUNT = 0

    BREAK

    END

    set rowcount 0

    Looks like you are trying to delete 56+ million rows 200 at a time, this may take awhile. May I suggest that you increase the size of your batch? Also, you may want to read this article: Deleting Large Number of Records[/url].

    The way you are using ROWCOUNT has been depreciated and may not be supported in future versions of SQL Server.

  • Do read Lynn's article - and make sure there's a useful index (leading on cdate).

    If you are deleting a substantial proportion of the rows in the table, you might find it easier to bulk copy out the rows to keep, truncate the table, and bulk load the saved rows back in. If you are careful to follow the rules for minimally-logged bulk operations, this could be a very fast alternative.

  • Tara-1044200 (6/25/2010)


    I am running hte following query to delete since 7 days its not yet finished, any thoughts ? and the number of records involved are 56734591

    set nocount on

    SET ROWCOUNT 200

    WHILE 1 = 1

    BEGIN

    delete from dbo.RevenueT where Cdate>='2004-01-01'

    IF @@ROWCOUNT = 0

    BREAK

    END

    set rowcount 0

    How long it takes to find 200 records in your table based on the given condition (Cdate>='2004-01-01')? Does it takes more than 2 seconds?

    If so, why you surprised? To delete 56734591 records in 200 records per batch, it needs to be done 283,672 times!

    Check the current count in your table (try it as "select rowcnt from sysindexes where [id]=object_id('tally') and indid in (0,1)", I hope your index/table are not partitioned), is it reduces over time (you can mesure how long it takes)? If it does go down then your query is still working and doing its best 😀

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • As asid above how do i do a BULK INSERT from table to table very fast, that sounds like a good approach.

  • Paul does bring up an interesting point, how many records are you keeping in the table?

  • Tara-1044200 (6/25/2010)


    As asid above how do i do a BULK INSERT from table to table very fast, that sounds like a good approach.

    SELECT * INTO NewTable FROM OldTable [WHERE ....]

    Depending on your server recovery model ("Simple" is the best for this, but not all DBAs will like it enough to allow it in production 😀 ), it can be very fast.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Lynn Pettis (6/25/2010)


    Paul does bring up an interesting point...

    It has been known 😀

    I'm off to bed (3:20am!), so I'll leave Tara in your capable hands for the bulk load stuff. Have fun 🙂

  • thank you guys i am going to try bulk insert now.

  • Ok if i am doing a "select into " from a partition table and i think it will not create the same paritioned table and os i have to "insert into select "

  • Tara,

    How many records will be left in the table after the delete?

    Lynn asked that before but I think you missed it.

    Run the following query to give the answer:

    select count(*) from dbo.RevenueT where Cdate < '20040101';

    There is also another very efficient way to delete rows from a partitioned table - if the partitioning is based on Cdate. It would help a lot if you could provide the CREATE TABLE definition for the table, and the definition of the partitioning scheme and function too. It would also be nice to know if the table is referenced by any foreign keys.

    Thanks

  • Paul

    HEre is the DDL you have asked and there are no reference keys. Please let me know if there is any fastest way to delete records.

    CREATE DATABASE [RevBud] ON PRIMARY

    ( NAME = N'RevBud', FILENAME = N'D:\DATA\RevBud.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),

    FILEGROUP [2004_01_Bud]

    ( NAME = N'2004_01_$$', FILENAME = N'D:\DATA\2001_01_Bud.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),

    FILEGROUP [2004_02_Bud]

    ( NAME = N'2004_02_$$', FILENAME = N'D:\DATA\2001_02_bud.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),

    ................................................................

    FILEGROUP [2018_12_Bud]

    ( NAME = N'2018_12_Bud', FILENAME = N'D:\DATA\2018_12_Bud.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )

    LOG ON

    ( NAME = N'RevBud_log', FILENAME = N'D:\LOG\RevBud_log.ldf' , SIZE = 61115584KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

    CREATE PARTITION FUNCTION Part_Fn_Month_Bud (smalldatetime)

    ASRANGE RIGHT FOR VALUES (

    '2001-01-01','2001-02-01','2001-03-01','2001-04-01', '2001-05-01','2001-06-01',

    '2001-07-01', '2001-08-01', '2001-09-01','2001-10-01','2001-11-01', '2001-12-01',

    ..................................................................

    '2018-01-01', '2018-02-01','2018-03-01', '2018-04-01', '2018-05-01', '2018-06-01',

    '2018-07-01', '2018-08-01', '2018-09-01','2018-10-01','2018-11-01', '2018-12-01' )

    CREATE PARTITION SCHEME Part_Schm_Month_Bud

    AS PARTITION Part_Fn_Month_Bud TO (

    '2001_01_Bud','2001_02_Bud','2001_03_Bud','2001_04_Bud','2001_05_Bud','2001_06_Bud',

    '2001_07_Bud', '2001_08_Bud','2001_09_Bud','2001_10_Bud', '2001_11_Bud', '2001_12_Bud',

    ......................................................

    '2018_01_Bud','2018_02_Bud','2018_03_Bud','2018_04_Bud', '2018_05_Bud','2018_06_Bud',

    '2018_07_Bud', '2018_08_Bud','2018_09_Bud','2018_10_Bud', '2018_11_Bud', '2018_12_Bud', 'PRIMARY')

  • Table definition?

    Row count?

  • Paul

    The definition very simple which consists of 53 columns with varchar(16) and 4 datetime fields and the row count is 345039256

  • Lynn Pettis (6/25/2010)


    The way you are using ROWCOUNT has been depreciated and may not be supported in future versions of SQL Server.

    Lynn,

    Did you mean to say depreciated? Or did you maybe mean deprecated?

Viewing 15 posts - 1 through 15 (of 16 total)

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