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 have the following Data Explorer query which attempts to compute the number of characters per reputation sorted in descending order for each user. Think of it as a silly level of effort metric. Who typed the most number of characters for each reputation point earned? Just a fun little comparison.

select top 10    /* 100 would be better */
    u.Id,
    u.DisplayName,
    min(u.Reputation) as Reputation,
    sum(len(p.Body)) as TotalLength,
    count(*) as TotalPosts,
    avg(len(p.Body)) as AvgPostLength,
    (cast(sum(len(p.Body)) as float) / cast(min(u.Reputation) as float)) as PostLengthPerReputation
from
    Posts p
    inner join Users u ON u.Id = p.OwnerUserId
where
    u.Reputation > 100000   /* 1000 would be better */
group by
    u.Id, u.DisplayName
order by
    PostLengthPerReputation desc

The problem is that I would like to do a top 10 (or even 100) for users above say 10,000 or even 1,000 reputation, but the query will timeout. I did get it to return for users with reputation > 100,000, but that is not very interesting. Anyone have any ideas on how to get this to work?

share|improve this question
10  
Huh? That's on topic on Stack Overflow and Meta Stack Overflow! There should be a badge for that... –  Yannis Apr 20 '12 at 20:23
1  
Ugggh.... don't use float for math like this!! –  JNK Apr 20 '12 at 20:38
    
Depending on the plan you're getting, putting the contents of the where clause into the join might nudge the optimiser into a more efficient plan. Of course, I don't think you can actually get a query plan from data.so ;-) –  ConcernedOfTunbridgeWells Apr 20 '12 at 20:38
    
@ConcernedOfTunbridgeWells There is an "Include execution plan" checkbox on there, actually. –  jadarnel27 Apr 20 '12 at 20:54
    
@JNK: Oh really? Why not? Is there a better way? –  Brian Gideon Apr 20 '12 at 20:54
    
@BrianGideon It runs for me (without timing out) with CorncernedOfTurnbridgeWells suggestion of moving the WHERE clause into the JOIN. Here is a revised version. –  jadarnel27 Apr 20 '12 at 20:56
    
@jadamel27 - that's useful. I never noticed it before. However the stats I was doing mostly came from dba.se, so the volumes weren't terribly great. –  ConcernedOfTunbridgeWells Apr 20 '12 at 21:07
    
@jadarnel27: Yeah, I just did the same thing and got it to work. But, now it's being really spotty again :( –  Brian Gideon Apr 20 '12 at 21:09
    
Wow, this guy had to type 875 characters for each rep point. –  Brian Gideon Apr 20 '12 at 21:10
    
@YahooAnswersenthusiast Ooh can we get SO rep on a question like that –  Some Helpful Commenter Apr 20 '12 at 21:13
    
@BrianGideon on the opposite side this guy got more than an 100 rep points per character See how you rank here –  Some Helpful Commenter Apr 20 '12 at 21:27
    
@SomeHelpfulCommenter: Ooo...that's cool. –  Brian Gideon Apr 20 '12 at 23:52
add comment

You must log in to answer this question.

Browse other questions tagged .