0
ALTER TABLE dbo.TableA ADD  CONSTRAINT [PK_composite] PRIMARY KEY CLUSTERED 
(
    col1 ASC,
    col2 ASC,
    col3 ASC,
    col4 ASC,
    
)WITH (PAD_INDEX = OFF, 
       STATISTICS_NORECOMPUTE = OFF, 
       SORT_IN_TEMPDB = OFF, 
       IGNORE_DUP_KEY = OFF, 
       ONLINE = OFF, 
       ALLOW_ROW_LOCKS = ON, 
       ALLOW_PAGE_LOCKS = ON) 
GO

Sample table data

col1 col2 col3 col4 col5 col6 col7 
       1
       1
       2
       2
       3
       4
       5
       5
       5
   

TableA contains over 1.2 billion rows . we have to delete all values older than say col2 <7700 ( which is more than half of the rows in the table) . On average to delete 5 million rows it takes one hour. is there a way this can be made run faster . The only index is on the table is clustered index and the table is in a database an AG .

showing here part of the code where col2=5 matches 5 million rows and the delete takes 1 hour and 4 minutes

 DELETE TOP (@BATCHSIZE)
   FROM dbo.TableA
  WHERE col2=5  --example
3
  • 1
    You could follow this approach. Jul 22 at 14:30
  • Okay Erik , Thank you.I'll try that
    – yegnasew
    Jul 22 at 14:54
  • Additionally to @ErikDarling's comment you should create a nonclustered index on col2 column Jul 22 at 15:05
0

You could partition the table on col2. This would limit the scan, or even permit truncating whole partitions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.