Fastest way to delete from a large table

Question

Post reply

Fastest way to delete from a large table

skaggs.andrew

SSCrazy

Points: 2476
More actions
May 14, 2014 at 8:08 am

#307946

Hi,
I want to remove only certain records from my table. Currently, my table has roughly 40 million records and I am looking to delete 5 million records. When I write a simple DELETE FROM WHERE statement, it takes about an hour to complete this deletion. I am currently testing a new SQL statement, but I am not sure if this is any better:
while 1=1
begin
set rowcount 1000
SELECT * FROM APSB_FACT
WHERE SCHOOL_YEAR_KEY=(SELECT MAX(SCHOOL_YEAR_KEY) FROM APSB_STAR..APSB_FACT) and HZ_TEST_KEY <> -9
if @@rowcount < 1 break
end
Are there any suggestions as to something I can use that is more efficient?
Please let me know.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

ScottPletcher SSC Guru Points: 101120 More actions · Answer 1

What is the clustering key on the table?

Can you determine a range of clustering key values for the rows you want to DELETE?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

skaggs.andrew SSCrazy Points: 2476 More actions · Answer 2

Im not really sure how to go about getting the range of keys that I want deleted.

ScottPletcher SSC Guru Points: 101120 More actions · Answer 3

What is the clustering key on the table?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

skaggs.andrew SSCrazy Points: 2476 More actions · Answer 4

This could be what is confusing me. This is the clustered index I have on the table and there are also several other nonclustered indices.

USE [APSB_STAR]

GO

/****** Object: Index [_dta_index_APSB_FACT_c_12_1026102696__K9_K8_K10_K5_K4] Script Date: 05/14/2014 09:36:37 ******/

CREATE CLUSTERED INDEX [_dta_index_APSB_FACT_c_12_1026102696__K9_K8_K10_K5_K4] ON [dbo].[APSB_FACT]

(

[SCHOOL_KEY] ASC,

[STUDENT_KEY] ASC,

[STATE_TEST_KEY] ASC,

[HZ_SUBJECT_COURSE_KEY] ASC,

[HZ_STANDARD_KEY] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Maybe you aren't explaining this well or I just don't get it, but what are you trying to delete? You show us a select statement and the DDL for an index but nothing else. It is hard for me to work with the limited information provided. I can't see from here the things you see there.