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.countr y as countryid,
Level1Domain_M. Country_j as countryname,
COUNT(NewLog_T. csUriStem) as hitcount
FROM
NewLog_T,
Page_M,
Level1Domain_M
WHERE
Level1Domain_M. Level1DomainNam e = NewLog_T.countr y AND
SUBSTRING(NewLo g_T.date,1,4) = '2005' AND
SUBSTRING(NewLo g_T.date,6,2) = '08' AND
SUBSTRING(NewLo g_T.date,9,2) = '29' AND
'http://www.testserver. com'+NewLog_T.c sUriStem = Page_M.URL
GROUP BY
NewLog_T.countr y,
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.hitc ount as totalcount,
GrandTotalCount .hitcount as grandtotalcount
FROM
NewLog_T,
Page_M,
(SELECT
sum(mid_TotalCo unt.mid_hitcoun t) as hitcount
FROM
(SELECT
COUNT(NewLog_T. csUriStem) as mid_hitcount
FROM
NewLog_T,
Page_M
WHERE
SUBSTRING(NewLo g_T.date,1,4) = '2005' AND
SUBSTRING(NewLo g_T.date,6,2) = '08' AND
SUBSTRING(NewLo g_T.date,9,2) = '29' AND
'http://www.testserver. com'+NewLog_T.c sUriStem = Page_M.URL AND
NewLog_T.countr y = 'SG' ---------------------> to be set from QUERY A output
GROUP BY
Page_M.PageName
) as mid_TotalCount
) as TotalCount,
(SELECT
sum(mid_GrandTo talCount.mid_hi tcount) as hitcount
FROM
(SELECT
COUNT(NewLog_T. csUriStem) as mid_hitcount
FROM
NewLog_T,
Page_M
WHERE
SUBSTRING(NewLo g_T.date,1,4) = '2005' AND
SUBSTRING(NewLo g_T.date,6,2) = '08' AND
SUBSTRING(NewLo g_T.date,9,2) = '29' AND
'http://www.testserver. com'+NewLog_T.c sUriStem = Page_M.URL
GROUP BY
Page_M.PageName
) as mid_GrandTotalC ount
) as GrandTotalCount
WHERE
SUBSTRING(NewLo g_T.date,1,4) = '2005' AND
SUBSTRING(NewLo g_T.date,6,2) = '08' AND
SUBSTRING(NewLo g_T.date,9,2) = '29' AND
'http://www.testserver. com'+NewLog_T.c sUriStem = Page_M.URL AND
NewLog_T.countr y = 'SG' ---------------------> to be set from QUERY A output
GROUP BY
Page_M.PageName ,
TotalCount.hitc ount,
GrandTotalCount .hitcount
ORDER BY
pagehitcount desc;
---------------------------------------------------------------

Thanks.