What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 127 Stack Exchange communities.

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;

The 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.

share|improve this question
    
Wow! Why the downvote? –  John Saunders Oct 10 '13 at 17:50
    
It used to be better. But the DE hardware got downgraded in July when it moved from Oregon to New York. Right now any sort of vote joining will almost always time out. –  Mysticial Oct 10 '13 at 18:05
4  
@JohnSaunders: Some people just like to downvote; it's their hobby. Here and on Programmers, it's not uncommon for me to have posts that have a dozen or more upvotes, and one single downvote. –  Robert Harvey Oct 10 '13 at 19:18
    
Even if there were indexes on what you suggest you're still going to be picking up a significant amount of the tables so can't avoid the full-scan necessarily. –  ben is uǝq backwards Dec 19 '13 at 21:24
add comment

1 Answer

up vote 9 down vote accepted

As Mystical mentions, part of the problem is that Data Explorer is still currently running on less-than-optimal hardware*, so there's only so much it can cope with. Data Explorer has been moved to new hardware back in Oregeon, so hopefully this should improve the performance of most queries since more resources will now be available.

Of course, this is typically not an issue for a well-behaved query…only unfortunately yours is not (through no fault of your own, to be clear). None of the indexes which you believed to exist actually do, so your query boils down to two full table scans** on reasonably large tables (~16.5 million post records, and ~49 million vote records) with, in the case of the Posts table, a reasonably heavy where clause.

In my local environment which has about half the above-mentioned data, the query takes 40 seconds to run. Given that the query timeout on the public SEDE instance is two minutes and running queries there involves competing for presumably limited resources, it's not surprising that the query times out.

While the dynamic nature of Data Explorer queries negates the benefit of adding most indexes, there's likely a little room to add a few to support common query cases on the largest of tables. It would take some analysis of the type of queries people are usually running to decide if this would be appropriate though, and then Nick would have to agree to add them/make sure rebuilding them each week didn't put too much strain on the automated load process.

Consequently I can't make any promises, but hopefully something can be improved, since running most queries against Stack Overflow's data is problematic at the moment.

* Or at least I haven't heard otherwise, but I haven't asked about it recently either
** They're technically clustered index scans, but the point is that the query has to touch all records

share|improve this answer
add comment

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .