Page 1 of 4 123 ... LastLast
Results 1 to 15 of 46
  1. #1
    Join Date
    Mar 2004
    Posts
    13

    Unanswered: Urgent! Please Help! Deleting data from a huge table

    I have a huge table with 4 primary keys on it. I need to delete the data from this table ( approx. 5.6 millions records to be deleted). It takes a hell lot of time to delete it by normal query.
    Can someone please suggest me a better way?
    Any help will be appreciated.

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    First thought would be to drop all indexes that are not used by your delete statment, delete the records and then create the old indexes. This eliminates the time needed to update any indexes due to data being deleted. If one of your indexes is a clustered index you might also want to drop thie index and temporarily replace it with a nonclustered index.

    Second thought would be to rename the table in question, create a new table with the original name and then select data from the renamed table to the newly created table. When finished, drop the renamed table.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You mean 1 PK with a 4 column composite...

    And is it all the data or just portions?

    If it's portions of the data. what's the predicate

    Can you post he DDL as well?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Mar 2004
    Posts
    13
    Its a composite primary key on the 4 columns.
    There are around 10 million rows in the table out of which i need to delete 5.6 million. I have to delete on the condition based upon the statusID.
    i.e. Delete from tablename where Statusid = 'D'
    It also has a insert trigger on it.
    There are indexes on 3 columns.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you have the disk space and can schedule downtime to do it, my suggestion would be to:

    1) Create a new table with the same columns
    2) Copy all of the data you want to keep into the new table
    3) Rebuild all of the indicies on the new table
    4) In a single SQL batch:
    4a) Rename the production table safely out of the way
    4b) Rename the new table to the production name
    5) Backup everything
    6) Drop the old table.

    -PatP

  6. #6
    Join Date
    Mar 2004
    Posts
    13
    When to i create my trigger back on the table.
    Is it after i rebuild the indices.

    Here is the Table script.

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trg_CreateRDSDispute]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
    drop trigger [dbo].[trg_CreateRDSDispute]
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DisputeWFAttribute]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[DisputeWFAttribute]
    GO

    CREATE TABLE [dbo].[DisputeWFAttribute] (
    [ProcessID] [char] (32) NOT NULL ,
    [CompID] [char] (3) NOT NULL ,
    [TaskID] [char] (32) NOT NULL ,
    [AttribID] [int] NOT NULL ,
    [AttribName] [varchar] (30) NULL ,
    [AttribValue] [varchar] (7000) NULL ,
    [AttribType] [varchar] (30) NULL ,
    [StatusIDAttrib] [char] (1) NULL ,
    [LastUpdateDate] [datetime] NOT NULL ,
    [Counter] [int] NULL ,
    [DisputeID] [int] NULL ,
    [AcctNo] [varchar] (14) NULL ,
    [AttribAction] [varchar] (10) NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[DisputeWFAttribute] WITH NOCHECK ADD
    CONSTRAINT [PK_DisputeWFAttribute] PRIMARY KEY NONCLUSTERED
    (
    [ProcessID],
    [CompID],
    [TaskID],
    [AttribID]
    ) WITH FILLFACTOR = 90 ON [PRIMARY]
    GO

    CREATE INDEX [IDX_DispAction] ON [dbo].[DisputeWFAttribute]([DisputeID], [AttribAction]) WITH FILLFACTOR = 90 ON [PRIMARY]
    GO

    CREATE INDEX [IDX_DisputeComp] ON [dbo].[DisputeWFAttribute]([DisputeID], [CompID]) WITH FILLFACTOR = 90 ON [PRIMARY]
    GO

    GRANT SELECT ON [dbo].[DisputeWFAttribute] TO [public]
    GO

    GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[DisputeWFAttribute] TO [ProdSupp]
    GO

    GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[DisputeWFAttribute] TO [Web]
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It doesn't matter...

    as for the indexes, I think I'd do:

    Code:
    CREATE INDEX [IDX_1] ON [dbo].[DisputeWFAttribute]([DisputeID]) WITH FILLFACTOR = 90 ON [PRIMARY]
    GO
    
    CREATE INDEX [IDX_2] ON [dbo].[DisputeWFAttribute]([AttribAction]) WITH FILLFACTOR = 90 ON [PRIMARY]
    GO
    
    
    CREATE INDEX [IDX_3] ON [dbo].[DisputeWFAttribute]([CompID]) WITH FILLFACTOR = 90 ON [PRIMARY]
    GO
    And utilize index intersection...

    What's a common DML statement look like?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Oct 2002
    Posts
    66
    Have you taken a look at the TRUNCATE TABLE command?

  9. #9
    Join Date
    Mar 2004
    Posts
    13
    didnt get you. What do you mean by the truncate table commmand. Can you please explain it to me.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by amesjustin
    Have you taken a look at the TRUNCATE TABLE command?
    Nah..

    TRUNCAT TABLE TableName

    Empties the entire table...

    It's a limited log operation in that it doesn't log each row...it just marks the pages....

    Did you get Pats jive...think that's the most effecient way...

    When you create the new table, make sure you use

    SELECT * INTO newTable FROM oldTable WHERE conditions you want to keep
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Oct 2002
    Posts
    66
    TRUNCATE TABLE <MyTableName> immediatley flushes out the table entirely - much faster than any query. Check it out in BOL

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by amesjustin
    TRUNCATE TABLE <MyTableName> immediatley flushes out the table entirely - much faster than any query. Check it out in BOL
    They are not deleting all the rows....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Oct 2002
    Posts
    66
    Originally posted by Brett Kaiser
    They are not deleting all the rows....
    Whoops. I guess I should read everything before I respond. Sorry about that!

    I agree that Pats solution is the best....

  14. #14
    Join Date
    Mar 2004
    Posts
    13
    Even i think pats solution would be the right thing to do. Will i have to recompile the stored procedures which refer to the original prod. table once it is dropped.

  15. #15
    Join Date
    Mar 2004
    Posts
    45
    Use Bulk-Logged recovery model. Bcp out data (raw format) you keep. Truncate table. Bcp data in to table.

Posting Permissions

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