CODEWITHSUNDEEP
sqlperformance
Charlie
Charlie

Reputation: 11

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 a 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?

Upvotes: 1

Views: 123

Answers (2)

Winston Smith
Winston Smith

Reputation: 21922

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

Upvotes: 1

mehul9595
mehul9595

Reputation: 1955

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.

Upvotes: 0

Related Questions