I've written two queries which are concerned in some way with delete votes, and in both cases, they are too slow. They both time out if I don't use
TOP n at various points in the query. For the first query, see "Query Which Omits Questions I've Voted to Delete", but the second is
Old Closed Questions with No Delete Votes:
; WITH OLDCLOSED AS ( SELECT * FROM Posts AS p WHERE p.CreationDate < DATEADD(YY, -##years##, GETDATE()) AND p.ClosedDate IS NOT NULL AND p.AnswerCount = 0 AND p.PostTypeId = 1 ), DELETEVOTES AS ( SELECT * FROM Votes AS v WHERE v.VoteTypeId=10 ) SELECT top 10 * FROM OLDCLOSED AS o LEFT OUTER JOIN DELETEVOTES AS v ON v.PostId = o.Id WHERE v.Id IS NULL;
DELETEVOTES CTE was originally just a join with the additional join condition of v.VoteTypeId. That version timed out too.
I would have thought there would be an index on Votes.PostId, and maybe also on Votes.VoteTypeId, so I would have thought this query would be fairly fast. Please show me where I'm wrong.