Delete rows from a large table with joins and aggregating (resulting in 1 % less rows in the big table) I got a bit stuck on performance, so I thought about asking a bit of help. I have a working query, but unfortunatelly it feels rather slow and I know it's not as performant as it could be made (easily?). The situation is to delete rows from a table which contain around one million rows, by doing a join to another table likewise having around a million rows worth of data (these tables have about the same amount of rows). The result after removing rows from SomeList is that there's about 1 % less rows

The table structure is as follows

CREATE TABLE SomeList
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    TimeData DateTime NOT NULL
);

CREATE TABLE SomeListAuxData
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    CountData INT NOT NULL,
    SomeListId INT FOREIGN KEY REFERENCES SomeList(Id) ON DELETE CASCADE
);


And the query to delete as follows

DELETE
FROM SomeList 
WHERE TimeData &lt; @someTime AND Id = @someListId AND
(
    SELECT COUNT(*)
    FROM SomeListAuxData
    WHERE SomeList.Id = SomeListAuxData.SomeListId
) &gt; 0
AND
(
    SELECT MAX(CountData)
    FROM SomeListAuxData
    WHERE SomeList.Id = SomeListAuxData.SomeListId
) &lt; @someValue;


Specifically this seem wasteful since I'm doing two subqueries, but I'm not sure how to go about the COUNT(*) and MAX(CountData) parts if I'd try joining, for instance.

This query is made in a program code where the someListIds are looped with same the @someValue (e.g. 2000) and @someTime (e.g. '2013-11-07 09:00:00.000') dates, so that too is rather slow. The query could be done for all the SomeList rows at once.

&lt;edit: Also, I just learned that the code calling does have a list of pairs of type (@someListId, @someDate), which means the date isn't a constant. The input comes from the user interfaces and typically there may be even hundreds of such pairs.

Also, maybe scheduling a job at night may do the job. Though I'll need to checks Craig's advice which seem to be valid too. :)

&lt;edit: I clarified what I meant by the "1 %" in my question. The point to make was that I don't think the data warrants collecting the non-removable rows to a #temptable, truncating SomeList and then moving the data from #temptable" back to SomeList. Craig's good point answered (partially) a different question, nevertheless it may be applicable. Sorry for my sloppy writing.

&lt;edit 2013-11-08: The join conditions corrected as suggeted in comments.
Since you are already referencing the SomeList table from the SomeListAuxData table, why not add "ON DELETE CASCADE" to the foreign constraint definition on SomeListAuxData?

CREATE TABLE SomeListAuxData
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    CountData INT NOT NULL,
    SomeListId INT FOREIGN KEY REFERENCES SomeList(Id) ON DELETE CASCADE
);


This way, if you delete record with an id of 1 in the SomeList table, any record in SomeListAuxData referencing record 1 will be deleted as well.

As to deleting a % of rows at a time, I like to use a loop. This one deletes 10,000 records at a time, until there are none left.

delete top (10000) from SomeList WHERE TimeData &lt; @someTime AND Id = @someListId
  WHILE @@ROWCOUNT &gt; 0
  BEGIN
        waitfor delay '00:00:05' -- add in a delay optional

        delete top (10000) from SomeList WHERE TimeData &lt; @someTime AND Id = @someListId

  END


Update 14:09

Using the TOP operator, you can also specify a percent of rows.

DELETE TOP(1) PERCENT
FROM SomeList 
WHERE TimeData &lt; @someTime AND Id = @someListId AND
(
    SELECT COUNT(*)
    FROM SomeListAuxData
    WHERE SomeListAuxData.Id = SomeListAuxData.SomeListId
) &gt; 0
AND
(
    SELECT MAX(CountData)
    FROM SomeListAuxData
    WHERE SomeListAuxData.Id = SomeListAuxData.SomeListId
) &lt; @someValue;