Hi there,
I have a SQL query which keeps timing out when I run it and I'm hoping that there's a more efficient way of doing it which will prevent this from happening.
About the query
In the company I work for, they measure the number of hits per article. The URL and hits are recorded in one table and the title and category of the article etc is stored in another. Also, the stats have been set up so that there is a new table of hits for each month.
So, I have 12 tables, and I want to stick them together, one on top of the other - to get all the hits in one big table. Then, I'd like to join the title, category etc onto that big hits table and group everything so that I get the number of hits per URL.
Here's the query. It works if I put TOP 5 in every select statement, but I get a [Microsoft][ODBC SQL Server Driver]Timeout expired message if I select whole tables.
Code:
SELECT *, COUNT(*) AS Hits
FROM (SELECT All_News.URL AS URL, dbo.Manu_News.Primary_Topic AS Category, dbo.Manu_News.Message_Title AS Title,
dbo.Manu_News.Message_Date AS PublishDate
FROM (SELECT dbo.news_stats_1.url AS URL, dbo.news_stats_1.messageID AS messageID
FROM dbo.news_stats_1
UNION ALL
SELECT dbo.news_stats_2.url AS URL, dbo.news_stats_2.messageID AS messageID
FROM dbo.news_stats_2
UNION ALL
SELECT dbo.news_stats_3.url AS URL, dbo.news_stats_3.messageID AS messageID
FROM dbo.news_stats_3
UNION ALL
SELECT dbo.news_stats_4.url AS URL, dbo.news_stats_4.messageID AS messageID
FROM dbo.news_stats_4
UNION ALL
SELECT dbo.news_stats_5.url AS URL, dbo.news_stats_5.messageID AS messageID
FROM dbo.news_stats_5
UNION ALL
SELECT dbo.news_stats_6.url AS URL, dbo.news_stats_6.messageID AS messageID
FROM dbo.news_stats_6
UNION ALL
SELECT dbo.news_stats_7.url AS URL, dbo.news_stats_7.messageID AS messageID
FROM dbo.news_stats_7
UNION ALL
SELECT dbo.news_stats_8.url AS URL, dbo.news_stats_8.messageID AS messageID
FROM dbo.news_stats_8
UNION ALL
SELECT dbo.news_stats_9.url AS URL, dbo.news_stats_9.messageID AS messageID
FROM dbo.news_stats_9
UNION ALL
SELECT dbo.news_stats_10.url AS URL, dbo.news_stats_10.messageID AS messageID
FROM dbo.news_stats_10
UNION ALL
SELECT dbo.news_stats_11.url AS URL, dbo.news_stats_11.messageID AS messageID
FROM dbo.news_stats_11
UNION ALL
SELECT dbo.news_stats_12.url AS URL, dbo.news_stats_12.messageID AS messageID
FROM dbo.news_stats_12) All_News LEFT OUTER JOIN
dbo.Manu_News ON All_News.messageID = dbo.Manu_News.Message_Id) DERIVEDTBL
GROUP BY URL, Category, Title, PublishDate
It would be great if anyone can suggest a solution to this!
Many thanks,
Katie
Add to del.icio.us 
Can you digg it?
Linear Mode
