SQL Server Performance Forum – Threads Archive

performance increase – help needed

Hello, This is regarding log analysis of a web system, I am finding my backend SQL Server 2000 T-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.

Hi Why do you split the date and validate in where clause.
Can you compare directly
where NewLog_T.date=’2005-08-29′. and using inner joins something like FROM
NewLog_T
inner join Level1Domain_M on
Level1Domain_M.Level1DomainName = NewLog_T.country

To the performances of the query, not only the coding style but also indexes also play an huge role. Why don’t you try with Index tuning Wizard

Thanks for the replies so far.. ranjitjain, i split the date field beacuse i have to validate 3 different input cases/conditions from screen,
Case 1:
SUBSTRING(NewLog_T.date,1,4) = ‘2005’ AND
SUBSTRING(NewLog_T.date,6,2) = ’08’
Case 2:
SUBSTRING(NewLog_T.date,1,4) = ‘2005’ AND
SUBSTRING(NewLog_T.date,6,2) = ’08’ AND
SUBSTRING(NewLog_T.date,9,2) = ’29’
Case 3:
CONVERT(CHAR(10),NewLog_T.date,112) >= CONVERT(CHAR(10),REPLACE(‘2005/08/29′,’/’,’-‘),112) AND
CONVERT(CHAR(10),NewLog_T.date,112) <= CONVERT(CHAR(10),REPLACE(‘2005/08/29′,’/’,’-‘),112) dineshasanka,
– already indexed using Index tuning wizard any other solutions..
Thanks.

Hi,
Still not getting the valid reason to split the date.
Firstly you check for year and check for month and finally for date so what’s new in this…..
Why i’m stressing as i think the substring is avoiding the QA to use the available indexes as you are splitting with substring.

the date field in DB for ex<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> ‘2005-08-29′, but in screen i have following conditions case 1 : records to be fetched for the particular year and month (2005,0<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> case 2 : records to be fetched for the particular date (2005-08-29) case 3 : records to be fetched between 2 dates (2005/08/29) so for case 1 i have to split the date but other cases i can able to use with out split as you mentioned. noted the point – substring avoids indexes …

How the table Page_M is related with other tables.
And i hope You have index on Page_M.URL.
Have you considered changing the code to use inner joins instead in where clause.
Also change the style of comparing the two dates to use between operator.
I think ithis makes the query more sargable. like
where
NewLog_T.date between ‘2005/08/29’ AND ‘2005/08/29’

<blockquote id="quote">quote:<hr height="1" noshade id="quote">case 1 : records to be fetched for the particular year and month (2005,0<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><hr height="1" noshade id="quote"></blockquote id="quote"><pre id="code">where date between yymm01 and yymm31 –or whichever is last day of the month</pre id="code">Of course this is just idea, you’ll have to actually pass @monthStart and @monthEnd variables and to fill them properly.

]]>