performance increase - help needed

[jaggee]

Hello,

This is regarding log analysis of a web system, I am finding my backend SQL programming has taken so much of time to process the application due to following quires for a log table having 500 million records, i have already done index tuning , but no use, so I would like to change the following quires into a single stored procedure in order to reduce Client server network traffic and to increase performance.

Anybody can help me to identify, how to write or how to modify the following T-SQL code to tune high performance Stored Procedure,

This is a query about to identify the 'Countries' and access 'Pages' from a W3C formatted Log DB Table.
- Means which country accessed for a particular web page and date
- Total hit count from particular country for the particular date
- Total hit count (TOP 3) of a page, which was accessed maximum for the particular date

Query A : Selecting Country ID , Country Name and Total Hit Count of particular day
----------------------------------------------------------------
SELECT
NewLog_T.country as countryid,
Level1Domain_M.Country_j as countryname,
COUNT(NewLog_T.csUriStem) as hitcount
FROM
NewLog_T,
Page_M,
Level1Domain_M
WHERE
Level1Domain_M.Level1DomainName = NewLog_T.country AND
SUBSTRING(NewLog_T.date,1,4) = '2005' AND
SUBSTRING(NewLog_T.date,6,2) = '08' AND
SUBSTRING(NewLog_T.date,9,2) = '29' AND
'http://www.testserver.com'+NewLog_T.csUriStem = Page_M.URL
GROUP BY
NewLog_T.country,
Level1Domain_M.Country_j
ORDER BY
hitcount desc;
----------------------------------------------------------------

Query B :
Selecting Page Name, Page Hitcount, Total Access Count (particular country - results from Query A)
and Grand Total Access Count (from all countries)
---------------------------------------------------------------
SELECT TOP 3
Page_M.PageName as pagetitle,
COUNT(NewLog_T.csUriStem) as pagehitcount,
TotalCount.hitcount as totalcount,
GrandTotalCount.hitcount as grandtotalcount
FROM
NewLog_T,
Page_M,
(SELECT
sum(mid_TotalCount.mid_hitcount) as hitcount
FROM
(SELECT
COUNT(NewLog_T.csUriStem) as mid_hitcount
FROM
NewLog_T,
Page_M
WHERE
SUBSTRING(NewLog_T.date,1,4) = '2005' AND
SUBSTRING(NewLog_T.date,6,2) = '08' AND
SUBSTRING(NewLog_T.date,9,2) = '29' AND
'http://www.testserver.com'+NewLog_T.csUriStem = Page_M.URL AND
NewLog_T.country = 'SG' ---------------------> to be set from QUERY A output
GROUP BY
Page_M.PageName
) as mid_TotalCount
) as TotalCount,
(SELECT
sum(mid_GrandTotalCount.mid_hitcount) as hitcount
FROM
(SELECT
COUNT(NewLog_T.csUriStem) as mid_hitcount
FROM
NewLog_T,
Page_M
WHERE
SUBSTRING(NewLog_T.date,1,4) = '2005' AND
SUBSTRING(NewLog_T.date,6,2) = '08' AND
SUBSTRING(NewLog_T.date,9,2) = '29' AND
'http://www.testserver.com'+NewLog_T.csUriStem = Page_M.URL
GROUP BY
Page_M.PageName
) as mid_GrandTotalCount
) as GrandTotalCount
WHERE
SUBSTRING(NewLog_T.date,1,4) = '2005' AND
SUBSTRING(NewLog_T.date,6,2) = '08' AND
SUBSTRING(NewLog_T.date,9,2) = '29' AND
'http://www.testserver.com'+NewLog_T.csUriStem = Page_M.URL AND
NewLog_T.country = 'SG' ---------------------> to be set from QUERY A output
GROUP BY
Page_M.PageName,
TotalCount.hitcount,
GrandTotalCount.hitcount
ORDER BY
pagehitcount desc;
---------------------------------------------------------------

Thanks.