10 Words Likely To Get Post Closed as Non-Constructive - Too Slow

1

Please login or register to vote for this query.

(click on this box to dismiss)

This is a newer query to return the list of words that have the highest ratio of being used in 'closed as non-constructive' vs all posts. Although I've tried two different methods of splitting, the sheer amount of data and my rustiness with SQL optimization is causing all the methods I have tried to timeout, even when doing a pre-query for just words in closed posts. Currently running most recent 25% of posts and sorting by ratio desc, so words with largest chance of being closed.

Stack Overflow

Q&A for professional and enthusiast programmers

SET STATISTICS TIME ON

CREATE TABLE #Numbers ( n int NOT NULL );
INSERT INTO #Numbers
SELECT TOP 250 ROW_NUMBER() OVER (Order By Id) FROM Posts;

print 'here';

CREATE TABLE #Titles (Title nvarchar(250) NOT NULL, Closed bit NOT NULL );

INSERT INTO #Titles (Title, Closed)
SELECT TOP 100001
       ' ' + LOWER(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(P.Title, ',', ' '), '?', ''), '  ',' '), '/', ''), ':', ''), '"', ''), '(', ''), ')', ''), '''', '')) + ' ', 
       CASE WHEN PH.id IS NOT NULL THEN 1 ELSE 0 END
FROM Posts P
   LEFT JOIN PostHistory PH ON PH.PostId = P.Id AND PH.Comment = '3'
WHERE P.Title IS NOT NULL;

print 'here 2';

CREATE TABLE #WordFrequency ( Word nvarchar(250) NOT NULL, TotalNum int NOT NULL, NumClosed int NOT NULL);


print 'here 4';

DROP TABLE #WordFrequency;
DROP TABLE #Titles;
DROP TABLE #Numbers;

SET STATISTICS TIME OFF

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results