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 < @someTime AND Id = @someListId AND ( SELECT COUNT(*) FROM SomeListAuxData WHERE SomeList.Id = SomeListAuxData.SomeListId ) > 0 AND ( SELECT MAX(CountData) FROM SomeListAuxData WHERE SomeList.Id = SomeListAuxData.SomeListId ) < @someValue;
Specifically this seem wasteful since I'm doing two subqueries, but I'm not sure how to go about the
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
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.
<edit: Also, I just learned that the code calling does have a list of pairs of type (
@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. :)
<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
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.
<edit 2013-11-08: The join conditions corrected as suggeted in comments.