1

I am interested in finding interesting unanswered questions on different SE sites using my UserId.

While I am aware of this SEDE query that exists, the problem with it is that it seems that with larger sites, or when a user hasn't participated in 20+ tags(?)1, the query times out.

My question is:

Does there exist an app for this? Or would I need to create an app/is there no need for there to be an app for this?

If I would need to create an app, I could do that (learning Javascript shouldn't be that difficult), or if there's no need, I could just fork the query and work on optimizing the script.


1Maybe that's why the query times out when I put in my UserId when searching on SO? It seems to work with Jon Skeet's UserId, but I'm not really sure.

1 Answer 1

1

I've improved the SEDE query here.

My main change was reducing the number of rows that had to be spooled in the temporary table #unanswered by joining with the already established tags for the user:

create table #unanswered (Id int primary key)

insert #unanswered 
select distinct q.Id  
from Posts q
inner join posttags pt on pt.postid = q.id 
inner join #tags t on t.tagid = pt.tagid
where (select count(*) from Posts a where a.ParentId = q.Id and a.Score > 0) = 0
and q.CommunityOwnedDate is null 
and q.ClosedDate is null 
and q.AcceptedAnswerId is null

This reduced for your userid the number of rows in that table from 7.446.000 to 9.218 on Stack Overflow.

Do note that the order of the final results are not deterministic when multiple questions end-up with the same weight. It is up to SQL Server in which order questions within the same weight are shown.

1
  • Huh, so that's where the long load times were coming from. I had originally thought (when I was making improvements myself) that the long load times had something to do with how many questions I was retrieving, but I guess that's not what was going on.
    – CrSb0001
    Apr 3 at 13:24

You must log in to answer this question.

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