The SQL Queries which saved Today American Web Site

Here are the queries which were killing the Today American Database and how I fixed them:

(1) Query 1

--3 secs
select * from rssfeedcontents
where ContentId IN
(Select TOP 50 ContentId from UserRssFeedStatus
Where UserId=4 And [Read] = 0
order by ContentId desc)
order by PublishDate desc, ContentId desc

---1 secs
SELECT RFC1.* FROM rssfeedcontents RFC1
INNER JOIN (SELECT TOP 50 ContentId FROM UserRssFeedStatus
WHERE UserId=4 And [Read] = 0
ORDER BY ContentId DESC) RFC2 ON (RFC1.ContentId = RFC2.ContentId)
ORDER BY PublishDate DESC, ContentId DESC

-- 0 secs
SELECT TOP 50 RFC1.* FROM rssfeedcontents RFC1
INNER JOIN UserRssFeedStatus URFS ON (UserId=4 And [Read] = 0 AND RFC1.ContentId = URFS.ContentId)
ORDER BY URFS.ContentId DESC, RFC1.PublishDate DESC

(2) Query 2

--13 secs
select TOP 250 4, ContentId, 0, GETDATE() from rssfeedcontents
where ContentId NOT IN (Select ContentId from UserRssFeedStatus Where UserId=4 And [Read] = 1)
order by PublishDate desc, ContentId desc

--9 secs
SELECT TOP 250 4, RFC.ContentId, 0, GETDATE() FROM rssfeedcontents RFC
LEFT OUTER JOIN (SELECT ContentId FROM UserRssFeedStatus
WHERE UserId=4 And [Read] = 1) URFS ON (URFS.ContentId = RFC.ContentId)
WHERE URFS.ContentId IS NULL
ORDER BY ContentId DESC, PublishDate DESC

--6 secs
SELECT TOP 250 4, RFC.ContentId, 0, GETDATE() FROM rssfeedcontents RFC
LEFT JOIN UserRssFeedStatus URFS ON
(UserId=4 And [Read] = 1 AND URFS.ContentId = RFC.ContentId)
WHERE URFS.ContentId IS NULL
ORDER BY ContentId DESC, PublishDate DESC

(3) Query 3

--3 secs
select * from RssFeedContents
Where ItemTitle IN
(select ItemTitle from DuplicateItems
where itemcount > 1)
AND Contentid NOT IN
(Select DISTINCT ContentId from UserReadRssFeedContents)

--2 secs
select * from RssFeedContents RFC
INNER JOIN DuplicateItems DI ON (RFC.ItemTitle = DI.ItemTitle AND DI.ItemCount > 1)
LEFT JOIN (Select DISTINCT ContentId from UserReadRssFeedContents) URFS
ON (RFC.ContentId = URFS.ContentId)
WHERE URFS.ContentId IS NULL

Today American operational once again

I have been increasingly frustrated by the lack of an alternate to today american. So after a log time, i revisited the code & implemented some fixes which make the site useable again.

Users are welcome to start using the site while i am testing and fixing issues as i find them.

Published with Blogger-droid v1.4.8