sql server - Efficient Amazon style 'customer also viewed' SQL Query -

i figured out need record client session id , viewed product id in order acheive this. here table script:

create table [dbo].[pagevisits](     [id] [int] identity(1,1) not null,     [xsession] [int] null,     [xpagename] [int] null ) on [primary] 

i've come query seems work ok

select top 5 xpagename, count(xpagename)as total  pagevisits  pagevisits.xsession in (select pagevisits.xsession                                pagevisits \                               pagevisits.xpagename = 20                                , pagevisits.xsession != 539447381 )  , pagevisits.xpagename != 20  group xpagename  order total desc 

however being bit of novice @ sql i'm fearful table grow quite large, choke server? there better way or ok?

i not sure why need inner select there. if want select pages visited customer in existing session (assumption: session id known) other current page, can:

select top 5 xpagename, count(xpagename)as total  pagevisits  pagevisits.xsession = 539447381 , pagevisits.xpagename != 20 group xpagename  order total desc 

where 539447381 current session id , 20 current page.

you can improve performance of if can add clustered index on xpagename , xsession:

create clustered index [custom] on [dbo].[pagevisits]  (     [xsession] asc,     [xpagename] asc )with (statistics_norecompute  = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks  = on, allow_page_locks  = on) on [primary] go 

ps: question title said "customer viewed" , answer. if expected result returning top 5 pages visited other customers viewed current page, query given in question sufficient add clustered index better performance (replaces table scans clustered index seeks).