Suggestion for SQL performance
Everytime the user visits the page I log it with logtime, and now I want to find the latest distinct 100 pages that 开发者_如何学Pythona user with ID "MyID".
I use the following SQL to select distinct record but the performance is not good. Could anyone have any suggestion for the improvement?
Select distinct Top 100 Url,PageName,
(select top 1 inner_pa.LogTime from PageActivity inner_pa with(nolock)
where inner_pa.Key = pa.Key
and inner_pa.UserID='MyID'
order by inner_pa.LogTime Desc) as LogTime
From PageActivity pa WITH(NOLOCK)
Where pa.UserId='MyID'
Order By LogTime DESC
==================================
Table schema is as follows
ID int(PK)
PageName varchar
Key varchar
Username varchar
Url text
LogTime datetime
So if I have the following records
ID PageName Key UserName Url LogTime
1 PageA post MyID PageA.html 2011/1/1/12:10:10
2 PageA post MyID PageA.html 2011/1/1/12:10:15
3 PageB post MyID PageB.html 2011/1/1/12:10:30
4 PageB post MyID PageB.html 2011/1/1/12:10:45
4 PageB post OtherID PageB.html 2011/1/1/12:10:48
The result I want from the query would be
PageName Url
PageA PageA.html
PageB PageB.html
=====================================================================
Things get changed. Now the Url will be combined with field from different table like
Select distinct Top 100 pa.PageName,
pu.URL + '=' + pa.Key as URL,
(select top 1 inner_pa.LogTime from PageActivity inner_pa with(nolock)
where inner_pa.Key = pa.Key
and inner_pa.UserID='MyID'
order by inner_pa.LogTime Desc) as LogTime
From PageActivity pa WITH(NOLOCK)
Join PageUrl pu on pa.UrlID = pu.ID
Where pa.UserId='MyID'
Order By LogTime DESC
Any ideas?
There's no need for the inner query - just group by PageName/URL.
SELECT TOP 100
PageName, URL
FROM PageActivity
GROUP BY PageName, URL
WHERE UserID = 'MyId'
ORDER BY LogTime DESC
I assume that you are working on MsSql, put your query into Database Engine Tuning Advisor. It will give you all the necessary recommendation to improve performance allowing you to create indexes and statistics as required.
精彩评论